postgresql-indexing

When to use

• Creating or modifying PostgreSQL indexes
• Analyzing query plans with EXPLAIN
• Debugging slow queries or missing index usage
• Dropping, reindexing, or validating indexes
• Working with indexes on partitioned tables (findings, resource_finding_mappings)
• Running VACUUM or ANALYZE after index changes

Index design

Partial indexes: constant columns go in WHERE, not in the key

When a column has a fixed value for the query (e.g., state = 'completed'), put it in the WHERE clause of the index, not in the indexed columns. Otherwise the planner cannot exploit the ordering of the other columns.

-- Bad: state in the key wastes space and breaks ordering
CREATE INDEX idx_scans_tenant_state ON scans (tenant_id, state, inserted_at DESC);

-- Good: state as a filter, planner uses tenant_id + inserted_at ordering
CREATE INDEX idx_scans_tenant_ins_completed ON scans (tenant_id, inserted_at DESC)
    WHERE state = 'completed';

Column order matters

Put high-selectivity columns first (columns that filter out the most rows). For composite indexes, the leftmost column must appear in the query's WHERE clause for the index to be used.

Validating index effectiveness

Always EXPLAIN (ANALYZE, BUFFERS) after adding indexes

Never assume an index is being used. Run EXPLAIN (ANALYZE, BUFFERS) to confirm.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE email = '[email protected]';

Use Postgres EXPLAIN Visualizer (pev) to visualize query plans and identify bottlenecks.

Force index usage for testing

The planner may choose a sequential scan on small datasets. Toggle enable_seqscan = off to confirm the index path works, then re-enable it.

SET enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT ON (provider_id) provider_id
FROM scans
WHERE tenant_id = '95383b24-da01-44b5-a713-0d9920d554db'
  AND state = 'completed'
ORDER BY provider_id, inserted_at DESC;

SET enable_seqscan = on;  -- always re-enable after testing