This is where it all comes together. A page is timing out, and the query behind it is "show me a customer's recent orders, newest first." You have the tools now — EXPLAIN, indexes, an eye for a bad plan. Let's run a real investigation from complaint to fix.
The seed loaded 300,000 orders across 5,000 customers, with no index beyond the primary key. Get your bearings first:
SELECT count(*) FROM orders;
The complaint: one customer's recent orders
Here is the query the app runs — the ten most recent orders for a single customer:
SELECT id, created_at, status, amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 10;
It returns instantly as text, but that tells you nothing — the result is tiny. The cost is in how Postgres found those rows. Never guess; measure.
Step 1 — reproduce and measure
EXPLAIN (ANALYZE, BUFFERS) actually runs the query and reports what happened: the plan the planner chose, its row estimates versus reality, the time each node took, and how many pages it read. Run it on the slow query:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, status, amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 10;
You'll get something like this (your exact numbers will differ):
Limit (cost=8523.19..8523.21 rows=10 ...) (actual time=41.203..41.205 rows=10 ...)
Buffers: shared hit=1936
-> Sort (cost=8523.19..8523.34 rows=60 ...) (actual time=41.201..41.202 ...)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on orders (cost=0.00..8521.89 rows=60 ...)
(actual time=0.312..41.150 rows=60 ...)
Filter: (customer_id = 42)
Rows Removed by Filter: 299940
Planning Time: 0.140 ms
Execution Time: 41.240 ms
Read it bottom-up, and two lines tell the whole story:
Seq Scan on orders— Postgres read the entire table to answer a question about one customer.Rows Removed by Filter: 299940— it inspected 300,000 rows and threw away all but ~60. That is 99.98% wasted work.
Then a Sort on on top, before the could take ten. On 300k rows this adds milliseconds; multiply by every customer hitting the page and you have your timeout.