はじめに
PostgreSQLはOracleとは異なり、SQL Hint機能を提供してないです。
そのため、Hint機能を使うために、Pg_hint_plan Extensionのインストールが必要です。
本記事では、Pg_hint_planのInstall方法とヒントの使用方法について説明します。
Pg_hint_planのInstall
- Pg_hint_plan File Download
pg_hint_plan Extension Github URL : https://github.com/ossc-db/pg_hint_plan
#例：Postgresql15の場合
#File Downloadする
wget https://github.com/ossc-db/pg_hint_plan/archive/refs/tags/REL15_1_5_1.tar.gz
#圧縮を解凍
tar zvxf REL15_1_5_1.tar.gz
cd pg_hint_plan-REL15_1_5_1
#Install
make && make install
#pg_hint_plan Extension適用
#postgresql.confを修正します。
#parameter shared_preload_libraries項目にpg_hint_planを追加
...
shared_preload_libraries = 'pg_hint_plan'
...
#Postgresql再起動
#このコマンドは各自環境に違いますので、自分の環境に合わせて再起動を行います。
systemctl restart postgresql
#Extension作成
postgres=# CREATE EXTENSION pg_hint_plan ;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+------------------------------
pg_hint_plan | 1.5.1 | hint_plan |
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
SQL Plan確認方法
- Postgresql PlanはExplainコマンドで確認ができます
- 予想Plan以外に実Planを確認するためには、analyze optionが必要になります。
- 他のOptionは下記のサイトを参照してください
https://www.postgresql.org/docs/current/sql-explain.html
#予想Plan確認
=# EXPLAIN
-# select /*+ HashJoin(a b) */ *
-# from employees a join employees b
-# on a.id = b.id;
QUERY PLAN
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
Hash Join (cost=156.71..268.36 rows=4565 width=108)
Hash Cond: (a.id = b.id)
-> Seq Scan on employees a (cost=0.00..99.65 rows=4565 width=54)
-> Hash (cost=99.65..99.65 rows=4565 width=54)
-> Seq Scan on employees b (cost=0.00..99.65 rows=4565 width=54)
(5 rows)
#実Plan確認
#analyze optionを使う場合、SQLが実行されますので注意してください。
=# EXPLAIN (analyze, buffers)
-# select /*+ HashJoin(a b) */ *
-# from employees a join employees b
-# on a.id = b.id;
QUERY PLAN
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
Hash Join (cost=156.71..268.36 rows=4565 width=108) (actual time=1.846..3.462 rows=4607 loops=1)
Hash Cond: (a.id = b.id)
Buffers: shared hit=108
-> Seq Scan on employees a (cost=0.00..99.65 rows=4565 width=54) (actual time=0.023..0.346 rows=4607 loops=1)
Buffers: shared hit=54
-> Hash (cost=99.65..99.65 rows=4565 width=54) (actual time=1.775..1.776 rows=4607 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 476kB
Buffers: shared hit=54
-> Seq Scan on employees b (cost=0.00..99.65 rows=4565 width=54) (actual time=0.002..0.742 rows=4607 loops=1)
Buffers: shared hit=54
Planning Time: 0.176 ms
Execution Time: 3.814 ms
(12 rows)
Hint使用
PostgresqlでHint適用はOracleと同じ形式になります。
postgres=# select /*+ Hint内容 */ * from employees a join employees b on a.id = b.id;
- 今回テストに使うTable構成
\d employees
Table "public.employees"
Column x Type x Collation x Nullable x Default
qqqqqqqqqqqqqqqnqqqqqqqqqqqqqqqqqqqqqqqnqqqqqqqqqqqnqqqqqqqqqqnqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
employee_id x integer x x not null x nextval('employees_employee_id_seq'::regclass)
first_name x character varying(50) x x x
last_name x character varying(50) x x x
department_id x integer x x x
Indexes:
"employees_pkey" PRIMARY KEY, btree (employee_id)
"idx_employees_department_id" btree (department_id)
"idx_employees_first_name" btree (first_name)
"idx_employees_last_name" btree (last_name)
\d departments
Table "public.departments"
Column x Type x Collation x Nullable x Default
qqqqqqqqqqqqqqqqqnqqqqqqqqqqqqqqqqqqqqqqqnqqqqqqqqqqqnqqqqqqqqqqnqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
department_id x integer x x not null x nextval('departments_department_id_seq'::regclass)
department_name x character varying(50) x x x
Indexes:
"departments_pkey" PRIMARY KEY, btree (department_id)
"idx_departments_department_id" btree (department_id)
- Scan Method
Index指定Hintを使用してidx_employees_department_idからemployees_pkeyに変更されています。
explain select * from employees where employee_id>100 and department_id=5;
QUERY PLAN
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
Bitmap Heap Scan on employees (cost=5.25..54.75 rows=490 width=33)
Recheck Cond: (department_id = 5)
Filter: (employee_id > 100)
-> Bitmap Index Scan on ★idx_employees_department_id (cost=0.00..5.13 rows=500 width=0)
Index Cond: (department_id = 5)
(5 rows)
#Hint適用後
↓↓↓↓↓↓↓↓
explain select /*+ indexscan(employees employees_pkey) */ * from employees where employee_id>100 and department_id=5;
QUERY PLAN
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
Index Scan using ★employees_pkey on employees (cost=0.28..157.98 rows=490 width=33)
Index Cond: (employee_id > 100)
Filter: (department_id = 5)
(3 rows)
- Join Method
Join Hintを利用して、Joinの種類を変更できます。
Hint適用する前はHash JoinでJoinされてますが、Hintでnestloop & leading Hintを適用してNested Loop JoinとJoin順番が変更されていることが確認できます。
=# explain
-# SELECT
-# e.employee_id, e.first_name, e.last_name, d.department_name
-# FROM employees e
-# JOIN departments d ON e.department_id = d.department_id;
QUERY PLAN
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
★Hash Join (cost=1.23..111.91 rows=5000 width=147)
Hash Cond: (e.department_id = d.department_id)
-> Seq Scan on ★employees e (cost=0.00..92.00 rows=5000 width=33)
-> Hash (cost=1.10..1.10 rows=10 width=122)
-> Seq Scan on ★departments d (cost=0.00..1.10 rows=10 width=122)
(5 rows)
#Hint適用後
↓↓↓↓↓↓↓↓
=# explain
-# SELECT /*+ nestloop (e d) leading ((d e))*/
-# e.employee_id, e.first_name, e.last_name, d.department_name
-# FROM employees e
-# JOIN departments d ON e.department_id = d.department_id;
QUERY PLAN
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
★Nested Loop (cost=0.28..194.11 rows=5000 width=147)
-> Seq Scan on ★ departments d (cost=0.00..1.10 rows=10 width=122)
-> Index Scan using idx_employees_department_id on ★ employees e (cost=0.28..14.30 rows=500 width=33)
Index Cond: (department_id = d.department_id)
他に使えるHintについては下記のサイトから確認ができます。
https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_list.md
まとめ
今回はHint利用方法について紹介しました。
Postgresqlは基本的にHint機能を提供してないですが、Extensionを利用するとSQL tuningに
必須であるHint機能が使えるようになります。