Reading EXPLAIN and tuning a slow query

EXPLAIN query shows the plan the planner chose, without running it. EXPLAIN ANALYZE query runs the query and adds what actually happened. Because it executes, EXPLAIN ANALYZE on an UPDATE or DELETE really changes data; wrap it in BEGIN ... ROLLBACK.

Anatomy of a plan

A plan is a tree. Each node pulls rows from its children (the indented nodes under its ->), so you read it inside out: the most indented nodes run first, and the top node returns the result.

1CREATE TABLE customers (id int PRIMARY KEY, city text NOT NULL, country text NOT NULL, signed_up timestamp NOT NULL);2INSERT INTO customers3SELECT g,4       (ARRAY['Tbilisi','Batumi','Kutaisi','Berlin','Munich','Hamburg','Warsaw','Krakow','Gdansk','Paris'])[1 + g % 10],5       (ARRAY['GE','GE','GE','DE','DE','DE','PL','PL','PL','FR'])[1 + g % 10],6       timestamp '2022-01-01' + g * interval '1 hour'7FROM generate_series(1, 20000) AS g;8ANALYZE customers;

1EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)2SELECT c.id, d.city3FROM customers c4JOIN customers d ON d.id = c.id + 15WHERE c.city = 'Paris' AND c.id < 200;

On each node:

• cost=0.58..167.42 — the planner's estimate in arbitrary units: the cost before the first row can be returned, then the total. Only useful for comparing plans, not as milliseconds.
• rows=20 — estimated rows per execution of the node; width is the estimated average row size in bytes.
• actual rows=1 loops=20 — the node ran 20 times and returned 1 row on average per loop. Total rows = rows x loops. A Nested Loop runs its inner side once per outer row, which is where loops come from.
• Rows Removed by Filter — rows the node read and threw away; a big number here on a large table is often the first clue.

TIMING OFF and SUMMARY OFF keep the output free of timings so it is the same on every run; on a real server you want timings on, and EXPLAIN (ANALYZE, BUFFERS) to see how many pages were found in PostgreSQL's shared buffers (hit) and how many had to be read in from outside them (read, which may still come from the operating system's cache).

Estimates drive everything

The planner chooses join order, join type and access path from its row estimates, which come from statistics gathered by ANALYZE (autovacuum runs it for you after enough changes). When a node's estimate is off by an order of magnitude, every decision above it is built on that error: a nested loop chosen for "20 rows" that really gets 200,000 is a classic slow query.

1CREATE FUNCTION plan_of(q text, opts text DEFAULT 'COSTS OFF') RETURNS text LANGUAGE plpgsql2  SET max_parallel_workers_per_gather = 0 AS $$3DECLARE4  line text;5  result text := '';6BEGIN7  FOR line IN EXECUTE 'EXPLAIN (' || opts || ') ' || q LOOP8    result := result || CASE WHEN result = '' THEN '' ELSE ' | ' END || trim(line);9  END LOOP;10  RETURN result;11END $$;12CREATE TABLE customers (id int PRIMARY KEY, city text NOT NULL, country text NOT NULL, signed_up timestamp NOT NULL);13INSERT INTO customers14SELECT g,15       (ARRAY['Tbilisi','Batumi','Kutaisi','Berlin','Munich','Hamburg','Warsaw','Krakow','Gdansk','Paris'])[1 + g % 10],16       (ARRAY['GE','GE','GE','DE','DE','DE','PL','PL','PL','FR'])[1 + g % 10],17       timestamp '2022-01-01' + g * interval '1 hour'18FROM generate_series(1, 20000) AS g;19ANALYZE customers;

1CREATE TEMP TABLE before AS2SELECT plan_of($$SELECT * FROM customers WHERE city = 'Tbilisi' AND country = 'GE'$$,3               'ANALYZE, TIMING OFF, SUMMARY OFF') AS plan_before;4 5CREATE STATISTICS customers_city_country (dependencies) ON city, country FROM customers;6ANALYZE customers;7 8SELECT plan_before,9       plan_of($$SELECT * FROM customers WHERE city = 'Tbilisi' AND country = 'GE'$$,10               'ANALYZE, TIMING OFF, SUMMARY OFF') AS plan_after11FROM before;

Sargable predicates

A predicate is sargable (search-argument-able) when the indexed column stands alone on one side of the comparison. Wrap the column in a cast, function or arithmetic and the index on the bare column cannot be used; move the work to the constant side instead.

1CREATE FUNCTION plan_of(q text, opts text DEFAULT 'COSTS OFF') RETURNS text LANGUAGE plpgsql2  SET max_parallel_workers_per_gather = 0 AS $$3DECLARE4  line text;5  result text := '';6BEGIN7  FOR line IN EXECUTE 'EXPLAIN (' || opts || ') ' || q LOOP8    result := result || CASE WHEN result = '' THEN '' ELSE ' | ' END || trim(line);9  END LOOP;10  RETURN result;11END $$;12CREATE TABLE customers (id int PRIMARY KEY, city text NOT NULL, country text NOT NULL, signed_up timestamp NOT NULL);13INSERT INTO customers14SELECT g,15       (ARRAY['Tbilisi','Batumi','Kutaisi','Berlin','Munich','Hamburg','Warsaw','Krakow','Gdansk','Paris'])[1 + g % 10],16       (ARRAY['GE','GE','GE','DE','DE','DE','PL','PL','PL','FR'])[1 + g % 10],17       timestamp '2022-01-01' + g * interval '1 hour'18FROM generate_series(1, 20000) AS g;19CREATE INDEX customers_signed_up ON customers (signed_up);20ANALYZE customers;

1SELECT label, plan_of(q) AS plan2FROM (VALUES3  (1, 'cast the column',      $$SELECT id FROM customers WHERE signed_up::date = '2023-03-05'$$),4  (2, 'half-open range',      $$SELECT id FROM customers WHERE signed_up >= '2023-03-05' AND signed_up < '2023-03-06'$$),5  (3, 'function on column',   $$SELECT id FROM customers WHERE date_trunc('day', signed_up) = '2023-03-05'$$),6  (4, 'arithmetic on column', $$SELECT id FROM customers WHERE signed_up + interval '1 day' > '2024-04-01'$$),7  (5, 'arithmetic moved',     $$SELECT id FROM customers WHERE signed_up > timestamp '2024-04-01' - interval '1 day'$$)8) AS t(n, label, q)9ORDER BY n;

A tuning checklist

1. Run EXPLAIN (ANALYZE, BUFFERS) on the real query with real parameters.
2. Find the node where most time goes, and the lowest node where estimated and actual rows diverge.
3. Fix the cause: refresh or extend statistics, make the predicate sargable, add or reshape an index, or rewrite the query (for example NOT IN to NOT EXISTS).
4. Re-run and compare. A plain EXPLAIN shows guesses; tune from actuals.

Common mistakes

• Comparing actual rows with rows without noticing loops, or multiplying the estimate by loops: both numbers are already per loop.
• Treating cost units as milliseconds.
• WHERE created_at::date = ... on an indexed timestamp, which cannot use the index on the bare column, as the example shows.
• Running EXPLAIN ANALYZE DELETE ... on production "just to look".

What interviewers ask

• EXPLAIN versus EXPLAIN ANALYZE? Estimated plan versus executed plan with actual rows and timings; the latter runs the statement.
• A query is slow; how do you investigate? EXPLAIN (ANALYZE, BUFFERS), find estimate-versus-actual gaps and expensive nodes, check statistics, sargability and indexes.
• What does loops=1000 on an inner node mean? It ran once per outer row; multiply its per-loop rows and time by 1000 to see its real cost.