February 5, 2024
Summary: The pg_hint_plan extension gives PostgreSQL ability to manually force some decisions in execution plans.
Table of Contents
Synopsis
pg_hint_plan makes it possible to tweak PostgreSQL execution plans using “hints” in SQL comments, as of /*+ SeqScan(a) */.
PostgreSQL uses a cost-based optimizer, which utilizes data statistics, not static rules. The planner (optimizer) estimates costs of each possible execution plans for a SQL statement then the execution plan with the lowest cost is executed. The planner does its best to select the best execution plan, but is not always perfect, since it doesn’t take into account some of the data properties or correlations between columns.
Description
Basic Usage
pg_hint_plan reads hinting phrases in a comment of special form given a SQL statement.
A hint can be specified by prefixing it with the sequence “/*+” and ending it with “*/”.
Hint phrases consist of hint names and parameters enclosed by parentheses and delimited by whitespaces.
Hint phrases can use newlines for readability.
In the example below, a hash join is selected as the join method while doing a sequential scan on pgbench_accounts:
=# /*+
HashJoin(a b)
SeqScan(a)
*/
EXPLAIN SELECT *
FROM pgbench_branches b
JOIN pgbench_accounts a ON b.bid = a.bid
ORDER BY a.aid;
QUERY PLAN
---------------------------------------------------------------------------------------
Sort (cost=31465.84..31715.84 rows=100000 width=197)
Sort Key: a.aid
-> Hash Join (cost=1.02..4016.02 rows=100000 width=197)
Hash Cond: (a.bid = b.bid)
-> Seq Scan on pgbench_accounts a (cost=0.00..2640.00 rows=100000 width=97)
-> Hash (cost=1.01..1.01 rows=1 width=100)
-> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=100)
(7 rows)
Hint list
The available hints are listed below.
| Group | Format | Description |
|---|---|---|
| Scan method | SeqScan(table) |
Forces sequential scan on the table. |
TidScan(table) |
Forces TID scan on the table. | |
IndexScan(table[ index...]) |
Forces index scan on the table. Restricts to specified indexes if any. | |
IndexOnlyScan(table[ index...]) |
Forces index-only scan on the table. Restricts to specified indexes if any. Index scan may be used if index-only scan is not available. | |
BitmapScan(table[ index...]) |
Forces bitmap scan on the table. Restricts to specified indexes if any. | |
IndexScanRegexp(table[ POSIX Regexp...])IndexOnlyScanRegexp(table[ POSIX Regexp...])BitmapScanRegexp(table[ POSIX Regexp...]) |
Forces index scan, index-only scan (For PostgreSQL 9.2 and later) or bitmap scan on the table. Restricts to indexes that matches the specified POSIX regular expression pattern. | |
NoSeqScan(table) |
Forces to not do sequential scan on the table. | |
NoTidScan(table) |
Forces to not do TID scan on the table. | |
NoIndexScan(table) |
Forces to not do index scan and index-only scan on the table. | |
NoIndexOnlyScan(table) |
Forces to not do index only scan on the table. | |
NoBitmapScan(table) |
Forces to not do bitmap scan on the table. | |
| Join method | NestLoop(table table[ table...]) |
Forces nested loop for the joins on the tables specified. |
HashJoin(table table[ table...]) |
Forces hash join for the joins on the tables specified. | |
MergeJoin(table table[ table...]) |
Forces merge join for the joins on the tables specified. | |
NoNestLoop(table table[ table...]) |
Forces to not do nested loop for the joins on the tables specified. | |
NoHashJoin(table table[ table...]) |
Forces to not do hash join for the joins on the tables specified. | |
NoMergeJoin(table table[ table...]) |
Forces to not do merge join for the joins on the tables specified. | |
| Join order | Leading(table table[ table...]) |
Forces join order as specified. |
Leading(<join pair>) |
Forces join order and directions as specified. A join pair is a pair of tables and/or other join pairs enclosed by parentheses, which can make a nested structure. | |
| Behavior control on Join | Memoize(table table[ table...]) |
Allows the topmost join of a join among the specified tables to Memoize the inner result. Not enforced. |
NoMemoize(table table[ table...]) |
Inhibits the topmost join of a join among the specified tables from Memoizing the inner result. | |
| Row number correction | Rows(table table[ table...] correction) |
Corrects row number of a result of the joins on the tables specified. The available correction methods are absolute (# |
| Parallel query configuration | Parallel(table <# of workers> [soft|hard]) |
Enforces or inhibits parallel execution of the specified table. <# of workers> is the desired number of parallel workers, where zero means inhibiting parallel execution. If the third parameter is soft (default), it just changes max_parallel_workers_per_gather and leaves everything else to the planner. Hard enforces the specified number of workers. |
| GUC | Set(GUC-param value) |
Sets GUC parameter to the value defined while planner is running. |