Understanding Query Plans

Before every query runs, Postgres builds a plan — a tree of steps it estimates will be cheapest.

What & Why

A query plan is a tree of operations — scan this table, filter these rows, join to that table, sort the result — each with an estimated cost and estimated row count. The planner picks the cheapest tree it can find, not necessarily the one a human would write first.

See How It Works

BUSINESS QUESTION

Growth wants to inspect a stable signup query before deciding whether it needs an index.

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

-- Prefix this stable SELECT with EXPLAIN (FORMAT TEXT) to inspect its plan.
SELECT
  id AS event_id,
  user_id,
  event_name,
  created_at
FROM growth.events
WHERE event_name = 'signup'
  AND created_at >= TIMESTAMPTZ '2024-01-01 00:00:00+00'
ORDER BY created_at, event_id;

RESULT — stable signup rows used by EXPLAIN

event_id	user_id	event_name	created_at
1001	101	signup	2024-01-15 09:12:00+00
1003	102	signup	2024-02-10 11:08:00+00

The SELECT result is fixed; the planner's node choices and costs still depend on live database statistics.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice