SQL EXPLAIN ANALYZE
EXPLAIN's estimates vs. what actually happened — ANALYZE actually runs the query and shows both.
What & Why
EXPLAIN ANALYZE actually executes the query and reports real elapsed time and real row counts next to the planner's original estimates — the way to catch a plan that looked fine on paper but wasn't.
See How It Works
BUSINESS QUESTION
Measure the real execution of a selective recent-event query.
| id | user_id | event_name | created_at | properties |
|---|---|---|---|---|
| 1001 | 101 | signup | 2024-01-15 09:12:00+00 | {"source":"organic"} |
| 1002 | 101 | activated | 2024-01-16 14:25:00+00 | {"step":"workspace"} |
| 1003 | 102 | signup | 2024-02-10 11:08:00+00 | {"source":"paid"} |
| 1004 | 103 | report_viewed | 2024-03-12 16:40:00+00 | {"report":"retention"} |
EXAMPLE QUERY
EXPLAIN (ANALYZE, BUFFERS)
SELECT
user_id,
event_name,
created_at
FROM growth.events
WHERE event_name = 'signup'
AND created_at >= TIMESTAMPTZ '2024-01-01 00:00:00+00';RUNTIME PLAN CONTRACT — measured fields are not static data
| field | determined by | meaning |
|---|---|---|
| plan node | planner and live statistics | chosen access or processing operation |
| actual rows | rows produced during execution | observed cardinality for the node |
| actual time | database and host at execution | measured node runtime |
| buffer usage | cache and storage state | blocks touched while executing |
EXPLAIN ANALYZE executes the statement, so node choices, timings, row counts, and buffers cannot be represented honestly as fixed fixture values.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario