AdvancedPerformance & Indexes

Query plan hints with pg_hint_plan extension

The query

SQL

-- Install: CREATE EXTENSION pg_hint_plan;

-- Force index scan (override optimizer)
/*+ IndexScan(e idx_emp_dept_sal) */
SELECT e.first_name, e.salary
FROM employees e
WHERE e.dept_id = 5
ORDER BY e.salary DESC;

-- Force nested loop join
/*+ NestLoop(e d) */
SELECT e.first_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 80000;

Tested against PostgreSQL 16

Note

“pg_hint_plan overrides PostgreSQL's query planner. Use ONLY when planner makes provably wrong choice.

Tables referenced

employees10 cols
departments5 cols