On a table called order_events holding about four million e-commerce log rows, a query pulling the last 30 days of activity for a given customer_id kept stalling during a late-night incident. The developer had added an index on customer_id three weeks earlier, but EXPLAIN ANALYZE showed PostgreSQL running a sequential scan across the whole table instead, turning what should have been a 40-millisecond query into one taking about four seconds.

SELECT * FROM order_events
WHERE customer_id = 8842
AND created_at > now() - interval '30 days'
ORDER BY created_at DESC;

The cause was a mismatch between the index and the query's ORDER BY clause. The index covered only customer_id, so PostgreSQL could locate a customer's rows quickly but then had to load them all into memory and sort by created_at afterward. Replacing it with a composite index on (customer_id, created_at DESC) matched the query's actual access pattern and cut execution time to nine milliseconds.

The same failure had appeared before, on a healthcare scheduling app and a logistics analytics tool, always because an index matched only the WHERE clause while sorting, grouping or joining columns were ignored. That pattern led to a four-step review process for every slow query: run EXPLAIN ANALYZE and check for a Seq Scan on tables beyond a few thousand rows; look at ORDER BY, GROUP BY and JOIN columns to decide if a composite index beats separate single-column ones; check column selectivity, since low-cardinality columns like an order_status field with only three values rarely help alone and should be paired with a more selective column; and finally record real millisecond timings from EXPLAIN ANALYZE before and after a fix instead of judging by feel.