PostgreSQL Query Planner Hints: pg_hint_plan and Statistics Tuning

PostgreSQL's query planner is excellent but occasionally chooses a suboptimal plan. Unlike MySQL, PostgreSQL has no native query hints — but several tools and techniques let you guide or force the planner.

Disable Specific Plan Nodes

-- Force sequential scan (disable index scans)
SET enable_indexscan = off;
SET enable_bitmapscan = off;

-- Force nested loop (disable hash join)
SET enable_hashjoin = off;
SET enable_mergejoin = off;

-- Re-enable after your query
SET enable_indexscan = on;
SET enable_hashjoin = on;

pg_hint_plan Extension

pg_hint_plan adds MySQL-style hint comments to PostgreSQL:

# Install
apt-get install postgresql-15-pg-hint-plan

# Add to postgresql.conf:
# shared_preload_libraries = 'pg_hint_plan'

-- Force index scan on orders table
/*+ IndexScan(orders idx_orders_created_at) */
SELECT * FROM orders WHERE created_at > '2025-01-01';

-- Force nested loop join
/*+ NestLoop(orders users) */
SELECT * FROM orders JOIN users ON orders.user_id = users.id
WHERE orders.status = 'pending';

-- Force specific join order
/*+ Leading(orders users products) */
SELECT * FROM orders
JOIN users ON ...
JOIN products ON ...;

Fix Bad Plans with Statistics

Before forcing plans, try improving planner statistics:

-- Increase statistics target for poor-estimate columns
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

-- Check current estimate accuracy
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending';
-- Compare 'rows=X' (estimate) vs 'actual rows=Y' (reality)
-- Large discrepancy = bad statistics = planner chooses wrong plan

Correlated Column Statistics

-- Create extended statistics for correlated columns
CREATE STATISTICS orders_stats (dependencies) ON status, region FROM orders;
ANALYZE orders;

-- Check extended statistics
SELECT * FROM pg_statistic_ext WHERE stxname = 'orders_stats';

Establish Why the Plan Is Wrong

Start by comparing estimated and actual rows at the first node where they diverge. Refresh ANALYZE, inspect pg_stats, and increase a column statistics target only where the distribution needs a larger sample. For correlated predicates or group keys, create the relevant dependencies, mcv, or ndistinct statistics object and run ANALYZE. Extended statistics have defined limitations, so confirm the target query shape is one they can improve. Also check parameter sensitivity, prepared-plan behavior, data skew, and cost assumptions before concluding that the access method itself must be forced.

Planner GUCs Are Diagnostic Levers

The enable_seqscan, enable_indexscan, and join-method settings are a crude way to discourage plan types; PostgreSQL documentation does not present them as an absolute guarantee that an executable plan can avoid the disabled method. Use SET LOCAL in a transaction to answer a diagnostic question, then roll back. If the alternative plan is faster, explain why its estimated cost lost rather than promoting a session-wide toggle to a permanent fix.

BEGIN;
SET LOCAL enable_hashjoin = off;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...;
ROLLBACK;

Install the Exact Extension Build

pg_hint_plan is an external module and must match the PostgreSQL major version. The project's installation guide supports loading it for a session with LOAD 'pg_hint_plan' or globally with shared_preload_libraries; CREATE EXTENSION is needed for the optional hint table, not merely for comment hints. Validate package availability, restart requirements, failover nodes, replicas, and managed-service support before adding hinted SQL. Test the same module version intended for production, especially during a PostgreSQL major upgrade.

Respect the Hint Parser

The module reads hints from only the first block comment and has stricter parsing rules than ordinary SQL comments. Put related hints in that one comment. If a table is aliased, target the alias; aliases also distinguish multiple occurrences of the same relation. Bare object names are compared case-sensitively by pg_hint_plan, which differs from normal unquoted PostgreSQL name folding. Incorrect object definitions can be silently unused, while syntax and conflict details depend on logging settings. Enable pg_hint_plan.debug_print and an appropriate message level in a safe environment, then classify every hint as used, not used, duplicated, or erroneous.

Validate Without Damaging Data

Capture the baseline and hinted EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) over common, rare, empty, and skewed parameter values. Remember that EXPLAIN ANALYZE executes the statement. For data-changing SQL, wrap the test in BEGIN and ROLLBACK when side effects are fully transactional, and do not assume rollback neutralizes external function effects. Compare planning time, execution time, buffers, temporary I/O, row-estimate error, locks, and concurrency. A hint that wins for one literal can regress a generic or differently selective case.

Rollout and Removal

Attach an owner, reason, baseline plan, tested extension and PostgreSQL versions, monitoring query identifier, and review date to every production hint. Canary by role, application version, or small traffic slice. Keep the unhinted SQL available behind a reversible deployment switch. Re-test after statistics changes, index changes, growth milestones, and upgrades; comments can change query text while PostgreSQL query identifiers may ignore them, so pair database metrics with application release data. Remove the hint when statistics, schema, or planner improvements make the natural plan reliable. If a hint cannot be removed safely, it is part of the application's compatibility surface and must be tested like one.

Official References

• PostgreSQL planner method configuration
• Statistics used by the PostgreSQL planner
• EXPLAIN execution and safety
• pg_hint_plan installation and loading
• pg_hint_plan syntax, aliases, and parser behavior
• pg_hint_plan error and unused-hint reporting