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);