[PostgreSQL] PREPARE 제네릭 플랜 함정 — 배포 직후엔 빠르다가 갑자기 느려질 때

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

부제: status='active'가 99%인 편향 컬럼을 ORM prepared 쿼리로 조회하는데, 특정 값에서만 플랜이 최악으로 굳을 때

-- status: 99.9%가 'active', 0.1%만 'pending' (심하게 편향된 컬럼)
PREPARE p1(text) AS SELECT count(*) FROM skew WHERE status = $1;

-- 커스텀 플랜(force): 값을 보고 그 값에 최적인 플랜을 매번 새로 짠다
SET plan_cache_mode = 'force_custom_plan';
EXPLAIN (COSTS OFF) EXECUTE p1('pending');   -- 희귀값 → 인덱스
EXPLAIN (COSTS OFF) EXECUTE p1('active');    -- 흔한값 → seq scan

-- p1('pending')
 Aggregate
   ->  Index Only Scan using skew_status on skew
         Index Cond: (status = 'pending'::text)
-- p1('active')
 Aggregate
   ->  Seq Scan on skew
         Filter: (status = 'active'::text)

-- 제네릭 플랜(force): 값을 $1로 남겨 하나의 플랜을 모든 값에 재사용
SET plan_cache_mode = 'force_generic_plan';
EXPLAIN (COSTS OFF) EXECUTE p1('pending');

 Aggregate
   ->  Index Only Scan using skew_status on skew
         Index Cond: (status = $1)

EXPLAIN EXECUTE에 값 대신 $1이 보이면 제네릭 플랜이다. 커스텀 플랜은 파라미터 실제 값을 planner에 넘겨 값별 최적 플랜을 짠다 — pending(100건)엔 인덱스, active(99,900건)엔 seq scan으로 서로 다른 플랜이 나온다. 제네릭 플랜은 값을 모른 채(평균 선택도로) 딱 하나의 플랜을 굳혀 재계획 비용을 아끼는 대신, 편향 컬럼에서는 어떤 값에는 최악이 된다. plan_cache_mode=auto(기본)는 처음 몇 회는 커스텀으로 실행하며 평균 비용을 재고, 제네릭 비용이 크게 나쁘지 않으면 제네릭으로 전환한다.

문제는 이 전환이 "배포 직후엔 빠르다가 갑자기 느려지는" 미스터리로 나타난다는 것이다. 위 제네릭 플랜은 인덱스 스캔으로 굳었는데, 이 플랜으로 흔한 값 active를 실행하면 99,900개 인덱스 엔트리를 전부 훑는다:

DEALLOCATE ALL;
PREPARE p1(text) AS SELECT count(*) FROM skew WHERE status = $1;

SET plan_cache_mode = 'force_generic_plan';   -- 굳은 인덱스 스캔
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) EXECUTE p1('active');
SET plan_cache_mode = 'force_custom_plan';     -- 값별 최적(seq scan)
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) EXECUTE p1('active');

-- generic: active를 굳은 인덱스 스캔으로 → 99900개 엔트리 스캔
 Aggregate (actual rows=1 loops=1)
   ->  Index Only Scan using skew_status on skew (actual rows=99900 loops=1)
         Index Cond: (status = $1)
         Heap Fetches: 0
-- custom: active는 seq scan이 최적
 Aggregate (actual rows=1 loops=1)
   ->  Seq Scan on skew (actual rows=99900 loops=1)
         Filter: (status = 'active'::text)
         Rows Removed by Filter: 100

편향 데이터에서 제네릭 플랜이 특정 값에 굳으면 그 값을 만나는 요청만 급격히 느려진다. 처방은 그 쿼리에 대해 커스텀 플랜을 강제하는 것 — SET plan_cache_mode='force_custom_plan'이면 매 실행 값별로 최적 플랜을 짜서 편향을 흡수한다. 재계획 비용은 늘지만, 편향 컬럼에서는 그 값이 훨씬 싸다.

이렇게도 쓴다

지금 세션의 plan_cache_mode를 확인/설정한다. auto가 기본.

SHOW plan_cache_mode;
SET plan_cache_mode = 'force_custom_plan';   -- auto | force_custom_plan | force_generic_plan

 

특정 편향 쿼리만 커스텀을 강제하고 싶으면 함수/세션 단위로 GUC를 건다. (조합: 함수 SET 절)

CREATE FUNCTION report(p text) RETURNS bigint
  LANGUAGE sql SET plan_cache_mode = 'force_custom_plan'
  AS $$ SELECT count(*) FROM skew WHERE status = p $$;

 

준비된 statement 목록과 파라미터 타입을 본다. (조합: pg_prepared_statements)

SELECT name, statement, generic_plans, custom_plans FROM pg_prepared_statements;

 

auto 모드에서 커스텀→제네릭 전환은 대략 6회째부터. 실측하려면 같은 EXECUTE를 여러 번 돌리며 EXPLAIN EXECUTE에 $1이 뜨는 시점을 본다.

EXPLAIN (COSTS OFF) EXECUTE p1('pending');   -- $1 이 보이면 제네릭

 

편향 자체를 planner가 더 잘 알게 하려면 통계 타깃을 올리고 ANALYZE. 제네릭 추정 정확도가 오른다. (조합: 통계)

ALTER TABLE skew ALTER COLUMN status SET STATISTICS 1000;
ANALYZE skew;

 

부분 인덱스로 희귀값만 인덱싱하면 제네릭이든 커스텀이든 그 값 조회가 싸진다. (조합: 부분 인덱스)

CREATE INDEX ON skew (id) WHERE status = 'pending';