Bottleneck
Sequential scan on orders (12.4M rows, 38 GB).
Predicate (status, created_at) is applied post-scan.
The single-column index on created_at is technically
eligible but the planner walked it backward, then filtered 11.6M rows
in memory.
The OFFSET 0 LIMIT 50 shape is a tell — keyset pagination
would help, but the index choice is the bigger win first.
EXPLAIN node trail
Recommendation — partial index
Composite (status, created_at DESC, user_id) covers the common dashboard query and lets the planner do an Index Scan + Limit without sorting.
Restricting the partial predicate keeps the index small (~840 MB vs ~3.2 GB on a full index against this row count).
Rollback
Show extended notes · maintenance · monitoring · what NOT to do
VACUUM cadence is unaffected — partial indexes still get vacuumed by the standard autovacuum schedule. Bloat will track the active subset (currently ~7% of rows match the partial predicate). Re-evaluate the predicate set quarterly; if 'returned' enters the hot status set, add it to the index WHERE clause and re-issue with CONCURRENTLY.
What NOT to do: don't add a covering INCLUDE clause yet — the dashboard query selects 8 columns, which would push this index past 1 GB and start to hurt write amplification. Revisit if you add a read-replica.
Show comparable cases from the wall · QD-996, QD-1003, QD-1029
QD-996 · same shape on a smaller events table (2.1M rows). Partial index dropped p95 980ms → 6ms. They added INCLUDE later and were fine — write rate was 8/s.
QD-1003 · status set was wider (12 values, no clear hot subset). Recommended a full composite index instead — partial wasn't selective enough.
QD-1029 · similar query against a partitioned table. The recipe needs adjusting per partition; we generated a DDL for the active 7-day partition only.
