PostgreSQL 특정 쿼리가 어제까진 빨랐는데 갑자기 느려졌어요

Question

수개월 잘 돌던 조회 쿼리가 어제부터 갑자기 10배 넘게 느려졌습니다. 코드 배포도 안 했고 데이터가 갑자기 폭증한 것도 아니에요. 같은 쿼리인데 EXPLAIN ANALYZE를 찍어보니 인덱스를 안 타고 Seq Scan을 하고 있습니다. 어제까진 Index Scan이었는데... 뭐가 바뀐 걸까요?

김영삼 · Accepted Answer

전형적인 플랜 뒤집힘(plan flip)입니다. 쿼리·인덱스는 그대로인데 옵티마이저가 갑자기 Seq Scan이 더 싸다고 오판하는 거예요. 원인은 대부분 통계 정보가 낡거나 틀어진 것입니다.1순위 — 통계 갱신ANALYZE your_table; -- 또는 전체 VACUUM ANALYZE your_table;대량 INSERT/UPDATE/DELETE가 있었거나 autovacuum이 제때 못 돌면 통계가 실제 분포와 어긋납니다. 그러면 옵티마이저가 "이 조건에 맞는 행이 엄청 많다"고 착각해 인덱스를 버리고 풀스캔을 택해요. ANALYZE 한 번으로 되돌아오는 경우가 많습니다.확인 포인트EXPLAIN (ANALYZE, BUFFERS)에서 추정 행 수(estimated rows)와 실제 행 수(actual rows)의 차이를 보세요. 크게 벌어져 있으면 통계 문제 확정입니다.pg_stat_user_tables에서 last_analyze, last_autovacuum 시각을 확인 — 오래됐

db_kanghyun · Answer

ANALYZE 하니까 바로 Index Scan으로 돌아왔어요! 그런데 이게 또 언제 뒤집힐지 불안한데, 아예 이 쿼리는 인덱스를 강제로 쓰게 할 수는 없나요?

김영삼 · Answer

PostgreSQL은 오라클 같은 인덱스 강제 힌트가 기본적으로 없습니다(철학적으로 옵티마이저를 신뢰하는 방향이에요). 대신 이렇게 접근합니다.통계 정밀도 올리기 — 특정 컬럼 분포가 편향돼 있으면 통계 표본을 늘리세요.ALTER TABLE t ALTER COLUMN col SET STATISTICS 1000; ANALYZE t;기본값 100보다 촘촘히 봐서 오판이 줄어듭니다.다중 컬럼 상관관계가 문제면 확장 통계를 만드세요.CREATE STATISTICS s (dependencies) ON col_a, col_b FROM t; ANALYZE t;정 급하면 pg_hint_plan 확장으로 힌트를 줄 수 있지만, 이건 최후의 수단입니다. 근본 원인(통계)을 두고 힌트로 덮으면 나중에 데이터가 바뀔 때 더 나빠질 수 있어요.제 권장은 확장 통계 + STATISTICS 상향으로 옵티마이저가 스스로 옳은 판단을 하게 만드는 겁니다. 힌트로 고정하는 것보다 장기적으로 안전합니다.

db_kanghyun · Answer

확장 통계까지 걸어두니 며칠째 안정적입니다. 그동안 autovacuum을 방치했던 게 문제였네요. 덕분에 원인까지 이해했습니다, 정말 감사합니다!

댓글 4