pg_hint_plan
Give PostgreSQL ability to manually force some decisions in execution plans.
Repository
ossc-db/pg_hint_plan
https://github.com/ossc-db/pg_hint_plan
Source
pg_hint_plan-REL18_1_8_0.tar.gz
pg_hint_plan-REL18_1_8_0.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_hint_plan | 1.8.0 | FEAT | BSD 3-Clause | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 2780 | pg_hint_plan | No | Yes | No | Yes | No | No | hint_plan |
| Related | pg_show_plans pg_store_plans pg_stat_statements hypopg pg_qualstats auto_explain index_advisor pg_profile |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 1.8.0 | 1817161514 | pg_hint_plan | - |
| RPM | PGDG | 1.8.0 | 1817161514 | pg_hint_plan_$v | - |
| DEB | PGDG | 1.8.0 | 1817161514 | postgresql-$v-pg-hint-plan | - |
Install
You can install pg_hint_plan directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_hint_plan; # Install for current active PG version
pig ext install -y pg_hint_plan -v 18 # PG 18
pig ext install -y pg_hint_plan -v 17 # PG 17
pig ext install -y pg_hint_plan -v 16 # PG 16
pig ext install -y pg_hint_plan -v 15 # PG 15
pig ext install -y pg_hint_plan -v 14 # PG 14
dnf install -y pg_hint_plan_18 # PG 18
dnf install -y pg_hint_plan_17 # PG 17
dnf install -y pg_hint_plan_16 # PG 16
dnf install -y pg_hint_plan_15 # PG 15
dnf install -y pg_hint_plan_14 # PG 14
apt install -y postgresql-18-pg-hint-plan # PG 18
apt install -y postgresql-17-pg-hint-plan # PG 17
apt install -y postgresql-16-pg-hint-plan # PG 16
apt install -y postgresql-15-pg-hint-plan # PG 15
apt install -y postgresql-14-pg-hint-plan # PG 14
Create Extension:
CREATE EXTENSION pg_hint_plan;
Usage
pg_hint_plan: Give PostgreSQL ability to manually force some decisions in execution plans
pg_hint_plan overrides the PostgreSQL query planner’s decisions using SQL comment hints, allowing you to force specific scan methods, join strategies, and join orders.
Hint Syntax
Hints are embedded in SQL comments prefixed with /*+ and closed with */:
/*+
HashJoin(a b)
SeqScan(a)
*/
SELECT *
FROM pgbench_branches b
JOIN pgbench_accounts a ON b.bid = a.bid
ORDER BY a.aid;
Scan Method Hints
| Hint | Description |
|---|---|
SeqScan(table) | Force sequential scan |
IndexScan(table [index...]) | Force index scan (optionally restrict to specific indexes) |
IndexOnlyScan(table [index...]) | Force index-only scan |
BitmapScan(table [index...]) | Force bitmap scan |
TidScan(table) | Force TID scan |
NoSeqScan(table) | Prevent sequential scan |
NoIndexScan(table) | Prevent index scan and index-only scan |
NoIndexOnlyScan(table) | Prevent index-only scan |
NoBitmapScan(table) | Prevent bitmap scan |
NoTidScan(table) | Prevent TID scan |
IndexScanRegexp(table [regexp...]) | Force index scan with regex-matched index names |
DisableIndex(table index...) | Disable specific indexes during planning |
Join Method Hints
| Hint | Description |
|---|---|
NestLoop(t1 t2 [t3...]) | Force nested loop join |
HashJoin(t1 t2 [t3...]) | Force hash join |
MergeJoin(t1 t2 [t3...]) | Force merge join |
NoNestLoop(t1 t2 [t3...]) | Prevent nested loop join |
NoHashJoin(t1 t2 [t3...]) | Prevent hash join |
NoMergeJoin(t1 t2 [t3...]) | Prevent merge join |
Memoize(t1 t2 [t3...]) | Allow memoization of inner result |
NoMemoize(t1 t2 [t3...]) | Prevent memoization |
Join Order Hints
-- Simple left-deep join order
/*+ Leading(a b c) */
-- Explicit join tree with nesting
/*+ Leading((a (b c))) */
Row Number Correction
/*+ Rows(a b #100) */ -- Set to absolute number
/*+ Rows(a b +100) */ -- Add to estimate
/*+ Rows(a b -100) */ -- Subtract from estimate
/*+ Rows(a b *2.0) */ -- Multiply estimate
Parallel Query Control
/*+ Parallel(table 4 hard) */ -- Force 4 parallel workers
/*+ Parallel(table 0 hard) */ -- Disable parallelism
GUC Parameter Override
/*+ Set(random_page_cost 1.0) Set(seq_page_cost 1.0) */
SELECT * FROM my_table WHERE id = 42;
GUC Configuration
| Parameter | Description | Default |
|---|---|---|
pg_hint_plan.enable_hint | Enable or disable hints globally | on |
pg_hint_plan.enable_hint_table | Enable hint table for query-based hints | off |
pg_hint_plan.debug_print | Print debug info for applied hints | off |
pg_hint_plan.parse_messages | Log level for hint parsing messages | INFO |
pg_hint_plan.message_level | Log level for debug messages | LOG |
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.