pg_hint_plan

What pg_hint_plan does

PostgreSQL does not have built-in query hints. This is a deliberate design choice — the PostgreSQL community prefers that the planner make autonomous decisions based on statistics, and that developers fix bad plans by improving statistics or restructuring queries rather than hard-coding plan choices.

pg_hint_plan provides the escape hatch. It reads specially formatted comments at the beginning of a SQL statement and uses them to influence the planner's decisions. The hints operate as strong suggestions — they override cost-based choices for the specified operations while letting the planner handle everything else normally.

This is not an everyday tool. It is the fire extinguisher behind the glass — you want it mounted and ready, even if reaching for it means something has gone rather wrong.

When to use pg_hint_plan

• Emergency plan fixes — a query suddenly regressed because the planner chose a bad plan after an ANALYZE or a statistics change. Pin the good plan with a hint while you investigate the root cause.
• Oracle migrations — Oracle developers accustomed to hints can maintain the same plan control during migration while queries are being rewritten for PostgreSQL's planner.
• Plan testing — force different plan shapes to benchmark alternatives. "What if this join used a hash join instead of a nested loop?" Combine with EXPLAIN ANALYZE for side-by-side comparisons.
• Known planner weaknesses — correlated columns, complex CTEs, or unusual data distributions where the planner consistently underestimates. Hints compensate until the underlying statistics issue is resolved.

Installation and setup

pg_hint_plan is not a contrib module — it must be installed separately from the PGDG repository or compiled from source. Like pg_stat_statements, it requires shared_preload_libraries and a server restart.

-- Install from PGDG repository (Debian/Ubuntu)
sudo apt-get install postgresql-17-pg-hint-plan

-- Add to shared_preload_libraries (requires restart)
shared_preload_libraries = 'pg_hint_plan'

-- Create the extension
CREATE EXTENSION pg_hint_plan;

Hint syntax

Hints are written as specially formatted block comments immediately before or at the beginning of a SQL statement. The comment must start with /*+ (no space between /* and +).

Scan method hints

The most common use — telling the planner precisely which scan method to employ, rather than trusting its judgement on the matter.

-- Force an index scan on the orders table
/*+
  IndexScan(orders idx_orders_customer_id)
*/
SELECT * FROM orders WHERE customer_id = 42;

-- Force a sequential scan (useful for testing)
/*+
  SeqScan(orders)
*/
SELECT * FROM orders WHERE customer_id = 42;

Join method hints

-- Force a hash join between orders and customers
/*+
  HashJoin(orders customers)
*/
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- Force a nested loop join
/*+
  NestLoop(orders customers)
*/
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- Force a merge join
/*+
  MergeJoin(orders customers)
*/
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

Combining multiple hints

One hint block, multiple instructions. The more you specify, the less the planner decides for itself — which is rather the point when you have lost confidence in its choices.

-- Combine multiple hints in a single block
/*+
  IndexScan(o idx_orders_status)
  HashJoin(o c)
  Leading((c o))
*/
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Available hints

-- Scan method hints
SeqScan(table)           -- Force sequential scan
IndexScan(table [index]) -- Force index scan (optional specific index)
IndexOnlyScan(table idx) -- Force index-only scan
BitmapScan(table [idx])  -- Force bitmap scan
TidScan(table)           -- Force TID scan
NoSeqScan(table)         -- Prevent sequential scan
NoIndexScan(table)       -- Prevent index scan
NoBitmapScan(table)      -- Prevent bitmap scan

-- Join method hints
NestLoop(t1 t2)          -- Force nested loop join
HashJoin(t1 t2)          -- Force hash join
MergeJoin(t1 t2)         -- Force merge join
NoNestLoop(t1 t2)        -- Prevent nested loop
NoHashJoin(t1 t2)        -- Prevent hash join
NoMergeJoin(t1 t2)       -- Prevent merge join

-- Join order hints
Leading((t1 t2))         -- Force join order
Leading((t1 (t2 t3)))    -- Force nested join order

Persistent hints via the hints table

For production use, pg_hint_plan can store hints in a table rather than requiring them in the SQL text. This is particularly useful when you cannot modify the application's SQL — the hint is matched by normalized query pattern. A sensible arrangement, though I should note that each row in this table is a commitment you are making to your future self.

-- Store hints in a table for persistent plan control
-- Useful for managing hints across deployments
INSERT INTO hint_plan.hints (
  norm_query_string, application_name, hints
) VALUES (
  'SELECT * FROM orders WHERE customer_id = ?;',
  '',
  'IndexScan(orders idx_orders_customer_id)'
);

Debugging hints

When a hint does not seem to take effect, enable debug output to see what pg_hint_plan received and what it applied.

-- Enable hint debugging to verify hints are applied
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO log;

/*+
  IndexScan(orders idx_orders_customer_id)
*/
SELECT * FROM orders WHERE customer_id = 42;

-- Check the log output to confirm the hint was used

Cloud availability

How Gold Lapel relates

I should be direct about this, because the contrast here is the sharpest you will find in our glossary. pg_hint_plan and Gold Lapel address the same fundamental problem — the query planner sometimes makes poor choices — but from opposite directions entirely.

pg_hint_plan is the manual transmission. You identify a bad plan, write a hint, deploy it, and then carry the responsibility of remembering it exists. The hint is static. It does not adapt when your data distribution shifts. It does not know when the planner's default plan has improved enough to make the hint unnecessary — or worse, counterproductive. Every hint you write is a bet that you will be there to revisit it.

Gold Lapel is the adaptive automatic. It observes query patterns, detects when the planner's choices are suboptimal, and applies optimizations — index recommendations, materialized views, query rewriting — that evolve as your data changes. No hint table to maintain. No comments to deploy. No bet against your future attention span.

The two are not mutually exclusive, and I would not suggest otherwise. Gold Lapel passes hinted queries through unchanged. Teams sometimes reach for pg_hint_plan as the immediate fix — the tourniquet — while Gold Lapel handles the longer-term care. That is a perfectly sound arrangement.