EXPLAIN shows the plan and the optimiser’s estimates, and the estimates are frequently wrong — a query planned for 40 rows that reads 400,000 looks entirely healthy.
EXPLAIN ANALYZE
SELECT c.name, COUNT(*) FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at > '2021-01-01'
GROUP BY c.id;
-> Table scan on <temporary> (actual time=412..419 rows=8814 loops=1)
-> Nested loop inner join (cost=88104 rows=41)
(actual time=0.4..380 rows=412008 loops=1)
-- estimated 41. actually 412,008.The gap between rows=41 and actual rows=412008 is the whole diagnostic, and it is invisible in plain EXPLAIN. A wrong estimate usually means stale statistics or a correlation the optimiser cannot see between two predicates, and the fix is ANALYZE TABLE or a histogram before it is an index. The one caution is that EXPLAIN ANALYZE actually runs the query, so it is not something to point at a DELETE.