[PostgreSQL] PG_HINT_PLAN 설치

1. 개요

해당 포스팅에서는 PG_HINT_PLAN의 설치 과정에 대해 다루어보고자 합니다.

 

타 DBMS (이 포스팅에서는 ORACLE로 가정)에 익숙하신 분들은

실행계획 분석 하에 Query를 적절히 튜닝하는 경우가 있었을 것입니다.

 

PostgreSQL 에서도 PG_HINT_PLAN을 설치하여 실행계획을 제어할 수 있습니다.

 

  ** 들어가기에 앞서,

-> ORACLE 에서는 hint를 이용하여 실행계획을 제어할 수 있습니다.

(간혹 비효율적이라면 hint가 무시되는 케이스도 존재합니다.)

 

하지만 PostgreSQL의 PG_HINT_PLAN은 Plan Tree 자체를 변경합니다.

따라서, PostgreSQL은 PG_HINT_PLAN의 내용을 무시할 수 없습니다.

즉 ORACLE의 Optimizer와는 달리, 유연함을 기대할 수 없을 수도 있습니다.

 

잘못된 hint를 줌으로 인해 오히려 퍼포먼스가 저하되는 경우가 존재할 수 있을 것이로 생각합니다.

따라서, 실제 적용에 앞서 충분한 검토와 테스트가 동반되어야 할 것입니다.

 

2. PG_HINT_PLAN 설치

1) 압축 파일 해제

[postgres@localhost pg_hint_plan]$ ls
pg_hint_plan-REL15_1_5_0.tar.gz
[postgres@localhost pg_hint_plan]$ tar xvf pg_hint_plan-REL15_1_5_0.tar.gz

 

2) PG_HINT_PLAN 소스트리 구성 및 빌드

[postgres@localhost pg_hint_plan]$ cd pg_hint_plan-REL15_1_5_0/

[postgres@localhost pg_hint_plan-REL15_1_5_0]$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -I. -I./ -I/postgres/pgsql/include/server -I/postgres/pgsql/include/internal  -D_GNU_SOURCE   -c -o pg_hint_plan.o pg_hint_plan.c -MMD -MP -MF .deps/pg_hint_plan.Po
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC pg_hint_plan.o -L/postgres/pgsql/lib   -Wl,--as-needed -Wl,-rpath,'/postgres/pgsql/lib',--enable-new-dtags  -shared -o pg_hint_plan.so

[postgres@localhost pg_hint_plan-REL15_1_5_0]$ make install
/usr/bin/mkdir -p '/postgres/pgsql/share/extension'
/usr/bin/mkdir -p '/postgres/pgsql/share/extension'
/usr/bin/mkdir -p '/postgres/pgsql/lib'
/usr/bin/install -c -m 644 .//pg_hint_plan.control '/postgres/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pg_hint_plan--1.3.0.sql .//pg_hint_plan--1.3.0--1.3.1.sql .//pg_hint_plan--1.3.1--1.3.2.sql .//pg_hint_plan--1.3.2--1.3.3.sql .//pg_hint_plan--1.3.3--1.3.4.sql .//pg_hint_plan--1.3.5--1.3.6.sql .//pg_hint_plan--1.3.4--1.3.5.sql .//pg_hint_plan--1.3.6--1.3.7.sql .//pg_hint_plan--1.3.7--1.3.8.sql .//pg_hint_plan--1.3.8--1.4.sql .//pg_hint_plan--1.4--1.4.1.sql .//pg_hint_plan--1.4.1--1.5.sql  '/postgres/pgsql/share/extension/'
/usr/bin/install -c -m 755  pg_hint_plan.so '/postgres/pgsql/lib/'

 

3) postgresql.conf 추가

[postgres@localhost pg_hint_plan-REL15_1_5_0]$ cd $PGDATA

[postgres@localhost data]$ vi postgresql.conf

shared_preload_libraries = 'pg_hint_plan'
pg_hint_plan.enable_hint = on
pg_hint_plan.debug_print=off
pg_hint_plan.message_level=info

* shared_preload_libraries : 라이브러리 로드 (extension 추가 시 필요)

* pg_hint_plan.enable_hint : PG_HINT_PLAN 활성화

* 그 외 debug_level과 message_level은 선택사항

 

4) PG_HINT_PLAN Extension 설치

postgres=# create extension pg_hint_plan;
CREATE EXTENSION