PostgreSQL 같은 쿼리가 어느 날 갑자기 100배 느려졌습니다 — 플랜 플립

Question

배포도 안 했는데 어제부터 특정 목록 쿼리가 평소 20ms에서 2초로 느려졌습니다. 인덱스는 그대로 있고, EXPLAIN을 보니 예전엔 Index Scan이던 게 Seq Scan으로 바뀌었습니다.

-- 같은 쿼리, 어제부터
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending' AND created_at > now() - interval '1 day';
-- Seq Scan on orders (actual time=... rows=1200000)

데이터가 늘긴 했지만 status='pending'은 전체의 0.5%밖에 안 됩니다. 왜 인덱스를 안 탈까요?

환경: PostgreSQL 16, 테이블 약 8천만 row

김영삼 · Accepted Answer

전형적인 플랜 플립(plan flip)입니다. 옵티마이저가 통계 기반으로 비용을 추정하는데, 통계가 낡거나 분포가 바뀌면 "Index Scan보다 Seq Scan이 싸다"고 잘못 판단합니다. 1) 통계부터 갱신 ANALYZE orders; -- 추정 rows vs 실제 rows 차이 확인 EXPLAIN ANALYZE <쿼리>; -- estimated≪actual 이면 통계 문제 2) 통계 정밀도 상향 — pending 같은 희소값이 most_common_vals에서 빠지면 선택도를 과대평가합니다. ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; ANALYZE orders; 3) 부분 인덱스로 희소 조건을 확실히 태우기: CREATE INDEX CONCURRENTLY idx_orders_pending ON orders (created_at) WHERE status = 'pending'; 이러면 옵티마이저가 통계와 무

db_운영민 · Answer

STATISTICS 1000 + ANALYZE 하니 바로 Index Scan으로 돌아왔습니다. 그런데 며칠 지나면 또 느려질까 걱정인데, 부분 인덱스를 추가하는 게 근본 대책일까요?

김영삼 · Answer

네, 희소 조건(전체의 1% 미만)이고 자주 쓰는 쿼리라면 부분 인덱스가 가장 안정적입니다. 통계가 흔들려도 인덱스 자체가 작아 옵티마이저가 선택할 확률이 높고, 인덱스 크기·쓰기 비용도 적습니다. 추가로 auto_explain 확장을 켜서 느린 쿼리의 실제 플랜을 로그로 남겨두면, 다음에 또 플립이 나도 원인 파악이 빠릅니다. auto_explain.log_min_duration = '500ms' auto_explain.log_analyze = on

db_운영민 · Answer

부분 인덱스 만들고 auto_explain까지 켜뒀습니다. 이틀째 20ms 유지 중이에요. 덕분에 원리까지 이해했습니다. 감사합니다!

댓글 0