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.

Advanced business practice