[PostgreSQL] enable_seqscan=off로 플래너 결정 진단하기

설치·접속: PostgreSQL 설치와 접속

부제: 인덱스가 있는데 플래너가 Seq Scan을 고른다 — 인덱스를 안 타는 게 정말 손해인지 확인하려고 seqscan을 꺼서 EXPLAIN 비용을 나란히 비교할 때

-- grp 컬럼에 인덱스가 있지만 grp=0 은 전체의 50% (50만 행 중 25만)
-- 먼저 플래너의 기본 선택
EXPLAIN (COSTS ON, TIMING OFF, SUMMARY OFF)
SELECT count(payload) FROM diag WHERE grp = 0;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=8600.21..8600.22 rows=1 width=8)
   ->  Gather  (cost=8599.99..8600.20 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=7599.99..7600.00 rows=1 width=8)
               ->  Parallel Seq Scan on diag  (cost=0.00..7340.17 rows=103930 width=33)
                     Filter: (grp = 0)
(6 rows)

diag_grp 인덱스가 grp에 걸려 있는데도 플래너는 Parallel Seq Scan을 골랐다. 총 비용 8600.22. "인덱스가 있는데 왜 안 타지?"를 억측하지 말고, seqscan을 꺼서 플래너가 강제로 대안 플랜을 짜게 만든 뒤 비용을 나란히 본다.

SET enable_seqscan = off;
EXPLAIN (COSTS ON, TIMING OFF, SUMMARY OFF)
SELECT count(payload) FROM diag WHERE grp = 0;
RESET enable_seqscan;

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=10080.70..10080.71 rows=1 width=8)
   ->  Gather  (cost=10080.48..10080.69 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=9080.48..9080.49 rows=1 width=8)
               ->  Parallel Bitmap Heap Scan on diag  (cost=2785.53..8820.66 rows=103930 width=33)
                     Recheck Cond: (grp = 0)
                     ->  Bitmap Index Scan on diag_grp  (cost=0.00..2723.17 rows=249433 width=0)
                           Index Cond: (grp = 0)
(8 rows)

이제 인덱스를 쓰는 플랜의 비용이 보인다. 총 비용 10080.71 — Seq Scan의 8600.22보다 오히려 비싸다. 답이 나왔다. grp=0이 전체의 절반이라 인덱스로 25만 건의 위치를 찾은 뒤 힙을 그만큼 다시 읽어야 하고, 이 랜덤 접근 비용이 그냥 테이블을 순차로 쭉 읽는 것보다 비싸다. 플래너가 Seq Scan을 고른 건 실수가 아니라 옳은 판단이었다.

enable_seqscan = off는 운영에서 상시 켜두는 설정이 아니다. 사실 이걸 켠다고 seq scan이 진짜 금지되는 것도 아니고(대안이 전혀 없으면 여전히 seq scan을 쓴다), 그저 seq scan의 비용 추정치에 큰 페널티를 얹어 다른 플랜을 강제로 짜보게 하는 진단 도구다. "인덱스를 안 타서 손해"라는 의심이 들 때, 꺼보고 EXPLAIN 비용을 나란히 놓으면 플래너가 왜 그렇게 판단했는지 역으로 이해된다. 만약 여기서 인덱스 플랜 비용이 훨씬 쌌다면, 그건 통계가 낡았거나(ANALYZE 필요) 추정이 틀린 신호다.

이렇게도 쓴다

실제 실행 시간까지 재서 비용 추정이 맞는지 검증한다. (조합: EXPLAIN ANALYZE)

SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(payload) FROM diag WHERE grp = 0;
RESET enable_seqscan;

 

세션 한정으로만 켠다 — RESET 또는 세션 종료로 원복(운영 반영 아님).

SET enable_seqscan = off;   -- 이 세션에서만
-- ... 진단 ...
RESET enable_seqscan;       -- 되돌리기

 

다른 플래너 플래그로 특정 조인/스캔 전략을 배제해 대안을 강제한다. (조합: 플래너 플래그)

SET enable_bitmapscan = off;   -- 순수 Index Scan 비용 확인
SET enable_indexscan = off;    -- 인덱스 아예 배제
SET enable_nestloop = off;     -- 조인 전략 비교

 

통계가 낡아서 오판하는 경우가 의심되면 먼저 이걸 한다. (조합: ANALYZE)

ANALYZE diag;   -- 추정 행 수가 실제와 어긋나면 통계부터 갱신

 

선택도가 낮은(소수 행) 조건에선 반대 결과 — 인덱스 플랜이 더 싸다.

-- 예: 전체의 0.1%만 매칭하는 조건이면 seqscan off 없이도 인덱스를 자연히 탄다
EXPLAIN SELECT count(payload) FROM diag WHERE id = 12345;

언제 이걸론 부족한가: 플래그로 특정 플랜의 비용을 확인해도 근본 원인(낡은 통계, 상관 컬럼 과소추정, 함수로 감싸 인덱스를 못 타는 조건)은 따로 고쳐야 한다. seqscan off는 진단이지 처방이 아니다.