EXPLAIN Basics

EXPLAIN shows the planner's chosen access path before you pay the cost of running a query. EXPLAIN (ANALYZE, BUFFERS) runs the query and adds timing plus buffer I/O so you can see what actually happened on PostgreSQL 18.4.

Recipe

Quick-reference recipe card - copy-paste ready.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL)
SELECT o.id, o.total
FROM orders o
WHERE o.status = 'shipped'
  AND o.created_at >= now() - interval '7 days';

When to reach for this: Any slow or surprising query on a production-like dataset. Capture the plan before and after index or statistics changes.

Working Example

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now(),
  total numeric(12,2) NOT NULL
);
 
CREATE INDEX orders_status_created_idx ON orders (status, created_at);
 
INSERT INTO orders (customer_id, status, created_at, total)
SELECT
  (random() * 10000)::bigint,
  CASE WHEN random() < 0.05 THEN 'shipped' ELSE 'pending' END,
  now() - (random() * interval '90 days'),
  (random() * 500)::numeric(12,2)
FROM generate_series(1, 200000);
 
ANALYZE orders;
 
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, total
FROM orders
WHERE status = 'shipped'
  AND created_at >= now() - interval '7 days';

A healthy plan often shows Index Scan on orders_status_created_idx with low rows removed by filter and modest shared hit counts.

What this demonstrates:

ANALYZE before comparing plans
Reading scan type and index name
Interpreting actual time and rows vs planner estimates
Using BUFFERS to spot cache misses

Deep Dive

How It Works

The planner builds a tree of nodes (scan, join, sort, aggregate). Each node has estimated and actual row counts.
EXPLAIN alone is cheap and safe on production. ANALYZE executes the query, so use it on staging or with LIMIT guards on destructive statements.
BUFFERS reports shared hit (cache) and shared read (disk). High read on a hot query means more RAM or better indexing may help.
SETTINGS (PostgreSQL 12+) shows non-default GUCs that influenced the plan, useful when random_page_cost or work_mem differ per role.

EXPLAIN Options at a Glance

Option	Purpose
`ANALYZE`	Execute query; show actual timings
`BUFFERS`	Buffer hit/read per node
`VERBOSE`	Output column lists and schema names
`SETTINGS`	Show planner-affecting GUC values
`WAL`	WAL bytes generated (writes)
`FORMAT JSON`	Machine-readable plan for tools

SQL Notes

-- Save a plan for a ticket (no execution)
EXPLAIN (FORMAT JSON)
SELECT count(*) FROM orders WHERE status = 'pending';
 
-- Compare estimates only (safe on prod)
EXPLAIN (VERBOSE)
SELECT * FROM orders WHERE customer_id = 42;

Gotchas

Running EXPLAIN ANALYZE on DELETE/UPDATE without care - You mutate production data. Fix: Wrap in a transaction and ROLLBACK, or test on a snapshot.
Trusting cold-cache numbers once - First run after restart looks worse than steady state. Fix: Run twice; compare the second ANALYZE result.
Ignoring row estimate drift - rows=1000 vs actual rows=85000 signals bad statistics or correlated predicates. Fix: ANALYZE and check extended statistics.
Reading only total time - A cheap outer node can hide an expensive nested loop inside. Fix: Find the node with the largest actual time share.
Forgetting search_path - Plans differ when schema qualification changes. Fix: Set search_path explicitly in the session you capture.

Alternatives

Alternative	Use When	Don't Use When
`auto_explain`	Continuous slow-query capture	You need interactive iteration
`pg_stat_statements`	Aggregate cost by query text	You need one-off plan detail
`EXPLAIN (GENERIC_PLAN)`	Prepared statement planning	Table stats are very stale

FAQs

Is EXPLAIN safe on production?

EXPLAIN without ANALYZE is read-only planning. EXPLAIN ANALYZE runs the query; use caution with writes and heavy scans.

What does "Seq Scan" mean?

PostgreSQL read the heap sequentially. Often correct for large fractions of the table or tiny tables.

Why do estimated and actual rows differ?

Stale statistics, correlated columns, or skewed data. Run ANALYZE and consider extended statistics.

Should I use TEXT or JSON format?

TEXT for humans in tickets. JSON for explain.dalibo.com, pganalyze, or CI plan diff tools.

Does EXPLAIN show lock waits?

No. Check pg_stat_activity and wait_event_type for lock contention separate from plan shape.

What is "Buffers: shared hit=..."?

Pages found in PostgreSQL shared buffers (cache) vs read from OS/disk.

Can I EXPLAIN a prepared statement?

Yes: EXPLAIN EXECUTE stmt_name(args) or EXPLAIN (GENERIC_PLAN) EXECUTE ... for generic plans.

How do I hide sensitive literals?

Use parameter placeholders in application queries; avoid pasting PII into shared plan logs.

Does PostgreSQL 18 change EXPLAIN output?

Core tree format is stable; new planner nodes may appear. Always pin version in regression baselines.

What should I read next?

Seq scan vs index scan trade-offs and join algorithm pages in this section.

Seq Scan vs Index Scan - When each scan wins
Nested Loop, Hash Join, Merge Join - Join nodes in plans
Bad Plan Case Studies - Real mis-estimate patterns
Statistics Basics - Why estimates go wrong

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

EXPLAIN Basics

Recipe

Quick-reference recipe card - copy-paste ready.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL)
SELECT o.id, o.total
FROM orders o
WHERE o.status = 'shipped'
  AND o.created_at >= now() - interval '7 days';

When to reach for this: Any slow or surprising query on a production-like dataset. Capture the plan before and after index or statistics changes.

Working Example

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now(),
  total numeric(12,2) NOT NULL
);
 
CREATE INDEX orders_status_created_idx ON orders (status, created_at);
 
INSERT INTO orders (customer_id, status, created_at, total)
SELECT
  (random() * 10000)::bigint,
  CASE WHEN random() < 0.05 THEN 'shipped' ELSE 'pending' END,
  now() - (random() * interval '90 days'),
  (random() * 500)::numeric(12,2)
FROM generate_series(1, 200000);
 
ANALYZE orders;
 
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, total
FROM orders
WHERE status = 'shipped'
  AND created_at >= now() - interval '7 days';

A healthy plan often shows Index Scan on orders_status_created_idx with low rows removed by filter and modest shared hit counts.

What this demonstrates:

ANALYZE before comparing plans
Reading scan type and index name
Interpreting actual time and rows vs planner estimates
Using BUFFERS to spot cache misses

Deep Dive

How It Works

The planner builds a tree of nodes (scan, join, sort, aggregate). Each node has estimated and actual row counts.
EXPLAIN alone is cheap and safe on production. ANALYZE executes the query, so use it on staging or with LIMIT guards on destructive statements.
BUFFERS reports shared hit (cache) and shared read (disk). High read on a hot query means more RAM or better indexing may help.
SETTINGS (PostgreSQL 12+) shows non-default GUCs that influenced the plan, useful when random_page_cost or work_mem differ per role.

EXPLAIN Options at a Glance

Option	Purpose
`ANALYZE`	Execute query; show actual timings
`BUFFERS`	Buffer hit/read per node
`VERBOSE`	Output column lists and schema names
`SETTINGS`	Show planner-affecting GUC values
`WAL`	WAL bytes generated (writes)
`FORMAT JSON`	Machine-readable plan for tools

SQL Notes

-- Save a plan for a ticket (no execution)
EXPLAIN (FORMAT JSON)
SELECT count(*) FROM orders WHERE status = 'pending';
 
-- Compare estimates only (safe on prod)
EXPLAIN (VERBOSE)
SELECT * FROM orders WHERE customer_id = 42;

Gotchas

Running EXPLAIN ANALYZE on DELETE/UPDATE without care - You mutate production data. Fix: Wrap in a transaction and ROLLBACK, or test on a snapshot.
Trusting cold-cache numbers once - First run after restart looks worse than steady state. Fix: Run twice; compare the second ANALYZE result.
Ignoring row estimate drift - rows=1000 vs actual rows=85000 signals bad statistics or correlated predicates. Fix: ANALYZE and check extended statistics.
Reading only total time - A cheap outer node can hide an expensive nested loop inside. Fix: Find the node with the largest actual time share.
Forgetting search_path - Plans differ when schema qualification changes. Fix: Set search_path explicitly in the session you capture.

Alternatives

Alternative	Use When	Don't Use When
`auto_explain`	Continuous slow-query capture	You need interactive iteration
`pg_stat_statements`	Aggregate cost by query text	You need one-off plan detail
`EXPLAIN (GENERIC_PLAN)`	Prepared statement planning	Table stats are very stale

FAQs

Is EXPLAIN safe on production?

EXPLAIN without ANALYZE is read-only planning. EXPLAIN ANALYZE runs the query; use caution with writes and heavy scans.

What does "Seq Scan" mean?

PostgreSQL read the heap sequentially. Often correct for large fractions of the table or tiny tables.

Why do estimated and actual rows differ?

Stale statistics, correlated columns, or skewed data. Run ANALYZE and consider extended statistics.

Should I use TEXT or JSON format?

TEXT for humans in tickets. JSON for explain.dalibo.com, pganalyze, or CI plan diff tools.

Does EXPLAIN show lock waits?

No. Check pg_stat_activity and wait_event_type for lock contention separate from plan shape.

What is "Buffers: shared hit=..."?

Pages found in PostgreSQL shared buffers (cache) vs read from OS/disk.

Can I EXPLAIN a prepared statement?

Yes: EXPLAIN EXECUTE stmt_name(args) or EXPLAIN (GENERIC_PLAN) EXECUTE ... for generic plans.

How do I hide sensitive literals?

Use parameter placeholders in application queries; avoid pasting PII into shared plan logs.

Does PostgreSQL 18 change EXPLAIN output?

Core tree format is stable; new planner nodes may appear. Always pin version in regression baselines.

What should I read next?

Seq scan vs index scan trade-offs and join algorithm pages in this section.

Seq Scan vs Index Scan - When each scan wins
Nested Loop, Hash Join, Merge Join - Join nodes in plans
Bad Plan Case Studies - Real mis-estimate patterns
Statistics Basics - Why estimates go wrong

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

EXPLAIN Basics

Search Documentation

EXPLAIN Basics