PostgreSQL에서 Prepared Statement(준비된 문장)는 두 가지 방식으로 실행 계획(Plan) 이 만들어집니다.
•
Custom Plan: 실행 시점의 실제 파라미터 값을 반영해 그때그때 최적화합니다. 값이 바뀌면 계획도 달라질 수 있습니다.
•
Generic Plan: 파라미터 값을 특정하지 않은 일반화된 계획을 만들어, 같은 세션에서 재사용합니다.
Custom Plan에는 실행계획에 실제 조건 값이 바인드된 형태로 나타나며, Generic Plan은 실행계획에 실제 조건 값이 아닌 $1, $2와 같은 대체 문자가 나타납니다.
-- Custom Plan
Index Scan Backward using tr_ord_big_x08 on tr_ord_big t1 (actual time=0.030..0.268 rows=324 loops=1)
Index Cond: (((shop_id)::text = 'S025'::text) AND (ord_dtm < ('20250101'::cstring)::date) AND (ord_dtm >= (('20250101'::cstring)::date - 3)))
-- Generic Plan
Index Scan Backward using tr_ord_big_x08 on tr_ord_big t1 (actual time=0.186..3.232 rows=5328 loops=1)
Index Cond: (((shop_id)::text = $1) AND (ord_dtm < ($2)::date) AND (ord_dtm >= (($2)::date - 3)))
SQL
복사
이러한 실행계획의 변동에 대한 설정은 plan_cache_mode로 설정이 가능합니다.
•
auto (기본값)
◦
처음 몇 번은 Custom plan으로 실행 → 평균 비용을 기록
◦
그 후 Generic plan을 만들어 비용 비교 → 더 싸면 Generic으로 전환, 아니면 계속 Custom
◦
실행이 이어지는 동안에도 주기적으로 비교해 Custom 
Generic 간 전환이 다시 일어날 수 있음.
Generic 간 전환이 다시 일어날 수 있음.◦
바뀌는 시점은 PostgreSQL 버전과 설정에 따라 다를 수 있음
•
force_custom_plan
◦
항상 Custom plan만 사용.
◦
파라미터 값마다 최적화 → 값 분포가 편향돼 있거나 커스텀이 유리한 경우 성능 향상 가능.
◦
하지만 실행 시마다 플래닝 오버헤드가 있고, 플랜이 자주 변함.
•
force_generic_plan
◦
항상 Generic plan만 사용.
◦
파라미터 값에 상관없이 동일한 계획을 재사용.
아래 SQL로 현재 세션의 plan_cache_mode를 확인할 수 있습니다.
SHOW PLAN_CACHE_MODE;
plan_cache_mode|
---------------+
auto |
-- plan_cache_mode가 auto인 상태에서는 초기 몇 번(보통 다섯번)은 Custom으로 실행
-- 이후는 Generic이 더 이득이라고 판단되면 그때부터 Generic으로 전환합니다
-- 만약에 Custom이 여전히 좋다면 Custom 실행계획이 계속해서 사용됩니다.
SQL
복사
Custom에서 Generic으로 실행계획이 변하는 것을 관찰하려면 Prepare 형태로 SQL을 실행해야 합니다. DBeaver에서Prepare 형태로 SQL을 실행하려면 다음과 같이 DBeaver의 설정을 변경해야 합니다.
•
파라미터 치환 기능을 OFF 해야 $1, $2가 그대로 서버로 전달됩니다.
◦
경로: Window → Preferences → Editors → SQL Editor → SQL Processing → (Parameters/Variables) → Enable SQL parameters 체크 해제
◦
이 설정을 켜두면 $1이 에디터에서 값으로 치환되어 서버 PREPARE가 값 고정 쿼리로 생성되는 실수를 하게 됩니다.
테스트1(shop_id=S100)
아래와 같이, prep_sql1이라는 Prepare를 만들고, 조건 값을 S100을 넣어서 실행합니다.
•
첫 번째부터 다섯 번째까지는 Custom Plan이 작동합니다.
◦
실행계획에 입력한 조건 값이 그대로 보여집니다.
◦
x01(ord_dtm) 인덱스를 사용합니다. 통계에 의해 S100 에는 x01 인덱스가 유리하다고 판다
•
여섯 번째 시점에 Generic Plan이 작동합니다.
◦
실행계획에 입력한 조건 값이 $1, $2로 보여집니다.
◦
x08(shop_id, ord_dtm) 인덱스를 사용합니다.
◦
x08 인덱스를 사용하는 Generic이 더 유리하다고 판단해 Generic 실행계획이 사용됩니다.
-- (재실행 시) 기존 준비문 제거
DEALLOCATE prep_sql1;
-- PREPARE 문장 생성
PREPARE prep_sql1(text,text) AS
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = $1
AND t1.ord_dtm < $2::date
AND t1.ord_dtm >= $2::date - 3
ORDER BY t1.ord_dtm DESC;
-- 아래 SQL을 6번 연속 실행하면서 실행계획을 관찰
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
EXECUTE prep_sql1('S100','20250101');
-- 1~5번째 실행: Custom(조건에 값이 바인딩 되어 있음)
-- x01 인덱스를 사용함.
Index Scan Backward using tr_ord_big_x01 on tr_ord_big t1 (actual time=0.081..20.545 rows=5328 loops=1)
Index Cond: ((ord_dtm < ('20250101'::cstring)::date) AND (ord_dtm >= (('20250101'::cstring)::date - 3)))
Filter: ((shop_id)::text = 'S100'::text)
Rows Removed by Filter: 68472
Buffers: shared hit=2369
Planning:
Buffers: shared hit=5
Planning Time: 0.330 ms
Execution Time: 21.007 ms
-- 6번째 실행: Generic(조건에 값이 $1, $2로 처리됨)
-- Custom보다 Generic이 유리하다고 판단해서 Generic으로 변경됨.
-- Generic으로 변경되면서 x08(shop_id,ord_dmt) 인덱스를 사용함.
Index Scan Backward using tr_ord_big_x08 on tr_ord_big t1 (actual time=0.186..3.232 rows=5328 loops=1)
Index Cond: (((shop_id)::text = $1) AND (ord_dtm < ($2)::date) AND (ord_dtm >= (($2)::date - 3)))
Buffers: shared hit=671
Planning Time: 0.021 ms
Execution Time: 3.742 ms
SQL
복사
테스트 2(shop_id=S025, 중간에 S100으로 변경)
prep_sql1을 DEALLCOATE후 다시 만들어 S025로 테스트 합니다.
shop_id=S025의 경우는 x01(ord_dtm)보다 x08(shop_id, ord_dtm) 인덱스가 유리하다 판단해, Custom plan에서도 x08 인덱스를 사용합니다.
-- (재실행 시) 기존 준비문 제거
DEALLOCATE prep_sql1;
-- PREPARE 문장 생성
PREPARE prep_sql1(text,text) AS
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = $1
AND t1.ord_dtm < $2::date
AND t1.ord_dtm >= $2::date - 3
ORDER BY t1.ord_dtm DESC;
-- 조건을 S025로 실행
-- 열번까지 무조건 Custom으로 실행됨(Generic으로 변경되지 않음)
-- S025 조건이 실제 바인딩되고 x08 인덱스 사용
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
EXECUTE prep_sql1('S025','20250101'); -- 조건을 S025로 실행
Index Scan Backward using tr_ord_big_x08 on tr_ord_big t1 (actual time=0.030..0.268 rows=324 loops=1)
Index Cond: (((shop_id)::text = 'S025'::text) AND (ord_dtm < ('20250101'::cstring)::date) AND (ord_dtm >= (('20250101'::cstring)::date - 3)))
Buffers: shared hit=212
Planning:
Buffers: shared hit=5
Planning Time: 0.280 ms
Execution Time: 0.310 ms
-- 11번째부터 조건을 S100으로 변경
-- 11~14번째까지는 Custom: S100 조건이 직접 바인됭 되서 x01 인덱스 사용
-- Custom 방식 플랜이므로 S100에 적절한 인덱스(x01)을 옵티마이저가 선택
-- 물론, x08이 더 좋지만, 옵티마이저의 통계 기준으로는 x01을 선택함
-- 15번째부터 Generic으로 변경됨(x08 인덱스 사용)
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
EXECUTE prep_sql1('S100','20250101'); -- 조건을 S100으로 변경 실행
-- 아래는 15번째의 Generic Plan
Index Scan Backward using tr_ord_big_x08 on tr_ord_big t1 (actual time=0.081..2.589 rows=5328 loops=1)
Index Cond: (((shop_id)::text = $1) AND (ord_dtm < ($2)::date) AND (ord_dtm >= (($2)::date - 3)))
Buffers: shared hit=671
Planning Time: 0.049 ms
Execution Time: 3.002 ms
-- 조건을 S025로 다시 변경
-- 현재 Generic Plan 사용중이므로 Genric Plan이 그대로 사용됨.
-- 처음 S025 조건으로 처리했을때는 바인드 값이 S025로 그대로 출력 -> Custom
-- 지금은 S025 조건이 $1으로 출력됨 -> Generic
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
EXECUTE prep_sql1('S025','20250101'); -- 조건을 S025로 실행
Index Scan Backward using tr_ord_big_x08 on tr_ord_big t1 (actual time=0.058..0.513 rows=324 loops=1)
Index Cond: (((shop_id)::text = $1) AND (ord_dtm < ($2)::date) AND (ord_dtm >= (($2)::date - 3)))
Buffers: shared hit=212
Planning Time: 0.022 ms
Execution Time: 0.586 ms
SQL
복사
테스트 3(무조건 Generic)
만약에 무조건 Generic이 좋다고 판단되면, 아래와 같이 plan_cache_mode를 generic으로 고정할 수도 있다.
SET plan_cache_mode = force_generic_plan;
-- (재실행 시) 기존 준비문 제거
DEALLOCATE prep_sql1;
-- PREPARE 문장 생성
PREPARE prep_sql1(text,text) AS
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = $1
AND t1.ord_dtm < $2::date
AND t1.ord_dtm >= $2::date - 3
ORDER BY t1.ord_dtm DESC;
-- 처음부터 무조건 Generic Plan 사용
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
EXECUTE prep_sql1('S100','20250101');
Index Scan Backward using tr_ord_big_x08 on tr_ord_big t1 (actual time=0.235..4.040 rows=5328 loops=1)
Index Cond: (((shop_id)::text = $1) AND (ord_dtm < ($2)::date) AND (ord_dtm >= (($2)::date - 3)))
Buffers: shared hit=671
Planning Time: 0.568 ms
Execution Time: 4.495 ms
SQL
복사
결론
plan_cache_mode가 auto인 경우에는 실행 시점마다 plan이 달라질 수 있음.
•
Custom과 Generic을 비교해 좋은 실행계획을 선택
•
초기 몇 차례는 Custom으로 실행한 뒤 Generic 후보와 비용을 비교해 전환을 시도하고, 이후에도 필요 시 재평가될 수 있다
Auto가 좋은가? Generic 고정이 좋을까? Custom 고정이 좋을까?
•
너무 어려운 문제입니다. Generic과 Custom 모두 장단점이 있기 때문에 Auto가 만들어졌으리라 생각합니다.
•
일반적으로 Auto를 유지하고, 실행계획이 상황에 따라 변경될 수 있음을 인지하고 있으면 될듯 합니다.
•
아주 특수한 경우에만 일부 SQL에 Generic이나 Custom을 고려해 볼 수 있지 않을까 생각합니다.
◦
하지만, 이처럼 처리하면 운영 난이도가 올라갈거 같습니다.
◦
차라리 특수한 경우에는 상황에 맞게 힌트를 고정하는게 더 좋으리라 생각합니다.
•
Generic Plan이 모든 세션이 서로 공유하는 것이 아닌, 자기 자신 세션에서만 공유된다는 점도 중요합니다.
◦
같은 SQL이 실행 세션에 따라 다른 실행 계획이 나올 가능성이 있는 것이죠.
이상입니다.