728x90
반응형
bind 변수를 이용하는 쿼리를 사용시 plan_cache_mode 설정 값에 따라서 플랜을 생성하는 것이 다르다.
plan_cache_mode의 기본 값은 auto이고, 줄 수 있는 옵션은 다음과 같다
force_generic_plan : 바인드 값을 참고하지 않고 일반적인 plan 생성
force_custom_plan : 오라클의 bind peeking이나 SQL Server의 sniffed parameter 처럼 바인드 값에 따라서 다르게 플랜을 생성한다.
auto : 동일 세션에서 수행되는 바인드 SQL인 경우 5번 까지는 force_custom_plan 처럼 바인드 변수 값을 이용해서 플랜을 생성한다. 이후 6번째 수행 부터는 바인드 값을 참조 하지 않는 플랜과 기존 바인드 값을 참조한 플랜들의 코스트를 비교해서 플랜을 생성하며, generic plan의 코스트가 낮은 경우(generic plan이 선택) 이후 부터는 cost 비교를 하지 않고 generic plan으로 고정 됨
테스트 환경 구성
| 테스트 데이터 생성 | create table test as select (row_number() over()) as id , md5(random()::text) as name , gs as input_date from generate_series('2020-01-01 00:00'::timestamp, '2024-06-10 23:00'::timestamp, '1 minute') AS gs; create index test_input_date on test(input_date) ; |
| 데이터 확인 | test=# \d+ test Table "public.test" +------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------+ | Column | Type | Collation | Nullable | Default | Storage | Stats target | Description | +------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------+ | id | bigint | | | | plain | | | | name | text | | | | extended | | | | input_date | timestamp without time zone | | | | plain | | | +------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------+ Indexes: "test_input_date" btree (input_date) Access method: heap test=# select count(*) from test ; +---------+ | count | +---------+ | 2337061 | +---------+ |
| Bind SQL | prepare stmt ( timestamp ) as select * from test where input_date > $1 ; |
테스트 #1
| 파라미터 확인 | test=# show plan_cache_mode ; +-----------------+ | plan_cache_mode | +-----------------+ | auto | +-----------------+ |
| test=# explain execute stmt ('2025-06-01 00:00') ; +--------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------+ | Index Scan using test_input_date on test (cost=0.43..4.45 rows=1 width=49) | | Index Cond: (input_date > '01-JUN-25 00:00:00'::timestamp without time zone) | +--------------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2022-01-01 00:00') ; +---------------------------------------------------------------------------------------+ | QUERY PLAN | +---------------------------------------------------------------------------------------+ | Index Scan using test_input_date on test (cost=0.43..49951.72 rows=1287902 width=49) | | Index Cond: (input_date > '01-JAN-22 00:00:00'::timestamp without time zone) | +---------------------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2020-01-01 00:00') ; +----------------------------------------------------------------------------+ | QUERY PLAN | +----------------------------------------------------------------------------+ | Seq Scan on test (cost=0.00..53307.26 rows=2337060 width=49) | | Filter: (input_date > '01-JAN-20 00:00:00'::timestamp without time zone) | +----------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2021-01-01 00:00') ; +----------------------------------------------------------------------------+ | QUERY PLAN | +----------------------------------------------------------------------------+ | Seq Scan on test (cost=0.00..53307.26 rows=1818857 width=49) | | Filter: (input_date > '01-JAN-21 00:00:00'::timestamp without time zone) | +----------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2021-01-01 00:00') ; +----------------------------------------------------------------------------+ | QUERY PLAN | +----------------------------------------------------------------------------+ | Seq Scan on test (cost=0.00..53307.26 rows=1818857 width=49) | | Filter: (input_date > '01-JAN-21 00:00:00'::timestamp without time zone) | +----------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2021-01-01 00:00') ; +--------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------+ | Index Scan using test_input_date on test (cost=0.43..30216.28 rows=779020 width=49) | | Index Cond: (input_date > $1) | +--------------------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2021-01-01 00:00') ; +--------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------+ | Index Scan using test_input_date on test (cost=0.43..30216.28 rows=779020 width=49) | | Index Cond: (input_date > $1) | +--------------------------------------------------------------------------------------+ |
테스트 #2
위의 동일 세션에서 plan_cache_mode 값을 변경
| force_custom_plan | test=# set plan_cache_mode = force_custom_plan ; test=# show plan_cache_mode ; +-------------------+ | plan_cache_mode | +-------------------+ | force_custom_plan | +-------------------+ (1 row) test=# explain execute stmt ('2021-01-01 00:00') ; +----------------------------------------------------------------------------+ | QUERY PLAN | +----------------------------------------------------------------------------+ | Seq Scan on test (cost=0.00..53307.26 rows=1818857 width=49) | | Filter: (input_date > '01-JAN-21 00:00:00'::timestamp without time zone) | +----------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2021-01-01 00:00') ; +----------------------------------------------------------------------------+ | QUERY PLAN | +----------------------------------------------------------------------------+ | Seq Scan on test (cost=0.00..53307.26 rows=1818857 width=49) | | Filter: (input_date > '01-JAN-21 00:00:00'::timestamp without time zone) | +----------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2022-01-01 00:00') ; +---------------------------------------------------------------------------------------+ | QUERY PLAN | +---------------------------------------------------------------------------------------+ | Index Scan using test_input_date on test (cost=0.43..49951.72 rows=1287902 width=49) | | Index Cond: (input_date > '01-JAN-22 00:00:00'::timestamp without time zone) | +---------------------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2023-01-01 00:00') ; +--------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------+ | Index Scan using test_input_date on test (cost=0.43..29507.31 rows=760736 width=49) | | Index Cond: (input_date > '01-JAN-23 00:00:00'::timestamp without time zone) | +--------------------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2024-01-01 00:00') ; +-------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------+ | Index Scan using test_input_date on test (cost=0.43..9065.17 rows=233642 width=49) | | Index Cond: (input_date > '01-JAN-24 00:00:00'::timestamp without time zone) | +-------------------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2020-01-01 00:00') ; +----------------------------------------------------------------------------+ | QUERY PLAN | +----------------------------------------------------------------------------+ | Seq Scan on test (cost=0.00..53307.26 rows=2337060 width=49) | | Filter: (input_date > '01-JAN-20 00:00:00'::timestamp without time zone) | +----------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2021-01-01 00:00') ; +----------------------------------------------------------------------------+ | QUERY PLAN | +----------------------------------------------------------------------------+ | Seq Scan on test (cost=0.00..53307.26 rows=1818857 width=49) | | Filter: (input_date > '01-JAN-21 00:00:00'::timestamp without time zone) | +----------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2022-01-01 00:00') ; +---------------------------------------------------------------------------------------+ | QUERY PLAN | +---------------------------------------------------------------------------------------+ | Index Scan using test_input_date on test (cost=0.43..49951.72 rows=1287902 width=49) | | Index Cond: (input_date > '01-JAN-22 00:00:00'::timestamp without time zone) | +---------------------------------------------------------------------------------------+ (2 rows) |
| force_generic_plan | test=# set plan_cache_mode = force_generic_plan ; test=# show plan_cache_mode ; +--------------------+ | plan_cache_mode | +--------------------+ | force_generic_plan | +--------------------+ (1 row) test=# explain execute stmt ('2022-01-01 00:00') ; +--------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------+ | Index Scan using test_input_date on test (cost=0.43..30216.28 rows=779020 width=49) | | Index Cond: (input_date > $1) | +--------------------------------------------------------------------------------------+ (2 rows) test=# explain execute stmt ('2020-01-01 00:00') ; +--------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------+ | Index Scan using test_input_date on test (cost=0.43..30216.28 rows=779020 width=49) | | Index Cond: (input_date > $1) | +--------------------------------------------------------------------------------------+ (2 rows) |
반응형