Missing index on a hot endpoint

Checkout and search latency jumped 20–50×. CPU stays moderate, yet individual queries sit for seconds. The slowdown started after a new filter shipped, and it scales with table size.

A new WHERE / ORDER BY predicate isn't covered by any index, so the planner falls back to a Seq Scan over a large table. Every request now reads millions of rows.

1. Find the offending statement in pg_stat_statements (highest total time).
2. Run EXPLAIN (ANALYZE, BUFFERS) and confirm a Seq Scan on the big table.
3. Propose an index matching the WHERE + ORDER BY (right columns, right order).
4. Re-check the plan before/after and confirm the Seq Scan became an Index Scan.

Generate an orders table of 5–20M rows and an endpoint filtering by user_id / status / created_at, then drop the index that makes it fast. The learner sees the real plan and the real latency.

• DetectLocated the slow query and recognised the Seq Scan as the cause.
• FixCreated an index that matches the predicate and verified the new plan.
• TrapAvoided adding a redundant or wrongly-ordered index that the planner ignores.