PostgresqlでHint使用方法

## はじめに
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機能が使えるようになります。