EXPLAIN and EXPLAIN ANALYZE

Shows the plan PostgreSQL chooses for a query and, with ANALYZE, what actually happened.

Syntax

EXPLAIN query;                       -- estimated plan, query not run
EXPLAIN ANALYZE query;               -- runs the query; actual rows and times
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT | JSON) query;

The plan is a tree; read it from the most indented node outward. Each node shows cost=startup..total rows=estimated width=bytes. With ANALYZE: actual time=... rows=... loops=....

Common nodes:

Seq Scan: reads the whole table — fine for small tables or when most rows are needed.
Index Scan / Index Only Scan: walks an index; Only means the table itself was barely touched.
Bitmap Index Scan → Bitmap Heap Scan: collect matching row locations, then fetch them in physical order.
Nested Loop (small outer side, indexed inner), Hash Join (build a hash on one side), Merge Join (both sides sorted).
Sort, HashAggregate / GroupAggregate, Limit.

What to look for:

Big gaps between estimated and actual rows: statistics are stale (run ANALYZE table) or the condition is hard to estimate.
A Seq Scan with a Filter that discards almost every row on a large table: a candidate for an index.
Rows Removed by Filter, loops (multiply per-loop numbers by loops).
EXPLAIN ANALYZE executes the statement — wrap INSERT/UPDATE/DELETE in BEGIN ... ROLLBACK.
Costs are in arbitrary planner units, not milliseconds. Timings vary run to run; compare plan shapes.
Since PostgreSQL 18, EXPLAIN ANALYZE includes buffer statistics by default.

Examples

Estimated plan perf

EXPLAIN
SELECT u.country, count(*)
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE o.status = 'refunded'
GROUP BY u.country;

Look for the scan type on orders, the join method and the aggregation node.

Actual rows and timing perf

EXPLAIN ANALYZE
SELECT count(*) FROM events WHERE event_type = 'refund';

Compare the estimated rows with the actual rows; the Seq Scan reports how many rows the filter removed.

ANALYZE a data-changing statement safely perf

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'cancelled' WHERE order_id <= 100;
ROLLBACK;

EXPLAIN ANALYZE really performs the update; the ROLLBACK undoes it.

Pitfalls

Running EXPLAIN ANALYZE on a DELETE/UPDATE outside a transaction (it really changes data).
Reading cost as milliseconds.
Judging a plan on a tiny table: the planner rightly prefers Seq Scans there.
Forgetting to ANALYZE after loading or changing lots of data.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: EXPLAIN, EXPLAIN ANALYZE (8.0.18+), EXPLAIN FORMAT=TREE.
SQL Server: Graphical/XML execution plans; SET SHOWPLAN_XML ON or SET STATISTICS PROFILE ON.
Oracle: EXPLAIN PLAN FOR ...; then SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY).
SQLite: EXPLAIN QUERY PLAN.