IntermediatePerformance & Indexes

Index-only scan vs table scan

The query

SQL

-- Create covering index to enable index-only scan
CREATE INDEX idx_emp_covering
ON employees(dept_id, salary)
INCLUDE (first_name, last_name); -- INCLUDE avoids heap fetch

-- Query that can use index-only scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT first_name, last_name, salary
FROM employees
WHERE dept_id = 3
ORDER BY salary DESC;

Tested against PostgreSQL 16

Note

“INCLUDE columns in index (PostgreSQL 11+) enables index-only scans. No heap access = faster query.

Tables referenced

employees10 cols