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

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

FAQs

Related

• 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+.