EXPLAIN vs EXPLAIN ANALYZE, spotting a Seq Scan, index-only scans, which join algorithm the optimizer picks and why, and finding the actual bottleneck in a multi-step plan.
Published September 23, 2026
Indexes & Query Performance introduced EXPLAIN ANALYZE briefly. This lesson is about actually reading a plan well enough to find the real bottleneck.
EXPLAIN SELECT * FROM orders WHERE user_id = 42; -- ESTIMATED plan, query NOT executed
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42; -- ACTUAL plan, query IS executed, with real timings
EXPLAIN alone shows what the query planner thinks will happen — estimated row counts and costs, based on table statistics, without actually running the query. EXPLAIN ANALYZE actually executes the query and reports real, measured timings and row counts alongside the plan — the difference matters because the planner's estimates can be wrong (most commonly due to stale statistics, covered below), and only EXPLAIN ANALYZE reveals when estimate and reality diverge. The tradeoff: EXPLAIN ANALYZE on a slow or write-heavy query actually runs it, which for a destructive statement (an UPDATE/DELETE) is a real concern — most databases support a way to analyze without committing effects, worth knowing the specific syntax for whichever database you're using.
Seq Scan on orders (cost=0.00..1000.00 rows=50000 width=64)
Filter: (user_id = 42)
Seq Scan means the planner is reading every row in the table and filtering afterward — no index is being used for this query, regardless of whether one exists, either because none exists on the filtered column, or because the planner decided a full scan was cheaper anyway (plausible for a small table, or when the filter matches a large fraction of rows — see index selectivity in Indexes & Query Performance). Contrast:
Index Scan using idx_orders_user_id on orders (cost=0.42..8.44 rows=12 width=64)
Index Cond: (user_id = 42)
An Index Scan uses the index to jump directly to matching rows, then fetches each matching row from the table — dramatically cheaper when the filter is selective (few matching rows out of many).
Index Only Scan using idx_orders_cover on orders (cost=0.42..4.44 rows=12 width=16)
Index Cond: (user_id = 42)
If the index alone contains every column the query needs (a covering index, see Indexes & Query Performance), the planner can skip visiting the actual table rows entirely — reading only the (typically smaller, more cache-friendly) index structure. This is the fastest possible plan shape for a query that a covering index can fully satisfy.
The optimizer picks based on estimated table sizes, available indexes, and available memory — this is exactly why the same query can produce different join algorithms on different tables, or before/after adding an index, even though the SQL text never changed.
ANALYZE orders; -- refresh the planner's statistics for this table
The planner's cost estimates (and therefore its choice of join algorithm and scan type) depend on statistics about data distribution — row counts, value distributions, most-common-values lists — collected the last time ANALYZE ran (often automatically, on a schedule, but not always promptly after a large bulk insert/delete). Stale statistics mislead the planner: it might estimate a filter will match 100 rows when it actually matches 100,000 (or vice versa), leading it to choose a nested loop where a hash join would've been far cheaper, or a full scan where an index would've helped. A query that inexplicably got slower after a large data change, with no schema or query change, is a classic symptom worth checking ANALYZE freshness for first.
Hash Join (cost=500..15000 rows=1000) (actual time=2.1..145.3 rows=980 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (actual time=0.1..120.5 rows=50000 loops=1) ← the actual bottleneck
-> Hash (actual time=1.8..1.8 rows=500 loops=1)
-> Seq Scan on users u (actual time=0.05..1.5 rows=500 loops=1)
In a multi-step EXPLAIN ANALYZE output, look for the step with the largest gap between its own actual time and the time already accounted for by its children — here, the outer Seq Scan on orders alone accounts for ~120ms out of the query's ~145ms total, making it unambiguously the bottleneck, not the join itself or the smaller users scan. This "largest time delta" scan is the standard technique for going from "this query is slow" to "this specific step is why" in a plan with many nested operations.
Q: Why would a planner choose a Seq Scan over an available index? A: When the estimated selectivity is poor (the filter matches a large fraction of the table), a Seq Scan can genuinely be cheaper than an Index Scan — an Index Scan pays a cost per matched row to fetch it from the table, and if that's most of the table anyway, sequentially reading everything is less total I/O than the index-then-fetch pattern repeated for nearly every row.
Q: Does EXPLAIN ANALYZE's real execution affect the query's result for the caller? A: It genuinely executes the query with real side effects for DML statements — for a SELECT, results are typically discarded rather than returned to the analyzing tool (which reports timing/plan info, not the row data), but for an UPDATE/DELETE, the write actually happens, which is exactly why analyzing a destructive query safely usually requires wrapping it in a transaction you deliberately roll back afterward.
Q: If a hash join needs to build an in-memory hash table, what happens if that table doesn't fit in memory? A: The database spills to disk, building a partitioned/batched hash join instead — still correct, but meaningfully slower than an all-in-memory hash join, which is part of why available working memory (a tunable setting) directly affects which join algorithm the planner considers cost-effective for a given query.
Q: How often should ANALYZE run in production? A: Most databases run it automatically on a schedule or after a threshold of row changes (PostgreSQL's autovacuum/autoanalyze, for instance) — manual ANALYZE is mainly needed after an unusually large bulk operation (a big import, a mass delete) that the automatic threshold hasn't caught up to yet, where waiting for the next scheduled run would leave the planner working from meaningfully stale statistics in the meantime.