Queries that should be fast end up doing full sequential scans because the composite index doesn't match the query's column order. The optimizer ignores the index entirely when the leading column isn't in the WHERE clause — a common surprise in production.
This module reproduces the problem: create a composite index on (a, b), query by b alone, and show the sequential scan in EXPLAIN ANALYZE. Then demonstrate how leading-column selectivity drives the optimizer's decision.
Fix strategies should include: reordering index columns, adding a separate single-column index, covering indexes, and partial indexes. Benchmark query latency and EXPLAIN plans across strategies with a pre-seeded dataset large enough to make the difference measurable (~100K+ rows).
AC-1 through AC-5 apply. Tests verify index usage via EXPLAIN output assertions.
Queries that should be fast end up doing full sequential scans because the composite index doesn't match the query's column order. The optimizer ignores the index entirely when the leading column isn't in the WHERE clause — a common surprise in production.
This module reproduces the problem: create a composite index on
(a, b), query bybalone, and show the sequential scan inEXPLAIN ANALYZE. Then demonstrate how leading-column selectivity drives the optimizer's decision.Fix strategies should include: reordering index columns, adding a separate single-column index, covering indexes, and partial indexes. Benchmark query latency and
EXPLAINplans across strategies with a pre-seeded dataset large enough to make the difference measurable (~100K+ rows).AC-1 through AC-5 apply. Tests verify index usage via
EXPLAINoutput assertions.