Use partial and expression indexes for filtered queries

Use partial and expression indexes for filtered queries | BeforeMerge Rules | BeforeMerge

Why this matters

When queries always target a subset (e.g. active rows) or filter on an expression (e.g. lower(email)), a full-column index is larger and slower to maintain than needed. Partial indexes cover only matching rows; expression indexes let the planner use the index for computed predicates.

-- partial: only index unprocessed jobs
CREATE INDEX ON jobs (created_at) WHERE status = 'pending';
 
-- expression: support case-insensitive lookups
CREATE INDEX ON users (lower(email));

Why This Matters

Related Rules

Catch this automatically on every PR

Why this matters