Explain what EXPLAIN ANALYZE tells you about a query

EXPLAIN shows the query planner's intention: the operator tree it picked, the estimated cost of each node, and the estimated rows. EXPLAIN ANALYZE executes the query and reports what the machine actually did: per-node actual rows, actual time, and execution details. The whole point is the gap between the two — the plan is a prediction, ANALYZE is the weather report.

A concrete example, Postgres:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT u.name, count(o.id)
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.plan = 'pro'
GROUP BY u.name;
 
HashAggregate  (cost=1250.11..1250.32 rows=21 width=40) (actual rows=21)
  ->  Hash Join  (cost=196.12..1246.50 rows=725 width=36) (actual rows=725)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=0.00..909.99 rows=49999 width=4)
              (actual rows=50000)
        ->  Hash  (cost=174.61..174.61 rows=1721 width=36) (actual rows=1720)
              ->  Seq Scan on users u  (cost=0.00..174.61 rows=1721 width=36)
                    (actual rows=1720)
Planning Time: 0.4 ms
Execution Time: 12.8 ms

Reading order matters. First look at the operator tree: which table is scanned how (Seq Scan vs Index Scan), which join method (Hash Join, Nested Loop, Merge Join), and where the work concentrates. Then compare rows (the planner's estimate) against actual rows on each node — a mismatch of 100x or more means the plan was built for the wrong query, and that's usually where the real problem lives (stale statistics, correlated predicates). Then read the timings: which node burns the milliseconds, and the sum — total execution time is what the client actually waits for, minus locking and network.

The flags change what you see. BUFFERS reports shared hit/read counts per node — it turns "fast" into "fast because it's cached" and exposes random I/O in index scans. TIMING OFF runs with minimal overhead when you only care about row counts and plan shape. ANALYZE, FORMAT JSON gives machine-readable output for automation. Note that ANALYZE runs the query: for slow or writing queries (INSERT ... RETURNING, DDL), run plain EXPLAIN first, or wrap in BEGIN; ... ROLLBACK; so nothing lands.

What it deliberately doesn't tell you: lock waits and replication lag — a query can spend seconds in wait_event with a beautiful plan, which is why pg_stat_activity is the complement. And the estimated cost is in arbitrary units, not seconds — it's the planner's internal ranking, useful for comparing alternative plans, never for telling time.

The strong answer closes with a habit: read rows-versus-actual first, because a good plan for a wrong estimate is still a slow query.