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
sql
-- 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;Warning: These settings affect ALL queries in the session. Use
SET LOCAL inside a transaction to scope them to one query.pg_hint_plan Extension
pg_hint_plan adds MySQL-style hint comments to PostgreSQL:
bash
# Install
apt-get install postgresql-15-pg-hint-plan
# Add to postgresql.conf:
# shared_preload_libraries = 'pg_hint_plan'
sql
-- 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:
sql
-- 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 planCorrelated Column Statistics
sql
-- 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';Key Takeaways
- Always fix statistics first — most bad plans come from stale or insufficient statistics
- Use
SET LOCAL enable_xxx = offinside a transaction to scope changes to one query - pg_hint_plan provides MySQL-style hints as SQL comments — useful for permanent hint application
- Use extended statistics (
CREATE STATISTICS) for correlated columns the planner misjudges
JusDB Can Help
Query planner issues can be subtle and hard to diagnose. JusDB specializes in PostgreSQL query plan optimization and statistics tuning.