pg_hint_plan
pg_hint_plan
pg_hint_plan : Give PostgreSQL ability to manually force some decisions in execution plans.
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 2780 | pg_hint_plan | pg_hint_plan | 1.8.0 | FEAT | BSD 3-Clause | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--sLd-- | No | Yes | Yes | Yes | no | no |
| Relationships | |
|---|---|
| Schemas | hint_plan |
| See Also | pg_show_plans pg_store_plans pg_stat_statements hypopg pg_qualstats auto_explain index_advisor pg_profile |
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PGDG | 1.8.0 | 18 17 16 15 14 | pg_hint_plan | - |
| RPM | PGDG | 1.8.0 | 18 17 16 15 14 | pg_hint_plan_$v | - |
| DEB | PGDG | 1.8.0 | 18 17 16 15 14 | postgresql-$v-pg-hint-plan | - |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
el8.aarch64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
el9.x86_64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
el9.aarch64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
el10.x86_64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
el10.aarch64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
d12.x86_64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
d12.aarch64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
d13.x86_64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
d13.aarch64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
u22.x86_64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
u22.aarch64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
u24.x86_64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
u24.aarch64 | PGDG 1.8.0 | PGDG 1.7.1 | PGDG 1.6.2 | PGDG 1.5.3 | PGDG 1.4.4 |
Source
Install
Make sure PGDG repo available:
pig repo add pgdg -u # add pgdg repo and update cacheInstall this extension with pig:
pig install pg_hint_plan; # install via package name, for the active PG version
pig install pg_hint_plan -v 18; # install for PG 18
pig install pg_hint_plan -v 17; # install for PG 17
pig install pg_hint_plan -v 16; # install for PG 16
pig install pg_hint_plan -v 15; # install for PG 15
pig install pg_hint_plan -v 14; # install for PG 14Config this extension to shared_preload_libraries:
shared_preload_libraries = 'pg_hint_plan';Create this extension with:
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 estimateParallel Query Control
/*+ Parallel(table 4 hard) */ -- Force 4 parallel workers
/*+ Parallel(table 0 hard) */ -- Disable parallelismGUC 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 |
Last updated on