How the database runs a query
🎯You'll be able to
Read an EXPLAIN ANALYZE plan well enough to see where the time goes.
🏠The everyday picture
To find one student's file in a school office, the clerk can either open every cabinet drawer (slow, but works for a tiny office) or look up the index card that says "cabinet 3, drawer 2". Which one they choose depends on how big the office is and whether the card exists.
📘What it really is
- SQL says what you want. The planner decides how: which table to read first, whether to use an index, how to join.
EXPLAINshows the plan;EXPLAIN ANALYZEruns the query and shows actual times and row counts for each step.- Words to recognise:
- Seq Scan: read every row of a table (fine for small tables, costly for big ones);
- Index Scan / Bitmap Index Scan: use an index to find rows;
- Nested Loop, Hash Join, Merge Join: ways of joining;
-
rows=... (estimated) vs actual rows=...: big differences mean the planner's statistics are off (run
ANALYZE). -
Read from the innermost, most indented lines outwards; look for the step with the largest actual time and the largest rows that are thrown away by a filter (
Rows Removed by Filter). -
Measure on realistic data. A plan on 50 test rows tells you little about 120,000.
📝A worked example
In the kit, one of the starter menu's per-dish queries:
EXPLAIN ANALYZE SELECT coalesce(sum(oi.qty), 0) FROM order_items oi JOIN orders o ON o.id = oi.order_id
WHERE oi.menu_item_id = 181 AND o.kitchen_id = 7 AND o.placed_at >= now() - interval '30 days';
What to look for in the output: a Seq Scan on order_items with Rows Removed by Filter in the hundreds of thousands, because
there is no index on menu_item_id. The query is only a few milliseconds, but the starter runs it (and a ratings query) once per
dish, 30 dishes per kitchen. That pattern is Lesson 3.
🌍Where it's used
- Investigating slow endpoints reported by users or monitoring.
- Code review of new queries on big tables.
- Capacity planning, before a table grows ten times.
📝Case scenario
A developer tests a new report on their laptop with 200 orders: 3 ms. In production, with 4 million orders, it takes 9 seconds,
because the plan switches from a quick scan of a tiny table to a scan of a huge one. Running EXPLAIN ANALYZE on a copy of
production-sized (synthetic) data before release would have shown it.
🖱️Try it
Run the query above with EXPLAIN ANALYZE in psql. Note the scan type on each table, the actual time and the rows removed by
filter. Success: you can point to the line that does the most work.
⚠️Common mistakes
- Reading
EXPLAINwithoutANALYZE. Sign: you trust estimates that are far from reality. Fix: useEXPLAIN ANALYZEon a safe copy (it really runs the query). - Testing on tiny data. Sign: everything is fast until launch. Fix: test with realistic volumes, like the kit's 120,000 orders.
- Running
EXPLAIN ANALYZEon a write in production. Sign: the "test" really deletes or updates rows. Fix: wrap it inBEGIN; ... ROLLBACK;or use a copy.
🛠️For your project
Project 2 starts by recording EXPLAIN ANALYZE for the menu queries before you change anything.
✅Quick check
What does "Rows Removed by Filter: 239,000" tell you?
Show the answer
The database read many rows only to throw them away, a sign that an index or a better query could help.
Why run ANALYZE after loading lots of data?
Show the answer
It updates the statistics the planner uses to estimate rows and choose a plan.