The EXPLAIN cheat sheet

Quick reference

Plan

EXPLAIN SELECT * FROM orders;
EXPLAIN (FORMAT JSON) SELECT 1;
EXPLAIN (VERBOSE) SELECT * FROM t;
EXPLAIN (COSTS OFF) SELECT * FROM t;
EXPLAIN SELECT * FROM t WHERE id = 1;

Analyse

EXPLAIN ANALYZE SELECT * FROM orders;
EXPLAIN (ANALYZE, TIMING OFF) SELECT 1;
EXPLAIN (ANALYZE, BUFFERS) SELECT 1;
EXPLAIN (ANALYZE, WAL) SELECT 1;
EXPLAIN (ANALYZE) SELECT count(*) FROM t;

Buffers

EXPLAIN (ANALYZE, BUFFERS) SELECT 1;
SET track_io_timing = on;
SELECT * FROM pg_statio_user_tables;
SELECT * FROM pg_statio_user_indexes;
SHOW track_io_timing;

Read

EXPLAIN (FORMAT JSON) SELECT * FROM t;
EXPLAIN (ANALYZE, FORMAT JSON) SELECT 1;
EXPLAIN (VERBOSE, FORMAT JSON) SELECT 1;
\x auto
\timing on

Explained

Plan EXPLAIN prints the plan tree with an estimated cost and row count per node. FORMAT JSON returns the tree as data you can parse, VERBOSE adds output columns, and COSTS OFF strips the estimates when you want only the shape. A Seq Scan on a large table is the line to look for.

Analyse EXPLAIN ANALYZE runs the statement and adds the actual time and rows per node. It really executes, so a DELETE deletes; wrap it in BEGIN and ROLLBACK. TIMING OFF removes the per-node timing overhead and can make a loop cheaper to measure.

Buffers EXPLAIN (ANALYZE, BUFFERS) adds shared hit, read, dirtied and written block counts. A hit is a page found in shared buffers; a read is a page fetched from the operating system. pg_statio_user_tables shows the same story per table.

Read Read the tree from the inside out. The four fields worth naming first are Node Type, Relation Name, Filter and Total Cost. In psql, \x auto and \\timing on make the JSON readable, and a Filter on a large scan is a missing index.

In one line

Plan without running

EXPLAIN (COSTS OFF) SELECT * FROM t;