IntermediateAdvanced PostgreSQL

Using EXPLAIN to optimize a slow query

The query

SQL

-- Before optimization: no index
EXPLAIN (ANALYZE, BUFFERS)
SELECT e.*, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 80000
  AND e.status = 'active';

-- After: create composite index
CREATE INDEX idx_emp_status_salary
ON employees(status, salary)
WHERE status = 'active'; -- partial index

-- Re-run EXPLAIN to verify improvement
EXPLAIN (ANALYZE, BUFFERS)
SELECT e.*, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 80000
  AND e.status = 'active';

Tested against PostgreSQL 16

Note

“Always EXPLAIN before and after adding indexes to verify improvement. Look for "Index Scan" vs "Seq Scan".

Tables referenced

employees10 cols
departments5 cols