EasyPerformance & Indexes

Partial index: index only active employees

The query

SQL

-- Only index active employees (smaller, faster)
CREATE INDEX idx_active_emp ON employees(last_name, first_name)
WHERE status = 'active';

-- This query WILL use the partial index:
SELECT * FROM employees WHERE last_name = 'Smith' AND status = 'active';

-- This query WON'T (missing WHERE status='active'):
SELECT * FROM employees WHERE last_name = 'Smith';

Tested against PostgreSQL 16

Note

“Partial indexes are smaller and faster for frequently-filtered subsets. Only useful if filter matches index WHERE clause.

Tables referenced

employees10 cols