pg_hint_plan

Overview

PG14-18 RPM: 1.4.5/1.5.4/1.6.3/1.7.2/1.8.1; PG18 DEB 1.8.0; 1.9.0 is PG19-only.

Version

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

Preload:

shared_preload_libraries = 'pg_hint_plan';

Create Extension:

CREATE EXTENSION pg_hint_plan;

Usage

Sources:

• README.md
• pg_hint_plan.control
• docs/hint_list.md
• docs/installation.md
• docs/requirements.md
• docs/functional_limitations.md

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

Join Method Hints

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

Loading and PostgreSQL Version Branches

Version 1.9.0 requires PostgreSQL 19. Use 1.8.1 for PG18, 1.7.2 for PG17, 1.6.3 for PG16, 1.5.4 for PG15 or 1.4.5 for PG14; do not install the PG19 build on an older server.

LOAD 'pg_hint_plan' activates hints in the current session without CREATE EXTENSION. Global preload requires a restart. To use the hint table, additionally run CREATE EXTENSION pg_hint_plan and enable pg_hint_plan.enable_hint_table. Hints cannot force a non-executable plan and query IDs ignore hint comments; verify actual plans with EXPLAIN. The current releases fix parser-loop, interrupt and planner error-path handling.