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에 다운로드합니다.
RPM 설치
Source Code로 설치
Source Code 다운로드
Source Code 압축 해제 및 설치(make && make install)
pg_hint_plan Extension 설치 확인
설치 확인
PostgreSQL 엔진이 설치된 경로에서 pg_hint_plan Extension 설치 여부를 확인합니다.
📢 PostgreSQL 설치 환경에 따라 아래 표시되는 경로는 다를 수 있습니다.
{PostgreSQL 엔진 경로}/lib경로에서 pg_hint_plan.so 라이브러리 파일 확인
{PostgreSQL 엔진 경로}/share/extension경로에서 pg_hint_plan 관련 control 파일과 sql 파일 확인
PostgreSQL에 pg_hint_plan Extension 적용
pg_hint_plaln Extension을 사용하기 위해서 PostgreSQL 설정 파일 내
shared_preload_libraries Parameter 변경 후 PostgreSQL 재기동이 필요합니다.Extension 설치
pg_hint_plan Extension 활용
Extension 설치 여부 확인
인덱스 정보 확인
Hint 작성 방법
📢 pg_hint_plan Extension이 없는 경우에는 SQL에 Hint를 작성하더라도 적용되지 않습니다
pg_hint_plan Extension 활용 : SQL Plan 제어
Scan Method : Index Scan
Hint 없이 수행
해당 Query는 비트맵 인덱스를 사용하도록 SQL Plan이 수립되었습니다.
Hint 적용
Query에서 특정 인덱스를 사용하도록 유도하기 위하여
indexscan Hint를 적용하였습니다.Join Method : Nested Loop Join + Leading
Hint 없이 수행
movie 테이블과 language 테이블을 Hash Join 하는 SQL Plan이 수립되었습니다.
Hint 적용
language 테이블을 Driving Table로 하고, movie 테이블과 Nested loop join 하도록 SQL Plan을 조정하기 위해
leading, nestloop Hint를 사용하였습니다.여러 가지 Hint 혼용 : Parallel + Rows + Set
Hint 없이 수행
Hint의 이해를 돕기 위하여 조인 방법이나 Scan 방법이 아닌 Hint를 적용하기 위해 인덱스를 모두 없애고 Query를 수행하였습니다.
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에 적용할 수 있습니다.📢 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) |
함께 보면 좋은 아티클
