PostgreSQL 쿼리가 갑자기 느려졌는데 EXPLAIN ANALYZE 결과 Seq Scan이 발생합니다

Question

PostgreSQL에서 잘 되던 쿼리가 갑자기 5초 이상 걸립니다. EXPLAIN ANALYZE 해보니 Seq Scan이 나옵니다.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1234 AND status = 'completed';

-- 결과: Seq Scan on orders (cost=0.00..15234.00 rows=1 width=128)
-- Execution Time: 5234.123 ms

user_id에 인덱스는 걸어놨는데 왜 Seq Scan인가요? 데이터가 100만건 정도 됩니다.

김영삼 · Accepted Answer

인덱스가 있는데 Seq Scan이 발생하는 대표적인 원인들:1. 통계 정보가 오래됨 (가장 흔한 원인)-- 테이블 통계 업데이트 ANALYZE orders; -- 그 후 다시 EXPLAIN EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1234 AND status = 'completed';2. 복합 인덱스 필요-- user_id 단일 인덱스로는 부족, 복합 인덱스 생성 CREATE INDEX idx_orders_user_status ON orders(user_id, status);3. 확인 사항-- 기존 인덱스 확인 \d orders -- 인덱스 사용 통계 SELECT indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE relname = 'orders';100만건이면 ANALYZE 후 복합 인덱스를 생성하면 5초 → 1ms 이내로 줄어들 겁니다.

서버지기영호 · Answer

ANALYZE 실행하고 복합 인덱스 걸었더니 0.3ms로 줄었습니다! 그런데 autovacuum이 이걸 자동으로 안 해주나요?

김영삼 · Answer

autovacuum이 ANALYZE도 함께 수행하지만, 데이터 변경이 많으면 통계가 뒤처질 수 있습니다. 특히 대량 INSERT/UPDATE 직후에 그렇습니다.-- 특정 테이블의 autovacuum 주기를 더 자주 실행되도록 설정 ALTER TABLE orders SET ( autovacuum_analyze_threshold = 100, autovacuum_analyze_scale_factor = 0.01 -- 1% 변경 시 자동 ANALYZE );대량 배치 작업 후에는 수동 ANALYZE를 습관화하는 것이 좋습니다.

서버지기영호 · Answer

5초에서 0.3ms로!! autovacuum 설정까지 완벽합니다. 정말 감사합니다!!

댓글 4