prepared statement 플랜 캐시 — generic vs custom plan

prepared statement(준비된 구문)는 SQL을 한 번 파싱·계획해 두고 값만 바꿔 여러 번 실행하는 기능입니다. 매 실행마다 파싱과 플래닝을 반복하지 않으니 CPU를 아끼고, 값이 파라미터로 분리돼 SQL 인젝션에도 안전합니다. 그런데 여기엔 사람들이 잘 모르는 함정이 하나 있습니다. PostgreSQL은 이 캐시된 계획을 generic plan(범용 계획)과 custom plan(맞춤 계획) 사이에서 저울질하는데, 그 선택을 잘못하면 인덱스를 멀쩡히 두고도 느려집니다.

개인적으로 이 주제는 "빠르라고 넣은 최적화가 오히려 느리게 만드는" 대표 사례라 좋아합니다. 원리를 알면 십중팔구 해결됩니다.

generic plan vs custom plan

파라미터가 있는 구문을 준비하면, PostgreSQL은 처음 몇 번은 실제 들어온 값으로 매번 계획을 세웁니다(custom plan). 이건 값에 딱 맞는 최적 계획을 주지만 매번 플래닝 비용이 듭니다. 몇 번 실행해 본 뒤, 값에 상관없이 쓸 수 있는 generic plan의 예상 비용이 custom과 비슷하면 generic으로 굳혀 이후 플래닝을 건너뜁니다.

문제는 데이터가 치우친(skewed) 경우입니다. 예를 들어 status = ?에서 값이 'done'이면 전체의 99%, 'error'면 0.1%라고 합시다. custom plan은 값을 보고 'error'엔 인덱스 스캔, 'done'엔 순차 스캔을 고릅니다. 그런데 generic plan은 값을 모르니 평균적인 선택도로 하나의 계획만 세웁니다. 그 계획이 'error' 조회에 순차 스캔을 걸어버리면, 0.1%짜리 조회가 테이블 전체를 훑는 사고가 납니다.

지금 어떤 계획이 쓰이는지 보기

PREPARE q(text) AS
  SELECT * FROM jobs WHERE status = $1;
-- 처음 5번은 custom plan, 이후 generic으로 전환될 수 있다
EXECUTE q('error');
EXECUTE q('error');
-- ...
-- 준비된 구문과 그 계획 유형을 확인
SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements;
-- 실제 계획을 뜯어보기
EXPLAIN (ANALYZE) EXECUTE q('error');

generic_plans가 올라가기 시작했는데 특정 값 조회가 갑자기 느려졌다면, generic plan이 그 값에 안 맞는 계획을 물려준 것일 가능성이 큽니다.

plan_cache_mode로 강제하기

PostgreSQL 12부터는 이 선택을 직접 강제할 수 있습니다. 값 편중이 심해 항상 값에 맞는 계획이 필요하면 custom을 강제합니다.

-- 이 세션에서 항상 값에 맞춰 다시 계획 (편중 데이터에 안전)
SET plan_cache_mode = force_custom_plan;
-- 반대로 항상 generic 재사용 (계획이 안정적이고 플래닝 비용을 아끼고 싶을 때)
SET plan_cache_mode = force_generic_plan;
-- 기본값: 휴리스틱이 알아서 저울질
SET plan_cache_mode = auto;

실전 감각: OLTP에서 같은 구문을 초당 수천 번 도는데 데이터 분포가 고르면 generic이 이깁니다. 반대로 조건 값에 따라 맞는 계획이 판이하게 달라지는 리포트성 쿼리는 custom이 안전합니다. 애매하면 auto로 두고, 특정 느린 구문만 force_custom_plan으로 국소 처방하세요.

드라이버·풀러가 개입한다

이 얘기가 어려운 이유는 애플리케이션 스택이 한 겹 더 끼기 때문입니다. 많은 드라이버가 서버 측 prepared statement를 자동으로 씁니다. 그런데 PgBouncer를 트랜잭션 풀링으로 쓰면, 준비는 A 연결에서 하고 실행은 B 연결로 가는 어긋남이 생겨 "prepared statement does not exist" 오류가 터집니다. 그래서 트랜잭션 풀링 환경에선 드라이버의 서버 측 준비를 끄거나, PgBouncer의 prepared statement 지원 옵션을 맞춰야 합니다. 저는 이걸로 배포 직후 장애를 겪고 나서야 조합을 신중히 보게 됐습니다.

자주 묻는 질문

prepared statement는 무조건 빠른가요?

파싱·플래닝을 아끼는 이득은 분명하지만, generic plan이 편중 데이터에 안 맞는 계획을 굳혀버리면 오히려 느려질 수 있습니다. 빠름은 "반복 실행 + 고른 분포"라는 전제에서 성립합니다.

generic으로 전환되는 기준이 정확히 몇 번인가요?

구현상 대략 5회 실행 이후, generic plan의 추정 비용이 custom 평균과 견줄 만하면 전환됩니다. 정확한 횟수에 의존하지 말고 plan_cache_mode로 명시 제어하는 편이 안전합니다.

ORM을 쓰는데 이걸 신경 써야 하나요?

ORM·드라이버가 내부적으로 서버 측 준비를 쓰는지에 따라 영향을 받습니다. 느려지는 특정 쿼리가 있다면 해당 세션에서 force_custom_plan을 걸어 재현되는지부터 확인해 보세요.

PgBouncer를 쓰면 prepared statement를 포기해야 하나요?

꼭 그렇진 않습니다. 세션 풀링이면 문제없고, 최신 PgBouncer는 트랜잭션 풀링에서도 prepared statement를 지원하는 옵션이 있습니다. 스택 조합에 맞춰 드라이버 설정을 맞추면 병행할 수 있습니다.