DB 인사이드 | PostgreSQL Extension - PG_HINT_PLAN

 

 

 

PostgreSQL은 Oracle과는 달리 SQL Hint 기능이 기본으로 제공되지 않기 때문에, 별도의 pg_hint_plan Extension을 설치하여 사용할 수 있습니다. SQL Hint 기능은 SQL문을 실행할 때, Optimizer가 생성한 SQL Plan을 사용자가 원하는 방식으로 변경할 수 있도록 Optimizer에게 알려주는 역할이 됩니다. pg_hint_plan Extension이 설치되어 있지 않으면 SQL문에 Hint를 사용하여도 Hint가 적용되지 않으므로 SQL문장에 Hint를 사용하기 위해서는 반드시 설치되어야 합니다.

📢 본 문서에서는 pg_hint_plan Extension 설치 방법과 Hint 사용 방법에 대해 기술합니다.

 

 

pg_hint_plan Extension 설치 파일 준비

PostgreSQL 버전과 OS 버전에 따라 pg_hint_plan Extension 파일이 상이하므로, 설치 환경에 맞는 Extension 설치 파일을 준비합니다. pg_hint_plan Extension 설치 파일은 Github에서 다운로드할 수 있습니다.

📢 pg_hint_plan Extension Github URL : https://github.com/ossc-db/pg_hint_plan

pg_hint_plan Github를 들어가면 오른쪽 중간 Release(빨간 박스)를 클릭하여 PostgreSQL 버전과 OS 버전에 따라 필요한 설치 파일을 확인할 수 있습니다. 파일의 경우, pg_hint_plan{PostgreSQL_Version} 으로 구분되어 있으니 스크롤을 내려 사용 중인 PostgreSQL 버전과 매칭되는 제목을 찾아 Assets에서 OS 버전에 맞는 파일을 준비합니다.

 

 

pg_hint_plan Extension 설치

📢 본 문서에서는 Rocky Linux 8.9 / PostgreSQL 16.1에서 테스트를 진행합니다. RPM과 Source Code를 사용하여 pg_hint_plan Extension을 설치할 수 있습니다.

RPM으로 설치

RPM 다운로드

Github에서 RPM 설치 파일의 링크를 복사하여 Server에 다운로드합니다.

## 설치파일 다운로드
[root@tech-225 ~] wget https://github.com/ossc-db/pg_hint_plan/releases/download/REL16_1_6_0/pg_hint_plan16-1.6.0-1.el8.x86_64.rpm

## 다운로드 파일 확인
[root@tech-225 ~] ls pg_hint_plan*
pg_hint_plan16-1.6.0-1.el8.x86_64.rpm

 

RPM 설치

[root@tech-225 ~] rpm -Uvh pg_hint_plan16-1.6.0-1.el8.x86_64.rpm
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:pg_hint_plan16-1.6.0-1.el8       ################################# [100%]

 

Source Code로 설치

Source Code 다운로드

[postgres@tech-231 ~]$ wget https://github.com/ossc-db/pg_hint_plan/archive/refs/tags/REL16_1_6_0.tar.gz
[postgres@tech-231 ~]$ ls 
REL16_1_6_0.tar.gz

 

Source Code 압축 해제 및 설치(make && make install)

[postgres@tech-231 ~]$ tar -zxvf REL16_1_6_0.tar.gz
[postgres@tech-231 ~]$ ls 
pg_hint_plan-REL16_1_6_0   REL16_1_6_0.tar.gz
[postgres@tech-231 ~]$ cd pg_hint_plan-REL16_1_6_0
[postgres@tech-231 ~]$ make && make install

 

 

pg_hint_plan Extension 설치 확인

설치 확인

PostgreSQL 엔진이 설치된 경로에서 pg_hint_plan Extension 설치 여부를 확인합니다.

📢 PostgreSQL 설치 환경에 따라 아래 표시되는 경로는 다를 수 있습니다.

• {PostgreSQL 엔진 경로}/lib 경로에서 pg_hint_plan.so 라이브러리 파일 확인

[root@tech-225 lib] pwd
/usr/pgsql-16/lib

[root@tech-225 lib] ls pg_hint_plan*
pg_hint_plan.so

• {PostgreSQL 엔진 경로}/share/extension 경로에서 pg_hint_plan 관련 control파일과 sql파일 확인

[root@tech-225 extension] pwd
/usr/pgsql-16/share/extension

[root@tech-225 extension] ls pg_hint_plan*
pg_hint_plan--1.3.0--1.3.1.sql  pg_hint_plan--1.3.3--1.3.4.sql  pg_hint_plan--1.3.7--1.3.8.sql  pg_hint_plan--1.4.1--1.4.2.sql  pg_hint_plan.control
pg_hint_plan--1.3.0.sql         pg_hint_plan--1.3.4--1.3.5.sql  pg_hint_plan--1.3.8--1.3.9.sql  pg_hint_plan--1.4.2--1.5.sql
pg_hint_plan--1.3.1--1.3.2.sql  pg_hint_plan--1.3.5--1.3.6.sql  pg_hint_plan--1.3.9--1.4.sql    pg_hint_plan--1.5--1.5.1.sql
pg_hint_plan--1.3.2--1.3.3.sql  pg_hint_plan--1.3.6--1.3.7.sql  pg_hint_plan--1.4--1.4.1.sql    pg_hint_plan--1.5.1--1.6.0.sql

 

PostgreSQL에 pg_hint_plan Extension 적용

pg_hint_plaln Extension을 사용하기 위해서 PostgreSQL 설정 파일 내 shared_preload_libraries Parameter 변경 후 PostgreSQL 재기동이 필요합니다.

[root@tech-225 extension] vi /var/lib/pgsql/16/data/postgresql.conf
...
shared_preload_libraries = 'pg_hint_plan'
...

[root@tech-225 extension] systemctl restart postgresql-16.service

 

Extension 설치

postgres=# CREATE EXTENSION pg_hint_plan ;
CREATE EXTENSION
postgres=# \dx
                    List of installed extensions
     Name     | Version |   Schema   |         Description
--------------+---------+------------+------------------------------
 pg_hint_plan | 1.4.2   | hint_plan  |
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

 

 

pg_hint_plan Extension 활용

Extension 설치 여부 확인

postgres=# SELECT * FROM pg_extension ;

  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion |   extconfig   | extcondition
-------+--------------------+----------+--------------+----------------+------------+---------------+--------------
 14482 | plpgsql            |       10 |           11 | f              | 1.0        |               |
 16814 | pg_hint_plan       |       10 |        16813 | f              | 1.6.0      | {16816,16815} | {"",""}
 16825 | pg_stat_statements |       10 |         2200 | t              | 1.10       |               |

 

인덱스 정보 확인

postgres=# SELECT * FROM pg_indexes WHERE tablename = 'movie' ;

 schemaname | tablename |    indexname     | tablespace |                                 indexdef
------------+-----------+------------------+------------+--------------------------------------------------------------------------
 public     | movie     | idx_release_year |            | CREATE INDEX idx_release_year ON public.movie USING btree (release_year)

 

Hint 작성 방법

📢 pg_hint_plan Extension이 없는 경우에는 SQL에 Hint를 작성하더라도 적용되지 않습니다

postgres=# SELECT /*+ Hint 내용 작성 */ film_id , release_year FROM movie ;

 

 

pg_hint_plan Extension 확용 : SQL Plan 제어

Scan Method : Index Scan

Hint 없이 수행

해당 Query는 비트맵 인덱스를 사용하도록 SQL Plan이 수립되었습니다.

postgres=# EXPLAIN
SELECT *
FROM   movie
WHERE  release_year = 2003 ;

QUERY PLAN
---------------------------------------------------------------------------------
 Bitmap Heap Scan on movie  (cost=4.93..80.73 rows=101 width=384)
   Recheck Cond: ((release_year)::integer = 2003)
   ->  Bitmap Index Scan on idx_release_year  (cost=0.00..4.91 rows=101 width=0)
         Index Cond: ((release_year)::integer = 2003)

 

Hint 적용

Query에서 특정 인덱스를 사용하도록 유도하기 위하여 indexscan Hint를 적용하였습니다.

postgres=# EXPLAIN
SELECT /*+ indexscan ( movie idx_release_year ) */
       film_id , release_year
FROM   movie
WHERE  release_year = 2003 ;

QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using idx_release_year on movie  (cost=0.15..241.28 rows=101 width=8)
   Index Cond: ((release_year)::integer = 2003)

 

Join Method : Nested Loop Join + Leading

Hint 없이 수행

movie 테이블과 language 테이블을 Hash Join 하는 SQL Plan이 수립되었습니다.

postgres=# EXPLAIN
SELECT m.title, m.description, l.name
FROM   movie m JOIN language l ON m.language_id = l.language_id ;

QUERY PLAN
-----------------------------------------------------------------------
 Hash Join  (cost=1.14..93.62 rows=1000 width=193)
   Hash Cond: (m.language_id = l.language_id)
   ->  Seq Scan on movie m  (cost=0.00..88.00 rows=1000 width=111)
   ->  Hash  (cost=1.06..1.06 rows=6 width=88)
         ->  Seq Scan on language l  (cost=0.00..1.06 rows=6 width=88)

 

Hint 적용

language 테이블을 Driving Table로 하고, movie 테이블과 Nested loop join 하도록 SQL Plan을 조정하기 위해 leading, nestloop Hint를 사용하였습니다.

postgres=# EXPLAIN
SELECT /*+ nestloop (m l) leading ((l m))*/
       m.title , m.description , l.name
FROM   movie m JOIN language l ON m.language_id = l.language_id ;

QUERY PLAN
-------------------------------------------------------------------------
 Nested Loop  (cost=0.00..181.56 rows=1000 width=193)
   Join Filter: (l.language_id = m.language_id)
   ->  Seq Scan on language l  (cost=0.00..1.06 rows=6 width=88)
   ->  Materialize  (cost=0.00..93.00 rows=1000 width=111)
         ->  Seq Scan on movie m  (cost=0.00..88.00 rows=1000 width=111)

 

여러 가지 Hint 혼용 : Parallel + Rows + Set

Hint 없이 수행

Hint의 이해를 돕기 위하여 조인 방법이나 Scan 방법이 아닌 Hint를 적용하기 위해 인덱스를 모두 없애고 Query를 수행하였습니다.

postgres=# EXPLAIN
SELECT m.title, m.release_year, p.amount
FROM   payment p JOIN rental r ON p.rental_id = r.rental_id
JOIN   inventory i ON r.inventory_id = i.inventory_id
JOIN   movie m ON m.film_id = i.film_id
WHERE  p.amount > 1
ORDER BY release_year ;

QUERY PLAN
--------------------------------------------------------------------------------------------
 Sort  (cost=2057.20..2086.83 rows=11852 width=25)
   Sort Key: m.release_year
   ->  Hash Join  (cost=739.56..1255.24 rows=11852 width=25)
         Hash Cond: (i.film_id = m.film_id)
         ->  Hash Join  (cost=639.06..991.78 rows=11852 width=8)
               Hash Cond: (r.inventory_id = i.inventory_id)
               ->  Hash Join  (cost=510.99..832.56 rows=11852 width=10)
                     Hash Cond: (p.rental_id = r.rental_id)
                     ->  Seq Scan on payment p  (cost=0.00..290.45 rows=11852 width=10)
                           Filter: (amount > '1'::numeric)
                     ->  Hash  (cost=310.44..310.44 rows=16044 width=8)
                           ->  Seq Scan on rental r  (cost=0.00..310.44 rows=16044 width=8)
               ->  Hash  (cost=70.81..70.81 rows=4581 width=6)
                     ->  Seq Scan on inventory i  (cost=0.00..70.81 rows=4581 width=6)
         ->  Hash  (cost=88.00..88.00 rows=1000 width=23)
               ->  Seq Scan on movie m  (cost=0.00..88.00 rows=1000 width=23)

 

Hint 적용

Query 수행과 SQL Plan 조절에 도움이 되는 Hint( Rows, Parallel, Set )를 적용하였습니다.

Rows Hint를 사용함으로써 SQL Plan에 출력되는 최종 rows를 특정 값으로 변경하여 출력하도록 적용하였습니다. Parallel Hint에는 soft의 경우(default) 병렬 허용을 하되 Optimizer의 판단에 자율적으로 적용을 맡기는 병렬 수행으로 적용하고 , hard의 경우 강제 병렬 수행하도록 적용합니다.

Set Hint는 PostgreSQL Configuration(postgresql.conf) 파일에 포함된 SQL Plan 관련 Parameter 중 일부를 SQL Plan에 적용할 수 있습니다.

postgres=# EXPLAIN
SELECT /*+ Rows(p r i m #1234) Parallel (r 2 hard) Parallel(m 5) Set(random_page_cost 2.0)*/
 m.title , m.release_year , p.amount
FROM   payment p JOIN rental r ON p.rental_id = r.rental_id
JOIN   inventory i ON r.inventory_id = i.inventory_id
JOIN   movie m ON m.film_id = i.film_id
WHERE  p.amount > 1
ORDER BY release_year ;

QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Sort  (cost=1008.17..1011.25 rows=1234 width=25)
   Sort Key: m.release_year
   ->  Hash Join  (cost=429.12..944.81 rows=1234 width=25)
         Hash Cond: (i.film_id = m.film_id)
         ->  Hash Join  (cost=328.62..681.34 rows=11852 width=8)
               Hash Cond: (r.inventory_id = i.inventory_id)
               ->  Hash Join  (cost=200.55..522.12 rows=11852 width=10)
                     Hash Cond: (p.rental_id = r.rental_id)
                     ->  Seq Scan on payment p  (cost=0.00..290.45 rows=11852 width=10)
                           Filter: (amount > '1'::numeric)
                     ->  Hash  (cost=0.00..0.00 rows=16044 width=8)
                           ->  Gather  (cost=0.00..0.00 rows=16044 width=8)
                                 Workers Planned: 2
                                 ->  Parallel Seq Scan on rental r  (cost=0.00..0.00 rows=6685 width=8)
               ->  Hash  (cost=70.81..70.81 rows=4581 width=6)
                     ->  Seq Scan on inventory i  (cost=0.00..70.81 rows=4581 width=6)
         ->  Hash  (cost=88.00..88.00 rows=1000 width=23)
               ->  Seq Scan on movie m  (cost=0.00..88.00 rows=1000 width=23)

📢 random_page_cost : sequential이 아닌 read의 디스크 page fetch 추정치 설정 Parameter (default 4.0)

 

 

pg_hint_plan Extension에서 제공하는 Hint List

📢 Format에서 [ ] 안에 작성된 항목은 필수 옵션이 아니므로 작성하지 않아도 Hint 적용이 가능합니다.

Scan Method

Hint List	Description	Format
SeqScan	테이블 순차 스캔을 하도록 적용	SeqScan(Table)
TidScan	테이블 TID 스캔을 하도록 적용	TidScan(Table)
IndexScan	테이블에서 특정 인덱스가 있는 경우, 인덱스 스캔을 하도록 적용. 이 때 테이블에 인덱스가 하나거나 인덱스를 특정할 필요가 없는 경우, 테이블만 작성 가능	IndexScan(Table [index])
IndexOnlyScan	테이블에서 특정 인덱스가 있는 경우, 인덱스 전용 스캔을 하도록 적용. 이 때 테이블에 인덱스가 하나거나 인덱스를 특정할 필요가 없는 경우, 테이블만 작성 가능	IndexOnlyScan(Table [index])
BitmapScan	테이블에서 특정 인덱스가 있는 경우, 비트맵 스캔을 하도록 적용. 이 때 테이블에 인덱스가 하나거나 인덱스를 특정할 필요가 없는 경우, 테이블만 작성 가능	BitmapScan(Table [index])
IndexScanRegexp	테이블에서 특정 POSIX 정규식 패턴과 일치하는 인덱스로 인덱스 스캔을 하도록 적용. 이 때 테이블에 특정 정규식 패턴이 하나거나 정규식 패턴을 특정할 필요가 없는 경우, 테이블만 작성 가능	IndexScanRegexp(Table [POSIX Regrexp..])
IndexOnlyScanRegexp	테이블에서 특정 POSIX 정규식 패턴과 일치하는 인덱스로 인덱스 전용 스캔을 하도록 적용 (9.2v 이상). 이 때 테이블에 특정 정규식 패턴이 하나거나 정규식 패턴을 특정할 필요가 없는 경우, 테이블만 작성 가능	IndexOnlyScanRegexp(Table [POSIX Regrexp..])
BitmapScanRegexp	테이블에서 특정 POSIX 정규식 패턴과 일치하는 인덱스로 비트맵 스캔을 하도록 적용. 이 때 테이블에 특정 정규식 패턴이 하나거나 정규식 패턴을 특정할 필요가 없는 경우, 테이블만 작성 가능	BitmapScanRegexp(Table [POSIX Regrexp..])
NoSeqScan	테이블에서 순차 스캔을 하지 않도록 적용	NoSeqScan(Table)
NoTidScan	테이블에서 TID 스캔을 하지 않도록 적용	NoTidScan(Table)
NoIndexScan	테이블에서 인덱스 스캔이나 인덱스 전용 스캔을 하지 않도록 적용	NoIndexScan(Table)
NoIndexOnlyScan	테이블에서 인덱스 전용 스캔을 하지 않도록 적용	NoIndexOnlyScan(Table)
NoBitmapScan	테이블에서 비트맵 스캔 하지 않도록 적용	NoBitmapScan(Table)

Join Method

Hint List	Description	Format
Leading	조인의 순서를 지정하도록 적용. 조인 방향까지 제어하려면 Format에서 괄호를 우측 (2)번으로 작성해야하고 이때 Table1이 Driving 또는 Outer Table	(1) Leading(Table Table) (2) Leading((Table1 Table2))
NestLoop	특정 테이블이 중첩 루프(Nested Loop) 조인으로 풀리도록 적용	NestLoop(TableTable [Table..])
HashJoin	특정 테이블이 해시 조인으로 풀리도록 적용	HashJoin(Table Table [Table..])
MergeJoin	특정 테이블이 머지 조인으로 풀리도록 적용	MergeJoin(Table Table [Table..])
NoNestLoop	특정 테이블이 중첩 루프(Nested Loop) 조인으로 풀리지 않도록 적용	NoNestLoop(Table Table [Table..])
NoHashJoin	특정 테이블이 해시 조인으로 풀리지 않도록 적용	NoHashJoin(Table Table [Table..])
NoMergeJoin	특정 테이블이 머지 조인으로 풀리지 않도록 적용	NoMergeJoin(Table Table [Table..])

Behavior Control on Join

Hint List	Description	Format
Memoize	Memoize가 활성화된 경우, 특정 테이블 간의 조인 중 최상위 조인의 결과값을 캐싱하도록 적용	Memoize(Table Table [ Table...])
NoMemoize	특정 테이블 사이의 조인에서 내부 결과를 캐싱하여 기억하는 것을 금지하도록 적용	NoMemoize(Table Table [ Table...])

Row Number Correction

Hint List	Description	Format
Rows	절대(#), 더하기(+), 빼기(-) 및 곱하기(*)를 이용하여 특정 테이블에 대한 조인 결과의 행 번호를 수정하도록 적용	Rows(Table Table [ Table...] correction)

Parallel Query Configuration

Hint List	Description	Format
Parallel	테이블 스캔에 병렬 수행을 적용 ( default : soft )	Parallel(Table <# of workers> [soft|hard])

GUC(Grand Unified Configuration) : postgresql.conf Parameter

Hint List	Description	Format
Set	Optimizer가 실행되는 동안 SQL Plan과 관련된 GUC 매개변수 값을 Hint에 정의된 값으로 적용. 다른 Hint와 충돌한 경우 적용되지않음	Set(GUC-param value)