Problem Statement
When would you create a partial or filtered index, and what is the trade-off?
Explanation
Create a filtered index when queries target a well-defined subset, like active=true, recent dates, or non-NULL values. The smaller index is cheaper to maintain and can be highly selective for those queries.
The trade-off is coverage. Queries that fall outside the predicate cannot use the index. Be sure the predicate matches how the application filters in practice so the benefit is realized.
Code Solution
SolutionRead Only
-- PostgreSQL CREATE INDEX idx_invoices_paid_recent ON invoices(paid_at) WHERE paid = true AND paid_at >= CURRENT_DATE - INTERVAL '90 days';
Practice Sets
This question appears in the following practice sets:
