PostgreSQL에서 SQL Plan에 영향을 줄 수 있는, 세션 레벨에서 변경 가능한 파라미터들.
쿼리가 의도하지 않은 플랜으로 실행 시에 세션레벨에서 파라미터를 쉽게 변경 하여 사용하면 된다.
tpch=> select name , setting , context, short_desc
tpch-> from pg_settings where category = 'Query Tuning / Planner Method Configuration' ;
name | setting | context | short_desc
----------------------+---------+---------+--------------------------------------------------------
edb_enable_pruning | on | user | Enables the planner to early-prune partitioned tables.
enable_bitmapscan | on | user | Enables the planner's use of bitmap-scan plans.
enable_hashagg | on | user | Enables the planner's use of hashed aggregation plans.
enable_hashjoin | on | user | Enables the planner's use of hash join plans.
enable_indexonlyscan | on | user | Enables the planner's use of index-only-scan plans.
enable_indexscan | on | user | Enables the planner's use of index-scan plans.
enable_material | on | user | Enables the planner's use of materialization.
enable_mergejoin | on | user | Enables the planner's use of merge join plans.
enable_nestloop | on | user | Enables the planner's use of nested-loop join plans.
enable_seqscan | on | user | Enables the planner's use of sequential-scan plans.
enable_sort | on | user | Enables the planner's use of explicit sort steps.
enable_tidscan | on | user | Enables the planner's use of TID scan plans.
(12 rows)
파라미터 변경은 set 명령어를 이용하면 되고, 파라미터 값 확인은 current_setting 함수를 사용하면 된다.
edb=# select current_setting('enable_hashjoin') ;
current_setting
-----------------
on
(1 row)
edb=# set enable_hashjoin=off ;
SET
edb=# select current_setting('enable_hashjoin') ;
current_setting
-----------------
off
(1 row)
파라미터들을 변경해 가면서 플랜이 변경 되는 것을 확인 할 수 있다.
tpch=> explain
tpch-> SELECT *
tpch-> FROM (
tpch(> SELECT c_name , o_clerk , sum(o_totalprice) t_price
tpch(> FROM orders o INNER JOIN customer c ON o.o_custkey = c.c_custkey
tpch(> GROUP BY c_name , o_clerk
tpch(> ORDER BY t_price desc )
tpch-> limit 10 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Limit (cost=519326.97..519327.09 rows=10 width=67)
-> Sort (cost=519326.97..523076.97 rows=1500000 width=43)
Sort Key: (sum(o.o_totalprice))
-> GroupAggregate (cost=285558.98..319308.98 rows=1500000 width=43)
Group Key: c.c_name, o.o_clerk
-> Sort (cost=285558.98..289308.98 rows=1500000 width=43)
Sort Key: c.c_name, o.o_clerk
-> Hash Join (cost=7032.00..85541.00 rows=1500000 width=43)
Hash Cond: (o.o_custkey = c.c_custkey)
-> Seq Scan on orders o (cost=0.00..42884.00 rows=1500000 width=30)
-> Hash (cost=5157.00..5157.00 rows=150000 width=25)
-> Seq Scan on customer c (cost=0.00..5157.00 rows=150000 width=25)
tpch=> set enable_hashjoin =off ;
SET
tpch=> explain
tpch-> SELECT *
tpch-> FROM (
tpch(> SELECT c_name , o_clerk , sum(o_totalprice) t_price
tpch(> FROM orders o INNER JOIN customer c ON o.o_custkey = c.c_custkey
tpch(> GROUP BY c_name , o_clerk
tpch(> ORDER BY t_price desc )
tpch-> limit 10 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=1167953.97..1167954.09 rows=10 width=67)
-> Sort (cost=1167953.97..1171703.97 rows=1500000 width=43)
Sort Key: (sum(o.o_totalprice))
-> GroupAggregate (cost=934185.98..967935.98 rows=1500000 width=43)
Group Key: c.c_name, o.o_clerk
-> Sort (cost=934185.98..937935.98 rows=1500000 width=43)
Sort Key: c.c_name, o.o_clerk
-> Nested Loop (cost=0.42..734168.00 rows=1500000 width=43)
-> Seq Scan on orders o (cost=0.00..42884.00 rows=1500000 width=30)
-> Index Scan using customer_pk on customer c (cost=0.42..0.45 rows=1 width=25)
Index Cond: (c_custkey = o.o_custkey)
tpch=> set enable_indexscan = off ;
SET
tpch=> explain
tpch-> SELECT *
tpch-> FROM (
tpch(> SELECT c_name , o_clerk , sum(o_totalprice) t_price
tpch(> FROM orders o INNER JOIN customer c ON o.o_custkey = c.c_custkey
tpch(> GROUP BY c_name , o_clerk
tpch(> ORDER BY t_price desc )
tpch-> limit 10 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Limit (cost=7153700.97..7153701.09 rows=10 width=67)
-> Sort (cost=7153700.97..7157450.97 rows=1500000 width=43)
Sort Key: (sum(o.o_totalprice))
-> GroupAggregate (cost=6919932.98..6953682.98 rows=1500000 width=43)
Group Key: c.c_name, o.o_clerk
-> Sort (cost=6919932.98..6923682.98 rows=1500000 width=43)
Sort Key: c.c_name, o.o_clerk
-> Nested Loop (cost=0.43..6719915.00 rows=1500000 width=43)
-> Seq Scan on orders o (cost=0.00..42884.00 rows=1500000 width=30)
-> Bitmap Heap Scan on customer c (cost=0.43..4.44 rows=1 width=25)
Recheck Cond: (c_custkey = o.o_custkey)
-> Bitmap Index Scan on customer_pk (cost=0.00..0.43 rows=1 width=0)
Index Cond: (c_custkey = o.o_custkey)
tpch=> set enable_bitmapscan = off ;
SET
Time: 0.316 ms
tpch=> explain
tpch-> SELECT *
tpch-> FROM (
tpch(> SELECT c_name , o_clerk , sum(o_totalprice) t_price
tpch(> FROM orders o INNER JOIN customer c ON o.o_custkey = c.c_custkey
tpch(> GROUP BY c_name , o_clerk
tpch(> ORDER BY t_price desc )
tpch-> limit 10 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Limit (cost=3375482201.97..3375482202.09 rows=10 width=67)
-> Sort (cost=3375482201.97..3375485951.97 rows=1500000 width=43)
Sort Key: (sum(o.o_totalprice))
-> GroupAggregate (cost=3375248433.98..3375282183.98 rows=1500000 width=43)
Group Key: c.c_name, o.o_clerk
-> Sort (cost=3375248433.98..3375252183.98 rows=1500000 width=43)
Sort Key: c.c_name, o.o_clerk
-> Nested Loop (cost=0.00..3375048416.00 rows=1500000 width=43)
Join Filter: (o.o_custkey = c.c_custkey)
-> Seq Scan on orders o (cost=0.00..42884.00 rows=1500000 width=30)
-> Materialize (cost=0.00..5907.00 rows=150000 width=25)
-> Seq Scan on customer c (cost=0.00..5157.00 rows=150000 width=25)