From SQL to plan: reading EXPLAIN

EXPLAIN shows how a database intends to run a query. The plan is a tree of operators: scans at the leaves read tables and indexes, and the nodes above them join, sort, group and filter the rows that come up from below, until the root hands the result to the client. PostgreSQL prints the tree one node per line, with the planner’s estimates for each node: a cost in its own units and the number of rows it expects. EXPLAIN ANALYZE also runs the query and prints, beside each estimate, the rows the node really produced and the pages it touched. SQLite’s EXPLAIN QUERY PLAN prints a shorter tree, one line per table, saying whether the table is read in full (SCAN) or through an index (SEARCH). In both engines the most useful habit is the same: compare what the planner expected with what happened, because a plan chosen from wrong numbers is usually a bad plan.

This lesson reads plans recorded on a throwaway PostgreSQL 18.6 server and plans from the SQLite that runs in your browser, all on one small shop database of customers and orders.

A plan is a tree of operators

Take one question: how many delivered orders came from each city? The diagram is the plan PostgreSQL chose for it. Rows flow upwards. The two scans at the bottom read the tables, the Hash node puts the customers in a hash table, the Hash Join looks up each order’s customer in that table, and the HashAggregate counts the joined rows per city and returns five rows.

EXPLAIN prints the same tree as text, root first. Each child sits on its own line below its parent, indented further and marked ->. To follow the work in the order it happens, read from the most indented lines upwards. The node names say which algorithm each step uses:

• Scans read tables. Seq Scan reads a whole table, Index Scan looks rows up through an index, Index Only Scan answers from the index alone, and Bitmap Heap Scan collects matching row locations from an index first and then visits the table in page order.
• Joins combine two inputs. Nested Loop does a lookup for every outer row, Hash Join goes through a hash table, and Merge Join merges two sorted streams.
• Shaping nodes change the stream. Sort orders the rows, HashAggregate groups them with a hash table, GroupAggregate groups sorted input, and Limit stops after a number of rows.

The indented lines without -> describe the node above them: Filter: is a condition checked on every row the node reads, Hash Cond: is the join condition and Group Key: lists the grouping columns.

Costs: what the planner expects

EXPLAIN on its own does not run the query. It prints the plan with the planner’s estimates on every node:

"""PostgreSQL 18.6: what EXPLAIN prints for a scan, a filter and a join, and the numbers its costs come from."""

from pg_session import psql

psql(r"""
SET max_parallel_workers_per_gather = 0;  -- one process: the same plan on every run

CREATE TABLE customers (id integer PRIMARY KEY, city text NOT NULL);
CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers,
  status text NOT NULL,
  total numeric(10, 2) NOT NULL
);
-- 6,000 customers in five cities, 30,000 orders: 99 in every 100 are delivered.
INSERT INTO customers
SELECT i, (ARRAY['Pune', 'Delhi', 'Chennai', 'Kolkata', 'Mumbai'])[1 + i % 5]
FROM generate_series(1, 6000) AS i;
INSERT INTO orders
SELECT i, 1 + (i * 7919) % 6000,
       CASE WHEN i % 100 = 0 THEN 'placed' ELSE 'delivered' END,
       100 + (i * 37) % 900
FROM generate_series(1, 30000) AS i;
ANALYZE;

\echo '-- The planner''s view of each table: pages on disk and rows'
SELECT relname, relpages, reltuples FROM pg_class
WHERE relname IN ('customers', 'orders') ORDER BY relname;

\echo '-- 1. Every row of a table'
EXPLAIN SELECT * FROM customers;

\echo '-- 2. The same scan with a filter'
EXPLAIN SELECT * FROM customers WHERE city = 'Pune';

\echo '-- 3. A join and a grouping'
EXPLAIN
SELECT c.city, count(*) AS orders
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.city;
""")

SET
CREATE TABLE
CREATE TABLE
INSERT 0 6000
INSERT 0 30000
ANALYZE
-- The planner's view of each table: pages on disk and rows
  relname  | relpages | reltuples 
-----------+----------+-----------
 customers |       33 |      6000
 orders    |      192 |     30000
(2 rows)

-- 1. Every row of a table
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on customers  (cost=0.00..93.00 rows=6000 width=10)
(1 row)

-- 2. The same scan with a filter
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on customers  (cost=0.00..108.00 rows=1200 width=10)
   Filter: (city = 'Pune'::text)
(2 rows)

-- 3. A join and a grouping
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 HashAggregate  (cost=961.52..961.57 rows=5 width=14)
   Group Key: c.city
   ->  Hash Join  (cost=168.00..813.02 rows=29700 width=6)
         Hash Cond: (o.customer_id = c.id)
         ->  Seq Scan on orders o  (cost=0.00..567.00 rows=29700 width=4)
               Filter: (status = 'delivered'::text)
         ->  Hash  (cost=93.00..93.00 rows=6000 width=10)
               ->  Seq Scan on customers c  (cost=0.00..93.00 rows=6000 width=10)
(8 rows)

"""Runs SQL on a private, throwaway PostgreSQL 18.6 server and prints what psql printed.

Each run creates a new database cluster in its own temporary folder (initdb), starts a server that listens only on a
Unix socket in that folder (no TCP port, nothing reachable from outside), sends each block of SQL to psql, then stops
the server and deletes the folder. Every run therefore starts from an empty database and prints the same text.

Autovacuum is switched off on this server, so statistics change only when an example runs ANALYZE itself: the plans
then depend on the example alone, never on when a background process happened to wake up.

PostgreSQL 18.6 is taken from the folder where mise installs it (mise install postgres@18.6). That folder is in the
home folder, which the isolated runs of learn:verify may not read, so the examples that use this file are recorded
with its network isolation off (LEARN_VERIFY_NETWORK=allow); nothing here opens a network connection.

The output is psql's own, with one change: psql starts every message with "psql:<stdin>:<line>:", which is left out.
The cluster uses the C locale, UTF-8 and the UTC time zone, so sorting and times do not depend on this computer.
"""

import atexit
import os
import pwd
import re
import shutil
import subprocess
import sys

REAL_HOME = pwd.getpwuid(os.getuid()).pw_dir
BIN = os.path.join(REAL_HOME, ".local", "share", "mise", "installs", "postgres", "18.6", "bin")
WHERE = re.compile(r"^psql:<stdin>:\d+: ", re.MULTILINE)

# Short names: a Unix socket path may not be longer than about 100 characters.
_base = os.path.join(os.environ.get("TMPDIR", os.getcwd()), "pg")
_data = os.path.join(_base, "d")
_sock = os.path.join(_base, "s")
_env = dict(os.environ, PGHOST=_sock, PGUSER="postgres", PGDATABASE="postgres", LC_ALL="C", LANG="C", TZ="UTC")
_started = False


def _tool(name: str) -> str:
    return os.path.join(BIN, name)


def _quiet(*argv: str) -> None:
    subprocess.run(argv, env=_env, check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)


def _start() -> None:
    global _started
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_sock)
    _quiet(_tool("initdb"), "-D", _data, "-U", "postgres", "-A", "trust", "-E", "UTF8", "--locale=C", "--no-sync", "--no-instructions")
    options = f"-k {_sock} -c listen_addresses='' -c fsync=off -c timezone=UTC -c autovacuum=off"
    _quiet(_tool("pg_ctl"), "start", "-w", "-s", "-D", _data, "-l", os.path.join(_base, "server.log"), "-o", options)
    _started = True
    atexit.register(_stop)


def _stop() -> None:
    global _started
    if _started:
        subprocess.run([_tool("pg_ctl"), "stop", "-s", "-m", "fast", "-D", _data], env=_env, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        _started = False
    shutil.rmtree(_base, ignore_errors=True)


def psql(sql: str, user: str = "postgres") -> None:
    """Sends a block of SQL to psql as `user` and prints what psql printed, errors included, in order."""
    if not _started:
        _start()
    run = subprocess.run(
        [_tool("psql"), "-X", "-U", user, "-d", "postgres", "-v", "ON_ERROR_STOP=0", "-P", "pager=off"],
        input=sql.strip() + "\n", env=_env, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )
    sys.stdout.write(WHERE.sub("", run.stdout))
    sys.stdout.flush()

Each node ends with four numbers in parentheses. cost=0.00..93.00 is a start-up cost and a total cost, rows=6000 is the number of rows the node is expected to return and width=10 is their average size in bytes. Costs are in the planner’s own units, by convention the cost of reading one page in sequence, so they are not milliseconds.

The first plan can be checked by hand. PostgreSQL’s manual prices a sequential scan as the pages read times seq_page_cost plus the rows read times cpu_tuple_cost, which are 1.0 and 0.01 by default. The catalog says that customers has 33 pages and 6,000 rows: 33 × 1.0 + 6,000 × 0.01 = 93.00. The filter of the second plan adds cpu_operator_cost, 0.0025 by default, for every row it checks: 93 + 6,000 × 0.0025 = 108.00. The scan still reads every row, so the cost goes up even though only rows=1200 are expected to come out, the customers of one city in five.

The third plan shows what the two costs mean. A node’s cost includes the costs of everything below it, and its start-up cost is what must be spent before its first row can come out. The Hash node’s start-up cost equals its total, 93.00, because it must read every customer before the join can probe the table. The HashAggregate cannot return a single city before it has counted every joined row, so its start-up cost, 961.52, is almost its whole cost. A Limit above a node with a low start-up cost can stop early; above a node like this one it cannot.

EXPLAIN ANALYZE: estimates against what happened

EXPLAIN ANALYZE runs the query and adds what really happened to every node. Since PostgreSQL 18 it reports buffers without being asked, and it prints actual row counts with two decimals, such as rows=1.00.

The next run builds the same tables but takes its statistics while every order is still placed, then marks 29,700 of the 30,000 orders delivered. The statistics are out of date when the query is planned. It plans the query twice, before and after a fresh ANALYZE:

"""PostgreSQL 18.6: one query planned from stale statistics, then from fresh ones, with EXPLAIN ANALYZE."""

from pg_session import psql

QUERY = """
SELECT c.city, count(*) AS orders
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.city;
"""

psql(rf"""
SET max_parallel_workers_per_gather = 0;  -- one process: the same plan on every run

CREATE TABLE customers (id integer PRIMARY KEY, city text NOT NULL);
CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers,
  status text NOT NULL,
  total numeric(10, 2) NOT NULL
);
INSERT INTO customers
SELECT i, (ARRAY['Pune', 'Delhi', 'Chennai', 'Kolkata', 'Mumbai'])[1 + i % 5]
FROM generate_series(1, 6000) AS i;
-- Every order starts as 'placed', and the statistics are taken now.
INSERT INTO orders
SELECT i, 1 + (i * 7919) % 6000, 'placed', 100 + (i * 37) % 900
FROM generate_series(1, 30000) AS i;
ANALYZE;

-- Then 29,700 of the 30,000 orders are delivered.
UPDATE orders SET status = 'delivered' WHERE id % 100 <> 0;

\echo '-- What the statistics still say about status'
SELECT most_common_vals, most_common_freqs FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

\echo '-- Planned from the stale statistics'
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) {QUERY}

ANALYZE orders;

\echo '-- After ANALYZE'
SELECT most_common_vals, most_common_freqs FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

\echo '-- Planned from fresh statistics'
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) {QUERY}
""")

SET
CREATE TABLE
CREATE TABLE
INSERT 0 6000
INSERT 0 30000
ANALYZE
UPDATE 29700
-- What the statistics still say about status
 most_common_vals | most_common_freqs 
------------------+-------------------
 {placed}         | {1}
(1 row)

-- Planned from the stale statistics
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1133.45..1133.47 rows=1 width=14) (actual rows=5.00 loops=1)
   Group Key: c.city
   Buffers: shared hit=89481
   ->  Sort  (cost=1133.45..1133.46 rows=1 width=6) (actual rows=29700.00 loops=1)
         Sort Key: c.city
         Sort Method: quicksort  Memory: 769kB
         Buffers: shared hit=89481
         ->  Nested Loop  (cost=0.28..1133.44 rows=1 width=6) (actual rows=29700.00 loops=1)
               Buffers: shared hit=89481
               ->  Seq Scan on orders o  (cost=0.00..1125.14 rows=1 width=4) (actual rows=29700.00 loops=1)
                     Filter: (status = 'delivered'::text)
                     Rows Removed by Filter: 300
                     Buffers: shared hit=381
               ->  Index Scan using customers_pkey on customers c  (cost=0.28..8.30 rows=1 width=10) (actual rows=1.00 loops=29700)
                     Index Cond: (id = o.customer_id)
                     Index Searches: 29700
                     Buffers: shared hit=89100
 Planning:
   Buffers: shared hit=58
(19 rows)

ANALYZE
-- After ANALYZE
  most_common_vals  | most_common_freqs 
--------------------+-------------------
 {delivered,placed} | {0.99,0.01}
(1 row)

-- Planned from fresh statistics
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1150.52..1150.57 rows=5 width=14) (actual rows=5.00 loops=1)
   Group Key: c.city
   Batches: 1  Memory Usage: 32kB
   Buffers: shared hit=414
   ->  Hash Join  (cost=168.00..1002.02 rows=29700 width=6) (actual rows=29700.00 loops=1)
         Hash Cond: (o.customer_id = c.id)
         Buffers: shared hit=414
         ->  Seq Scan on orders o  (cost=0.00..756.00 rows=29700 width=4) (actual rows=29700.00 loops=1)
               Filter: (status = 'delivered'::text)
               Rows Removed by Filter: 300
               Buffers: shared hit=381
         ->  Hash  (cost=93.00..93.00 rows=6000 width=10) (actual rows=6000.00 loops=1)
               Buckets: 8192  Batches: 1  Memory Usage: 322kB
               Buffers: shared hit=33
               ->  Seq Scan on customers c  (cost=0.00..93.00 rows=6000 width=10) (actual rows=6000.00 loops=1)
                     Buffers: shared hit=33
 Planning:
   Buffers: shared hit=28
(18 rows)

Each node now carries a second group of numbers, such as (actual rows=29700.00 loops=1). TIMING OFF leaves out the time spent in each node, which changes from run to run, so this output is the same every time; on your own server, keep timing on and read actual time= in milliseconds. Three details make the numbers readable:

• Rows are per execution. The inner Index Scan ran once for every outer row, loops=29700, and returned rows=1.00 each time. Multiply by loops for the total, 29,700 rows, which matches Index Searches: 29700.
• Buffers count pages. Buffers: shared hit=89481 means 89,481 page accesses found the page already in PostgreSQL’s shared buffers; read= would count pages that had to be read in. A node’s count includes its children’s.
• Filters report what they dropped. Rows Removed by Filter: 300 counts the placed orders that the scan read and threw away.

When the estimates are wrong, the plan is wrong

Read the first plan from the bottom. The statistics said that every order was placed, so the scan of orders expected rows=1 delivered order and found 29,700. For one row, a Nested Loop with a single index lookup is the cheapest join, so the planner chose it; for 29,700 rows it made 29,700 lookups and touched 89,481 buffers. After ANALYZE the estimate is exact, the planner builds a hash table of the customers once instead, and the query touches 414 buffers, 216 times fewer. Every node above a wrong estimate inherits the error, which is why the place to start is the lowest node whose estimated and actual rows part ways.

The next program puts each node’s estimate next to its actual rows and flags the nodes that are off by ten times or more:

"""Puts each node's row estimate next to what really happened, for the plan from stale statistics."""

import re

# The plan that stale_statistics.py printed from the stale statistics (PostgreSQL 18.6), node lines only.
PLAN = """
 GroupAggregate  (cost=1133.45..1133.47 rows=1 width=14) (actual rows=5.00 loops=1)
   ->  Sort  (cost=1133.45..1133.46 rows=1 width=6) (actual rows=29700.00 loops=1)
         ->  Nested Loop  (cost=0.28..1133.44 rows=1 width=6) (actual rows=29700.00 loops=1)
               ->  Seq Scan on orders o  (cost=0.00..1125.14 rows=1 width=4) (actual rows=29700.00 loops=1)
               ->  Index Scan using customers_pkey on customers c  (cost=0.28..8.30 rows=1 width=10) (actual rows=1.00 loops=29700)
"""

NODE = re.compile(
    r"^(?P<indent> *)(?:->  )?(?P<name>.+?)  \(cost=\S+ rows=(?P<est>\d+) width=\d+\)"
    r" \(actual rows=(?P<act>[\d.]+) loops=(?P<loops>\d+)\)$"
)


def factor(estimated: float, actual: float) -> float:
    """How many times the estimate is off, in either direction (each side counted as at least 1 row)."""
    e, a = max(estimated, 1.0), max(actual, 1.0)
    return max(e, a) / min(e, a)


print(f"{'node':<52}{'estimated':>10}{'actual':>8}{'loops':>7}{'total':>8}{'off by':>9}")
for line in PLAN.strip("\n").splitlines():
    m = NODE.match(line)
    if not m:
        continue
    # The root starts in column 1; each child is indented 6 more and starts with "->".
    depth = 0 if "->" not in line else (len(m["indent"]) - 3) // 6 + 1
    name = "  " * depth + m["name"]
    est, act, loops = int(m["est"]), float(m["act"]), int(m["loops"])
    off = factor(est, act)
    flag = "  <-" if off >= 10 else ""
    print(f"{name:<52}{est:>10}{act:>8.0f}{loops:>7}{act * loops:>8.0f}{off:>8.0f}x{flag}")

node                                                 estimated  actual  loops   total   off by
GroupAggregate                                               1       5      1       5       5x
  Sort                                                       1   29700      1   29700   29700x  <-
    Nested Loop                                              1   29700      1   29700   29700x  <-
      Seq Scan on orders o                                   1   29700      1   29700   29700x  <-
      Index Scan using customers_pkey on customers c         1       1  29700   29700       1x

The index scan is not flagged: it expected one row and found one, on each of its 29,700 loops. The planner’s mistake was not the lookup but the number of times it would be needed.

EXPLAIN ANALYZE really executes the statement. For an INSERT, UPDATE or DELETE, PostgreSQL’s manual suggests running it between BEGIN and ROLLBACK, so that the changes are undone.

Reading SQLite’s EXPLAIN QUERY PLAN

SQLite runs in your browser on this page, through sql.js. Its EXPLAIN QUERY PLAN prints a table with one row per step of the plan; press Run, change a query and run it again to see how the plan responds:

-- A small shop: 600 customers, 3,000 orders and an index on orders(customer_id).
CREATE TABLE customers (id INTEGER PRIMARY KEY, city TEXT NOT NULL);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id),
  status TEXT NOT NULL,
  total INTEGER NOT NULL
);
CREATE INDEX orders_customer ON orders (customer_id);

WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 600)
INSERT INTO customers
SELECT i, CASE i % 5 WHEN 0 THEN 'Pune' WHEN 1 THEN 'Delhi' WHEN 2 THEN 'Chennai' WHEN 3 THEN 'Kolkata' ELSE 'Mumbai' END
FROM n;
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 3000)
INSERT INTO orders
SELECT i, 1 + (i * 7919) % 600, CASE WHEN i % 100 = 0 THEN 'placed' ELSE 'delivered' END, 100 + (i * 37) % 900
FROM n;

-- 1. No index fits the condition: every row is read.
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE total > 900;

-- 2. A lookup by the rowid, the key of the table's own B-tree.
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE id = 42;

-- 3. A lookup through an index, then the table for the other columns.
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;

-- 4. The index holds every column the query needs.
EXPLAIN QUERY PLAN SELECT count(*) FROM orders WHERE customer_id = 42;

-- 5. A lookup, then a sort the index cannot provide.
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42 ORDER BY total;

-- 6. A join: one line per loop, the outer loop first.
EXPLAIN QUERY PLAN
SELECT c.city, count(*) AS orders
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.city;

┌────┬────────┬─────────┬─────────────┐
│ id │ parent │ notused │   detail    │
├────┼────────┼─────────┼─────────────┤
│ 2  │ 0      │ 216     │ SCAN orders │
└────┴────────┴─────────┴─────────────┘
┌────┬────────┬─────────┬───────────────────────────────────────────────────┐
│ id │ parent │ notused │                      detail                       │
├────┼────────┼─────────┼───────────────────────────────────────────────────┤
│ 2  │ 0      │ 33      │ SEARCH orders USING INTEGER PRIMARY KEY (rowid=?) │
└────┴────────┴─────────┴───────────────────────────────────────────────────┘
┌────┬────────┬─────────┬───────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                          detail                           │
├────┼────────┼─────────┼───────────────────────────────────────────────────────────┤
│ 3  │ 0      │ 61      │ SEARCH orders USING INDEX orders_customer (customer_id=?) │
└────┴────────┴─────────┴───────────────────────────────────────────────────────────┘
┌────┬────────┬─────────┬────────────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                               detail                               │
├────┼────────┼─────────┼────────────────────────────────────────────────────────────────────┤
│ 3  │ 0      │ 53      │ SEARCH orders USING COVERING INDEX orders_customer (customer_id=?) │
└────┴────────┴─────────┴────────────────────────────────────────────────────────────────────┘
┌────┬────────┬─────────┬───────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                          detail                           │
├────┼────────┼─────────┼───────────────────────────────────────────────────────────┤
│ 4  │ 0      │ 61      │ SEARCH orders USING INDEX orders_customer (customer_id=?) │
│ 16 │ 0      │ 0       │ USE TEMP B-TREE FOR ORDER BY                              │
└────┴────────┴─────────┴───────────────────────────────────────────────────────────┘
┌────┬────────┬─────────┬──────────────────────────────────────────────┐
│ id │ parent │ notused │                    detail                    │
├────┼────────┼─────────┼──────────────────────────────────────────────┤
│ 7  │ 0      │ 210     │ SCAN o USING COVERING INDEX orders_customer  │
│ 9  │ 0      │ 45      │ SEARCH c USING INTEGER PRIMARY KEY (rowid=?) │
│ 12 │ 0      │ 0       │ USE TEMP B-TREE FOR GROUP BY                 │
└────┴────────┴─────────┴──────────────────────────────────────────────┘

The detail column carries the plan, id and parent place each row in the tree, and SQLite’s documentation calls the third column unused. The six plans read like this:

1. SCAN orders: every row is read, because no index helps with total > 900.
2. SEARCH orders USING INTEGER PRIMARY KEY (rowid=?): one lookup in the table’s own B-tree by its rowid, which a column declared INTEGER PRIMARY KEY stands for.
3. SEARCH orders USING INDEX orders_customer (customer_id=?): the index finds the matching entries, then each row is fetched from the table for its other columns.
4. USING COVERING INDEX: count(*) needs nothing but the index, so the table is never read.
5. USE TEMP B-TREE FOR ORDER BY: the rows leave the index in customer_id order, so sorting them by total needs a temporary B-tree.
6. A join takes one line per loop, the outer loop first. The query names customers first, yet SQLite scans the index of orders in the outer loop and looks up each order’s customer by rowid in the inner one, then groups the rows by city in a temporary B-tree. Its documentation says that the plan shows how the query is evaluated, not how it was written.

SQLite runs every join as nested loops, which is why its plans have one line per table. Its documentation also warns that the output format may change between releases, so read it as information for people, not as data for a program.

Statistics change SQLite’s plans too

-- 20,000 orders: 19,800 delivered, 200 placed, 4,000 customers with 5 orders each.
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  total INTEGER NOT NULL
);
CREATE INDEX orders_status ON orders (status);
CREATE INDEX orders_customer ON orders (customer_id);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
INSERT INTO orders
SELECT i, 1 + (i * 7919) % 4000, CASE WHEN i % 100 = 0 THEN 'placed' ELSE 'delivered' END, 100 + (i * 37) % 900
FROM n;

-- Before ANALYZE: the planner has no statistics.
EXPLAIN QUERY PLAN
SELECT sum(total) FROM orders WHERE status = 'delivered' AND customer_id BETWEEN 1 AND 40;

-- How many rows each index would lead to.
SELECT (SELECT count(*) FROM orders WHERE status = 'delivered') AS via_status,
       (SELECT count(*) FROM orders WHERE customer_id BETWEEN 1 AND 40) AS via_customer;

ANALYZE;

-- What ANALYZE stored: the rows of the table, then the average rows per value of each index.
SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

-- The same query after ANALYZE.
EXPLAIN QUERY PLAN
SELECT sum(total) FROM orders WHERE status = 'delivered' AND customer_id BETWEEN 1 AND 40;

┌────┬────────┬─────────┬────────────────────────────────────────────────────┐
│ id │ parent │ notused │                       detail                       │
├────┼────────┼─────────┼────────────────────────────────────────────────────┤
│ 4  │ 0      │ 62      │ SEARCH orders USING INDEX orders_status (status=?) │
└────┴────────┴─────────┴────────────────────────────────────────────────────┘
┌────────────┬──────────────┐
│ via_status │ via_customer │
├────────────┼──────────────┤
│ 19800      │ 200          │
└────────────┴──────────────┘
┌─────────────────┬─────────────┐
│       idx       │    stat     │
├─────────────────┼─────────────┤
│ orders_customer │ 20000 5     │
│ orders_status   │ 20000 10000 │
└─────────────────┴─────────────┘
┌────┬────────┬─────────┬─────────────────────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                                   detail                                    │
├────┼────────┼─────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 4  │ 0      │ 105     │ SEARCH orders USING INDEX orders_customer (customer_id>? AND customer_id<?) │
└────┴────────┴─────────┴─────────────────────────────────────────────────────────────────────────────┘

Without statistics, SQLite assumes that each value in the first column of an index matches about ten rows. An equality on status therefore looked more selective than a range on customer_id, and the first plan searches the status index, which leads to 19,800 rows for a query that needs at most 200. ANALYZE stores what it measured in sqlite_stat1: the first number of each row is the number of rows in the index, the second the average number of rows that share one value of its first column. With 10,000 rows per status value against 5 per customer, the second plan uses the customer index. The plan writes the range BETWEEN 1 AND 40 as two bounds, customer_id>? and customer_id<?.

The stat1 row also shows the limit of an average. ‘placed’ matches 200 rows and ‘delivered’ 19,800, and both are summed up as 10,000. PostgreSQL keeps a list of the most common values with their frequencies, {delivered,placed} with {0.99,0.01} in the run above, so it can tell the two apart.

Where the estimates come from

Both engines plan from statistics that ANALYZE collected earlier; neither looks at the data while it plans. PostgreSQL’s ANALYZE reads a random sample of each table, 300 rows for each unit of the statistics target, so the default target of 100 samples 30,000 rows, the whole of a table as small as this one. For each column it keeps the fraction of NULLs, the number of distinct values, the most common values with their frequencies and a histogram of the rest, all visible in the pg_stats view. Because the sample is random, the manual notes that the statistics of a large table, and so its estimates, can change slightly after every ANALYZE even when the data did not.

On a normal PostgreSQL server the autovacuum daemon runs ANALYZE on its own once enough rows have changed: 50 rows plus 10 % of the table by default, 3,050 rows for these 30,000 orders. The server of these examples runs with autovacuum switched off, so that the statistics stayed stale on purpose. SQLite runs nothing on its own: its documentation recommends PRAGMA optimize, which runs ANALYZE where it is needed, before an application closes a connection and after every schema change.

A routine for reading any plan

1. Get the real plan: EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL, on realistic data, between BEGIN and ROLLBACK for statements that change data.
2. Find the lowest node whose estimated and actual rows differ by ten times or more, remembering that both are per loop.
3. Refresh the statistics with ANALYZE and look at the plan again. If the estimate is still wrong, the cause is the shape of the data or of the query, which the later lessons of this module take on.
4. Follow the buffers to the nodes that touch the most pages: that is where the time goes.

Exercise

"""Finds the plan nodes whose row estimates are far from what really happened."""


def misestimated(nodes, threshold=10):
    """Names of the nodes whose estimate is off by `threshold` times or more, in either direction, in plan order.

    Each node is (name, estimated_rows, actual_rows, loops), as EXPLAIN ANALYZE prints them.
    """
    return []

from nodes import misestimated

STALE_PLAN = [
    ("GroupAggregate", 1, 5.0, 1),
    ("Sort", 1, 29700.0, 1),
    ("Nested Loop", 1, 29700.0, 1),
    ("Seq Scan on orders o", 1, 29700.0, 1),
    ("Index Scan using customers_pkey on customers c", 1, 1.0, 29700),
]


def test_stale_plan():
    """The lesson's stale plan: three nodes are off 29,700 times, the inner index scan is right on every loop"""
    assert misestimated(STALE_PLAN) == ["Sort", "Nested Loop", "Seq Scan on orders o"]


def test_overestimates_and_empty_nodes():
    """Expecting too many rows counts too, and a node that returned nothing counts as 1 row"""
    nodes = [("Hash Join", 50000, 400.0, 1), ("Seq Scan on refunds", 1200, 0.0, 1), ("Seq Scan on users", 120, 100.0, 1)]
    assert misestimated(nodes) == ["Hash Join", "Seq Scan on refunds"]


def test_threshold():
    """The threshold is a parameter, and a node off by exactly the threshold counts"""
    nodes = [("Index Scan on a", 10, 100.0, 4), ("Index Scan on b", 10, 99.0, 1)]
    assert misestimated(nodes, threshold=10) == ["Index Scan on a"]
    assert misestimated(nodes, threshold=5) == ["Index Scan on a", "Index Scan on b"]

Check yourself

Interview questions

Fresher · What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the plan the planner chose without running the query: the tree of nodes, each with its estimated cost, rows and row width. EXPLAIN ANALYZE runs the query and adds what actually happened to every node: the rows it returned, how many times it ran (loops), the time it took and, in PostgreSQL 18, the buffers it touched. Two consequences follow. EXPLAIN ANALYZE really executes the statement, so an INSERT, UPDATE or DELETE belongs between BEGIN and ROLLBACK. And only EXPLAIN ANALYZE can test the estimates: a node whose estimated rows are far from its actual rows was planned from wrong numbers, and fresh statistics are the first fix to try. EXPLAIN alone is safe and instant, which makes it the quick check of whether an index would be used at all.

Fresher · In SQLite’s EXPLAIN QUERY PLAN, what is the difference between SCAN and SEARCH?

Both say how SQLite reads one table. SCAN visits every row: the whole table, or a whole index when SQLite reads the table in index order or a covering index answers the query. SEARCH visits only some of the rows, by looking up a condition in an index or by rowid, and the line names the index and the columns it uses, such as (customer_id=?). SQLite runs joins as nested loops and prints one line per loop, outer first, so a SCAN in an inner loop is a full pass over that table for every outer row, usually the line to fix with an index. COVERING INDEX on either kind of line means that the table itself is never read.

Key takeaways

• A plan is a tree: scans at the leaves, joins, sorts and aggregates above them, the result at the root. Read PostgreSQL’s text from the most indented lines upwards.
• cost=start-up..total is in planner units, not milliseconds, and each node includes its children. A sequential scan costs its pages times 1.0 plus its rows times 0.01 by default.
• EXPLAIN ANALYZE executes the query and shows actual rows per loop, loops and buffers; multiply by loops for totals.
• The first symptom of a bad plan is the lowest node whose estimated and actual rows differ by ten times or more; every node above it inherits the error.
• Fresh statistics fix most such errors: ANALYZE, autovacuum in PostgreSQL, PRAGMA optimize in SQLite.
• SQLite’s SCAN reads a whole table or index, SEARCH only part of it, and a join prints one line per nested loop.