PostgreSQL in Depth Module 3 – Query plans and indexes
Reading EXPLAIN ANALYZE in PostgreSQL 18
Read PostgreSQL 18 query plans from the deepest node up: costs, estimated and actual rows, loops, buffers, spills to disk and the options worth knowing.
What you will learn
- Read a plan tree from the deepest node up using costs, estimated and actual rows, loops and buffers
- Detect the warning signs in a plan, from estimates far from the actual rows to sorts and hashes that spill to disk
- Pick the EXPLAIN option that answers a question, such as WAL, SETTINGS, SERIALIZE, GENERIC_PLAN or FORMAT JSON
- Explain a data-changing statement safely inside a transaction that is rolled back
Before you start
What you’ll learn · Before you start
What you will learn
- Read a plan tree from the deepest node up using costs, estimated and actual rows, loops and buffers
- Detect the warning signs in a plan, from estimates far from the actual rows to sorts and hashes that spill to disk
- Pick the EXPLAIN option that answers a question, such as WAL, SETTINGS, SERIALIZE, GENERIC_PLAN or FORMAT JSON
- Explain a data-changing statement safely inside a transaction that is rolled back
Before you start
On this page
A report of pending orders reads 200,000 rows to keep 2,000, and its plan says so in one line: Rows Removed by Filter: 198000. EXPLAIN prints the plan PostgreSQL’s planner chose, with what it expects each step to cost and
return; EXPLAIN ANALYZE runs the statement and adds what each step really returned, from which pages and with how
much memory. Most slow queries are explained by the gap between those two sets of numbers.
You will read a plan tree from the deepest node up, multiply by loops, tell pages found in memory from pages read,
recognise three warning signs, and choose the form of EXPLAIN that answers your question, including for a DELETE
you do not want to keep.
EXPLAIN predicts, EXPLAIN ANALYZE measures
A query plan: The tree of steps PostgreSQL's planner chooses to run one statement: scans at the leaves, then joins, aggregates and sorts above them. EXPLAIN prints it, with estimated costs and rows for every step. is a tree of plan node: One step of a query plan, such as a Seq Scan, an Index Scan, a Hash Join or a Sort. It receives rows from the nodes below it and passes its own rows to the node above.: scans at the leaves,
then joins, aggregates and sorts above them, each passing its rows to the node above. The run builds the shop of this
lesson, 2,000 customers and 200,000 orders, and asks for pending orders per city twice: once with EXPLAIN, once with
EXPLAIN ANALYZE:
plan_session.py
"""One query planned with EXPLAIN, then run and measured with EXPLAIN ANALYZE, on PostgreSQL 18.6."""
from pg_session import Server
# The same plan on every run: autovacuum off, no parallel workers or JIT, and statistics from every row.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
"default_statistics_target": 1000, "timezone": "'UTC'"}
with Server(settings=SETTINGS) as pg:
pg.up()
pg.psql("setup.sql", flags=("-q",))
pg.psql("plan.sql")Output
$ psql -X -q -f setup.sql
$ psql -X -a -f plan.sql
-- Pending orders per city: first the plan the planner predicts, then the same plan run and measured.
-- TIMING OFF leaves out the clock readings of each node, which differ on every run; SUMMARY OFF the totals.
EXPLAIN
SELECT c.city, count(*) AS orders, sum(o.total) AS value
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending'
GROUP BY c.city ORDER BY c.city;
QUERY PLAN
----------------------------------------------------------------------------------------
Sort (cost=4050.38..4050.39 rows=5 width=46)
Sort Key: c.city
-> HashAggregate (cost=4050.26..4050.32 rows=5 width=46)
Group Key: c.city
-> Hash Join (cost=59.00..4035.26 rows=2000 width=13)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.00..3971.00 rows=2000 width=11)
Filter: (status = 'pending'::text)
-> Hash (cost=34.00..34.00 rows=2000 width=10)
-> Seq Scan on customers c (cost=0.00..34.00 rows=2000 width=10)
(10 rows)
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT c.city, count(*) AS orders, sum(o.total) AS value
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending'
GROUP BY c.city ORDER BY c.city;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Sort (cost=4050.38..4050.39 rows=5 width=46) (actual rows=5.00 loops=1)
Sort Key: c.city
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1488
-> HashAggregate (cost=4050.26..4050.32 rows=5 width=46) (actual rows=5.00 loops=1)
Group Key: c.city
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=1485
-> Hash Join (cost=59.00..4035.26 rows=2000 width=13) (actual rows=2000.00 loops=1)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=1485
-> Seq Scan on orders o (cost=0.00..3971.00 rows=2000 width=11) (actual rows=2000.00 loops=1)
Filter: (status = 'pending'::text)
Rows Removed by Filter: 198000
Buffers: shared hit=1471
-> Hash (cost=34.00..34.00 rows=2000 width=10) (actual rows=2000.00 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 102kB
Buffers: shared hit=14
-> Seq Scan on customers c (cost=0.00..34.00 rows=2000 width=10) (actual rows=2000.00 loops=1)
Buffers: shared hit=14
Planning:
Buffers: shared hit=12
(22 rows)
SELECT c.city, count(*) AS orders, sum(o.total) AS value
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending'
GROUP BY c.city ORDER BY c.city;
city | orders | value
---------+--------+-----------
Chennai | 400 | 169600.00
Kochi | 400 | 219600.00
Lucknow | 400 | 239600.00
Mumbai | 400 | 279600.00
Pune | 400 | 119800.00
(5 rows)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 plan_session.py
plan.sql
-- Pending orders per city: first the plan the planner predicts, then the same plan run and measured.
-- TIMING OFF leaves out the clock readings of each node, which differ on every run; SUMMARY OFF the totals.
EXPLAIN
SELECT c.city, count(*) AS orders, sum(o.total) AS value
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending'
GROUP BY c.city ORDER BY c.city;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT c.city, count(*) AS orders, sum(o.total) AS value
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending'
GROUP BY c.city ORDER BY c.city;
SELECT c.city, count(*) AS orders, sum(o.total) AS value
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending'
GROUP BY c.city ORDER BY c.city;setup.sql
-- The shop of this lesson: 2,000 customers, 400 in each of five cities, and 200,000 orders over 270 days.
-- The 2,000 newest orders, one per customer, are pending and unpaid; every other order is shipped and paid,
-- a dependency between two columns that the planner does not know about.
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, city text NOT NULL);
INSERT INTO customers
SELECT n, 'customer ' || n, (ARRAY['Pune', 'Mumbai', 'Chennai', 'Lucknow', 'Kochi'])[1 + (n - 1) / 400]
FROM generate_series(1, 2000) AS n;
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id int NOT NULL REFERENCES customers,
status text NOT NULL,
paid boolean NOT NULL,
total numeric(10,2) NOT NULL,
placed date NOT NULL
);
INSERT INTO orders (customer_id, status, paid, total, placed)
SELECT 1 + n % 2000, CASE WHEN n > 198000 THEN 'pending' ELSE 'shipped' END, n <= 198000,
(n % 900) + 99.5, date '2026-01-01' + (n - 1) / 741
FROM generate_series(1, 200000) AS n;
CREATE INDEX orders_customer_idx ON orders (customer_id);
VACUUM ANALYZE customers, orders;pg_session.py
"""Throwaway PostgreSQL 18.6 servers for the examples of this lesson.
Every session creates a cluster of its own with initdb in a new folder inside the examples folder, starts it, runs
psql scripts and server programs on it, prints what they printed, and stops and deletes it again, also when something
fails. The server is the pinned PostgreSQL 18.6 from the mise installations next to the Python that runs this file.
It listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system reports as
unused, with no Unix socket, and it asks for a password (scram-sha-256) made up for the run and never printed.
learn:verify's run sandbox cannot read the home folder, where mise keeps PostgreSQL, so these sessions are recorded
with LEARN_VERIFY_NETWORK=allow; they read only this folder and the toolchain and connect to nothing but 127.0.0.1.
The output is exactly what the programs printed, with four substitutions so that a record never shows the computer
it ran on and is the same on every run: the run's folder is shown as ".", the folder of PostgreSQL's programs is left
out of their paths, the operating-system user's name is shown as "learner", and the dots pg_ctl prints while it waits
(one about every tenth of a second) as three. The sessions never print the port. A session stops with status 1 when
a command fails that should have worked, or reports a different number of errors than the session expects, so a run
that went wrong is never recorded as if it had worked.
"""
import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path
PG_VERSION = "18.6"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
BIN = INSTALLS / "postgres" / PG_VERSION / "bin"
OS_USER = pwd.getpwuid(os.geteuid()).pw_name
def free_port():
"""A TCP port on 127.0.0.1 that nothing listens on now."""
with socket.socket() as probe:
probe.bind(("127.0.0.1", 0))
return probe.getsockname()[1]
class Server:
"""One throwaway cluster: initdb, settings, start; stopped and deleted when the with block ends."""
def __init__(self, settings=None, initdb=(), name="data"):
self.settings = dict(settings or {})
self.initdb_args = list(initdb)
self.name = name
self.running = []
def __enter__(self):
if not (BIN / "postgres").exists():
sys.exit(f"PostgreSQL {PG_VERSION} was not found next to this Python")
self.base = Path(tempfile.mkdtemp(prefix="pg", dir=".")).resolve()
self.port = free_port()
self.password = secrets.token_urlsafe(24)
(self.base / "pwfile").write_text(self.password + "\n")
self.env = {**os.environ, "PGHOST": "127.0.0.1", "PGPORT": str(self.port), "PGUSER": "postgres",
"PGPASSWORD": self.password, "PGDATABASE": "postgres", "PGCONNECT_TIMEOUT": "10"}
for key in ("PGSERVICE", "PGSERVICEFILE", "PGOPTIONS", "PSQLRC"):
self.env.pop(key, None)
for script in Path(".").glob("*.sql"):
shutil.copy(script, self.base / script.name)
for data in Path(".").glob("*.csv"):
shutil.copy(data, self.base / data.name)
return self
def __exit__(self, *exc):
for folder, _port in reversed(self.running):
self._call(["pg_ctl", "-D", folder, "-m", "fast", "-w", "-t", "60", "stop"])
self.running = []
shutil.rmtree(self.base, ignore_errors=True)
# ---- the cluster ---------------------------------------------------------------------------------------------
def initdb(self, show=False):
args = ["initdb", "-D", self.name, "-U", "postgres", "--auth=scram-sha-256", "--pwfile=pwfile",
*self.initdb_args]
if not show:
args.append("--no-instructions")
self._expect(args, show=show)
with open(self.base / self.name / "postgresql.conf", "a") as conf:
conf.write("\n# Added by the session: 127.0.0.1 only, no Unix socket.\n")
conf.write(f"listen_addresses = '127.0.0.1'\nport = {self.port}\nunix_socket_directories = ''\n")
for key, value in self.settings.items():
conf.write(f"{key} = {value}\n")
def start(self, show=False, folder=None, port=None):
folder = folder or self.name
self._expect(["pg_ctl", "-D", folder, "-l", f"{folder}.log", "-w", "-t", "60", "start"], show=show)
self.running.append((folder, port or self.port))
def up(self):
"""initdb and start, quietly."""
self.initdb()
self.start()
return self
def standby(self, folder="standby"):
"""A streaming standby of this server, made with pg_basebackup -R, started on a port of its own."""
self._expect(["pg_basebackup", "-D", folder, "-R", "-X", "stream", "-c", "fast"])
port = free_port()
with open(self.base / folder / "postgresql.conf", "a") as conf:
conf.write(f"port = {port}\n")
self.start(folder=folder, port=port)
for _ in range(300):
if self.query("SELECT count(*) FROM pg_stat_replication WHERE state = 'streaming'") == "1":
return port
time.sleep(0.1)
sys.exit("the standby did not start streaming")
# ---- running things ------------------------------------------------------------------------------------------
def _call(self, args, env=None):
args = [str(a) for a in args]
done = subprocess.run([str(BIN / args[0]), *args[1:]], stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
text=True, cwd=self.base, env=env or self.env)
return done.returncode, self.clean(done.stdout)
def _expect(self, args, show=False, status=0):
code, out = self._call(args)
if show:
_show(args, out)
if code != status:
if not show:
print(out)
sys.exit(f"{args[0]} ended with status {code}, not {status}")
return out
def clean(self, text):
"""The run's folder as ".", the user as learner, pg_ctl's waiting dots as three (see the docstring)."""
text = text.replace(str(BIN) + "/", "").replace(str(self.base) + "/", "").replace(str(self.base), ".")
text = re.sub(r"(waiting for server to (?:start|shut down))\.+", r"\1...", text)
return text.replace(f'"{OS_USER}"', '"learner"')
def wait_for(self, sql, value):
"""Waits (unprinted) until a query returns the value, for example after a reload."""
for _ in range(300):
if self.query(sql) == value:
return
time.sleep(0.1)
sys.exit(f"{sql} never returned {value}")
def show_file(self, path):
"""Prints a text file of the cluster, as `cat` would."""
print(f"$ cat {path}")
print((self.base / path).read_text().rstrip("\n"))
print()
def query(self, sql, port=None):
"""One value from one query, unprinted (the session uses it to wait or to decide)."""
env = {**self.env, "PGPORT": str(port or self.port)}
code, out = self._call(["psql", "-X", "-At", "-c", sql], env=env)
if code != 0:
sys.exit(f"the query failed: {out}")
return out.strip()
def silent(self, sql):
"""Runs a statement without printing it (used only to give a role the run's password)."""
self.query(sql)
def psql(self, script, errors=0, status=None, user=None, db=None, port=None, flags=("-a",), note=None):
"""Runs a psql script with -X (no ~/.psqlrc) and the given flags, and prints the command and its output.
`errors` is how many ERROR lines the script must print; `status` the exit status it must end with."""
env = {**self.env, "PGPORT": str(port or self.port)}
if user:
env["PGUSER"] = user
if db:
env["PGDATABASE"] = db
args = ["psql", "-X", *flags, "-f", script]
code, out = self._call(args, env=env)
shown = ["psql", "-X", *flags, "-f", script]
if user:
shown[1:1] = ["-U", user]
if db:
shown[1:1] = ["-d", db]
if note:
print(f"-- {note}")
_show(shown, out)
found = sum(1 for line in out.splitlines() if "ERROR:" in line or ": error:" in line)
expected_status = 0 if status is None else status
if found != errors or code != expected_status:
sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")
def sessions(self, count):
"""Opens `count` idle psql connections (each one a client backend) until the with block ends."""
return _Idle(self, count)
def shell(self, *args, status=0):
"""Runs one of PostgreSQL's programs and prints the command and its output."""
return self._expect(list(args), show=True, status=status)
def listing(self, folder, prefix=""):
"""Prints the names in a folder of the cluster (sorted, folders with a slash), as `ls -p` would list them."""
path = self.base / folder
names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
print(f"-- in {folder}" + (f", the names that start with {prefix}" if prefix else "") + ":")
print(textwrap.fill(" ".join(names), width=78, initial_indent=" ", subsequent_indent=" ",
break_on_hyphens=False))
print()
return names
def _arg(a):
"""A command-line argument as a reader would type it: double quotes around text with single quotes in it."""
a = str(a)
if re.fullmatch(r"[\w@%+=:,./-]+", a):
return a
if not re.search(r"[\"$`\\!]", a):
return f'"{a}"'
return shlex.quote(a)
def _show(args, out):
print("$ " + " ".join(_arg(a) for a in args))
if out.strip():
print(out.rstrip("\n"))
print()
class _Idle:
"""Idle psql connections, closed again when the with block ends."""
def __init__(self, server, count):
self.server, self.count, self.procs = server, count, []
def __enter__(self):
before = int(self.server.query("SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"))
for _ in range(self.count):
self.procs.append(subprocess.Popen([str(BIN / "psql"), "-X", "-q"], stdin=subprocess.PIPE,
stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL,
env=self.server.env, cwd=self.server.base))
sql = "SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"
for _ in range(300):
# the query's own connection is one more client backend
if int(self.server.query(sql)) == before + self.count:
return self
time.sleep(0.1)
sys.exit("the idle connections did not open")
def __exit__(self, *exc):
for p in self.procs:
p.stdin.close()
p.wait(timeout=30)Each node of the first plan shows cost=startup..total, the estimated rows it returns and their average width in
bytes. Costs are planner units, not milliseconds. The manual’s formula for a sequential scan gives the scan of orders
exactly: 1,471 pages at seq_page_cost 1.0, plus 200,000 rows at cpu_tuple_cost 0.01, plus the filter on each row
at cpu_operator_cost 0.0025, is 1,471 + 2,000 + 500 = 3,971. A node’s total includes everything below it, which is
why the Sort at the top costs 4,050.39.
The second plan adds, on every node, actual rows and loops. This lesson runs it with TIMING OFF, which leaves out
the clock readings that would differ on every run, and SUMMARY OFF, which leaves out the total times. Read it from the
deepest node up, following the rows:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- The top node asks first. Execution starts at the root. Sort cannot sort anything until rows arrive, so it asks HashAggregate, which asks Hash Join, and so on down the tree. Rows then travel upwards, one node to the next.
- The hash is built first. A hash join reads its inner side before anything else. Seq Scan on customers reads 14 pages, and Hash stores all 2,000 customers by id in 102kB of memory, one batch.
- Orders stream past a filter. Seq Scan on orders reads all 1,471 pages and tests status = 'pending' on each of the 200,000 rows. It removes 198,000 and passes 2,000 up, exactly the planner's estimate.
- Each order finds its customer. For every pending order, Hash Join looks up customer_id in the hash and passes the joined row up. 2,000 rows come out, one per order.
- Five groups. HashAggregate keeps one running count and sum per city in a hash table of 32kB, then passes up its 5 groups. The estimate was 5 here too.
- The sort comes last. Sort orders the 5 rows by city with a quicksort in 25kB. Every node's estimated rows equalled its actual rows, so this plan was chosen on good information.
Every estimate here was right: 2,000 pending orders expected and found, 5 cities expected and found. That is the plan of a query the planner understood. The rest of this lesson is about the lines that show when it did not.
Comparing costs with times
A total cost of 4,050 says nothing about seconds. Costs only compare plans for the same query on the same server; to
know how long a node took, run EXPLAIN ANALYZE with timing on and read its actual time, in milliseconds.
Checkpoint
Check: Estimates and measurements
Actual rows are per loop, so multiply by loops
Some nodes run more than once. In a nested loop, the inner side runs once for every row of the outer side. The run asks for the orders of five customers:
loops_session.py
"""A nested loop: the inner side runs once per outer row, and EXPLAIN ANALYZE counts rows per loop."""
from pg_session import Server
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
"default_statistics_target": 1000, "timezone": "'UTC'"}
with Server(settings=SETTINGS) as pg:
pg.up()
pg.psql("setup.sql", flags=("-q",))
pg.psql("loops.sql")Output
$ psql -X -q -f setup.sql
$ psql -X -a -f loops.sql
-- Five customers and their orders: the inner side of a nested loop runs once for every outer row.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT c.name, o.id, o.total
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id
WHERE c.id <= 5;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=5.35..1453.05 rows=500 width=28) (actual rows=500.00 loops=1)
Buffers: shared hit=513
-> Index Scan using customers_pkey on customers c (cost=0.28..8.37 rows=5 width=17) (actual rows=5.00 loops=1)
Index Cond: (id <= 5)
Index Searches: 1
Buffers: shared hit=3
-> Bitmap Heap Scan on orders o (cost=5.07..287.94 rows=100 width=19) (actual rows=100.00 loops=5)
Recheck Cond: (c.id = customer_id)
Heap Blocks: exact=500
Buffers: shared hit=510
-> Bitmap Index Scan on orders_customer_idx (cost=0.00..5.04 rows=100 width=0) (actual rows=100.00 loops=5)
Index Cond: (customer_id = c.id)
Index Searches: 5
Buffers: shared hit=10
Planning:
Buffers: shared hit=254 read=6
(16 rows)
-- The same five customers, counted: 100 orders each.
SELECT count(*) AS orders FROM orders WHERE customer_id <= 5;
orders
--------
500
(1 row)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 loops_session.py
loops.sql
-- Five customers and their orders: the inner side of a nested loop runs once for every outer row.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT c.name, o.id, o.total
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id
WHERE c.id <= 5;
-- The same five customers, counted: 100 orders each.
SELECT count(*) AS orders FROM orders WHERE customer_id <= 5;The index scan on customers_pkey ran once and returned 5 customers. For each of them, the bitmap scan of orders ran
again: actual rows=100.00 loops=5. The manual’s rule is that rows and times are averages per execution, so the node
returned 100 × 5 = 500 rows in total, which the count confirms. The estimate is per execution too: rows=100 on the
inner side, rows=500 at the Nested Loop above it.
PostgreSQL 18 prints those averages with two decimals. Before version 18 an inner side that found a row on one loop in
three printed rows=0, which hid the work; now it prints rows=0.33. The same release added Index Searches to index
scans: 5 here, one descent of orders_customer_idx for each customer.
Key idea
Actual rows, like estimated rows, are per loop: a node’s real output is rows times loops, and an innocent-looking inner node with a large loops count is where a nested loop spends its time.
Checkpoint
Check: Rows per loop
Buffers show where every page came from
PostgreSQL 18 adds a Buffers line to EXPLAIN ANALYZE without being asked; earlier versions needed the BUFFERS
option. It counts 8 kB pages: shared hit for pages found in the shared buffers: PostgreSQL's cache of table and index pages in shared memory, used by every process of the server. Its size, shared_buffers (128MB by default), changes only with a restart. More in “PostgreSQL architecture: processes, memory, files”,
shared read for pages the server had to ask the operating system for, dirtied and written for pages it changed or
wrote out, and temp for the temporary files of sorts and hashes. The run restarts the server, which empties the
shared buffers, and scans orders twice:
buffers_session.py
"""Buffers in EXPLAIN ANALYZE: the same scan right after a restart, with empty shared buffers, and then again."""
from pg_session import Server
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
"default_statistics_target": 1000, "timezone": "'UTC'"}
with Server(settings=SETTINGS) as pg:
pg.up()
pg.psql("setup.sql", flags=("-q",))
# A restart empties the shared buffers; the operating system may still cache the files.
pg.shell("pg_ctl", "-D", "data", "-l", "data.log", "restart")
pg.psql("buffers.sql", note="the first scan after the restart")
pg.psql("buffers.sql", note="the same scan again")Output
$ psql -X -q -f setup.sql
$ pg_ctl -D data -l data.log restart
waiting for server to shut down... done
server stopped
waiting for server to start... done
server started
-- the first scan after the restart
$ psql -X -a -f buffers.sql
-- One full scan of orders. PostgreSQL 18 adds the Buffers lines to EXPLAIN ANALYZE without being asked.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM orders WHERE paid;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=3966.00..3966.01 rows=1 width=8) (actual rows=1.00 loops=1)
Buffers: shared read=1471
-> Seq Scan on orders (cost=0.00..3471.00 rows=198000 width=0) (actual rows=198000.00 loops=1)
Filter: paid
Rows Removed by Filter: 2000
Buffers: shared read=1471
Planning:
Buffers: shared hit=55 read=18 dirtied=1
(8 rows)
-- the same scan again
$ psql -X -a -f buffers.sql
-- One full scan of orders. PostgreSQL 18 adds the Buffers lines to EXPLAIN ANALYZE without being asked.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM orders WHERE paid;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=3966.00..3966.01 rows=1 width=8) (actual rows=1.00 loops=1)
Buffers: shared hit=1471
-> Seq Scan on orders (cost=0.00..3471.00 rows=198000 width=0) (actual rows=198000.00 loops=1)
Filter: paid
Rows Removed by Filter: 2000
Buffers: shared hit=1471
Planning:
Buffers: shared hit=78
(8 rows)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 buffers_session.py
buffers.sql
-- One full scan of orders. PostgreSQL 18 adds the Buffers lines to EXPLAIN ANALYZE without being asked.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM orders WHERE paid;The first scan read all 1,471 pages: shared read=1471. The second found every one in memory: shared hit=1471.
Same plan, same rows, very different work. The Planning line counts the catalog pages the planner itself looked at:
55 hits and 18 reads after the restart, 78 hits the second time.
read does not mean disk
shared read counts pages PostgreSQL did not have, not pages that came from the disk: the operating system’s own
cache may have served them. A query that is fast in testing and slow in production often differs only in this line.
When you compare two plans, compare their buffers as well as their rows: the nested loop of the next section makes 7,471 buffer accesses to return 2,000 rows, five times the 1,485 of the hash join above, whatever the clock says on a warm cache.
Checkpoint
Check: Buffer counts
Three warning signs: a wrong estimate, discarded rows, a spill
The run shows the three lines worth looking for first, on the same data:
warnings_session.py
"""Three warning signs in EXPLAIN ANALYZE: an estimate far from the actual rows, a sort and a hash that spill."""
from pg_session import Server
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
"default_statistics_target": 1000, "timezone": "'UTC'"}
with Server(settings=SETTINGS) as pg:
pg.up()
pg.psql("setup.sql", flags=("-q",))
pg.psql("warnings.sql")Output
$ psql -X -q -f setup.sql
$ psql -X -a -f warnings.sql
-- 1. An estimate 100 times too low: the planner multiplies the two conditions as if they were independent.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT c.city, o.id, o.total
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending' AND NOT o.paid;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.28..4056.90 rows=20 width=21) (actual rows=2000.00 loops=1)
Buffers: shared hit=7471
-> Seq Scan on orders o (cost=0.00..3971.00 rows=20 width=19) (actual rows=2000.00 loops=1)
Filter: ((NOT paid) AND (status = 'pending'::text))
Rows Removed by Filter: 198000
Buffers: shared hit=1471
-> Index Scan using customers_pkey on customers c (cost=0.28..4.29 rows=1 width=10) (actual rows=1.00 loops=2000)
Index Cond: (id = o.customer_id)
Index Searches: 2000
Buffers: shared hit=6000
Planning:
Buffers: shared hit=271 read=7
(12 rows)
-- 2. A sort that does not fit in work_mem writes to disk; with enough memory it does not.
SET work_mem = '256kB';
SET
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT * FROM orders ORDER BY total;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Sort (cost=35444.64..35944.64 rows=200000 width=32) (actual rows=200000.00 loops=1)
Sort Key: total
Sort Method: external merge Disk: 8256kB
Buffers: shared hit=1474, temp read=3090 written=3165
-> Seq Scan on orders (cost=0.00..3471.00 rows=200000 width=32) (actual rows=200000.00 loops=1)
Buffers: shared hit=1471
Planning:
Buffers: shared hit=15
(8 rows)
SET work_mem = '64MB';
SET
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT * FROM orders ORDER BY total;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Sort (cost=21080.64..21580.64 rows=200000 width=32) (actual rows=200000.00 loops=1)
Sort Key: total
Sort Method: quicksort Memory: 15520kB
Buffers: shared hit=1471
-> Seq Scan on orders (cost=0.00..3471.00 rows=200000 width=32) (actual rows=200000.00 loops=1)
Buffers: shared hit=1471
(6 rows)
-- 3. A hash aggregate that does not fit splits its groups into batches on disk.
SET work_mem = '256kB';
SET
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT customer_id, placed, count(*) FROM orders GROUP BY customer_id, placed;
QUERY PLAN
----------------------------------------------------------------------------------------------------
HashAggregate (cost=15221.00..16983.50 rows=20000 width=16) (actual rows=200000.00 loops=1)
Group Key: customer_id, placed
Planned Partitions: 4 Batches: 85 Memory Usage: 601kB Disk Usage: 7272kB
Buffers: shared hit=1471, temp read=1592 written=2326
-> Seq Scan on orders (cost=0.00..3471.00 rows=200000 width=8) (actual rows=200000.00 loops=1)
Buffers: shared hit=1471
Planning:
Buffers: shared hit=15 read=1
(8 rows)
RESET work_mem;
RESET
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 warnings_session.py
warnings.sql
-- 1. An estimate 100 times too low: the planner multiplies the two conditions as if they were independent.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT c.city, o.id, o.total
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending' AND NOT o.paid;
-- 2. A sort that does not fit in work_mem writes to disk; with enough memory it does not.
SET work_mem = '256kB';
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT * FROM orders ORDER BY total;
SET work_mem = '64MB';
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT * FROM orders ORDER BY total;
-- 3. A hash aggregate that does not fit splits its groups into batches on disk.
SET work_mem = '256kB';
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT customer_id, placed, count(*) FROM orders GROUP BY customer_id, placed;
RESET work_mem;An estimate far from the actual rows. The query asks for orders that are pending and unpaid. The planner knows that
1 % of orders are pending and 1 % unpaid, and, treating the two columns as independent, multiplies them: 0.01 % of
200,000, rows=20. In this shop the two conditions are the same 2,000 orders, so the actual rows were 2,000, 100
times the estimate. Expecting 20 rows, the planner chose a nested loop with an index lookup per order, and it ran
loops=2000 with 6,000 buffer hits. The statistics lesson of this track teaches the fix, extended statistics on the
two columns.
Rows removed by a filter. Rows Removed by Filter: 198000 means the scan read 200,000 rows to keep 2,000. On a
small table that is fine; on a large one it is the first sign that an index, or a partial index, would pay off, which
the next lessons of this module build.
A sort or a hash that spills. With work_mem at 256kB the sort of 200,000 orders printed Sort Method: external merge Disk: 8256kB and temp read=3090 written=3165; with 64MB it sorted in memory, quicksort Memory: 15520kB. A
hash aggregate spills differently: with 256kB it had planned 4 partitions for the 20,000 groups it expected, met
200,000 and printed Batches: 85 and Disk Usage: 7272kB.
Fix the estimate before the plan
When estimated and actual rows differ by ten times or more, change what the planner knows (fresh ANALYZE, more
statistics, extended statistics) before you force a plan. A better estimate fixes this query and every other query
that uses the same columns.
Checkpoint
Check: The warning signs of a plan
Choose the option that answers your question
EXPLAIN takes options in parentheses, and each answers a different question. The run tries five of them:
options_session.py
"""EXPLAIN's options for particular questions, and a plan in FORMAT JSON read by a program."""
import json
from pg_session import Server
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
"default_statistics_target": 1000, "timezone": "'UTC'"}
QUERY = ("SELECT c.city, o.id, o.total FROM orders AS o JOIN customers AS c ON c.id = o.customer_id "
"WHERE o.status = 'pending' AND NOT o.paid")
def nodes(plan, depth=0):
"""Every node of a JSON plan, with its depth in the tree."""
yield depth, plan
for child in plan.get("Plans", []):
yield from nodes(child, depth + 1)
with Server(settings=SETTINGS) as pg:
pg.up()
pg.psql("setup.sql", flags=("-q",))
pg.psql("options.sql")
# FORMAT JSON is for programs: read the plan of the pending-and-unpaid query and compare each node's estimate
# with what it really returned. Both counts are per loop: the inner side of a nested loop runs once per outer row.
plan = json.loads(pg.query(f"EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, FORMAT JSON) {QUERY}"))[0]["Plan"]
print("-- EXPLAIN (ANALYZE, FORMAT JSON) of the pending-and-unpaid query, read by Python (rows per loop):")
print(f"{'node':<30}{'estimated':>10}{'actual':>9}{'loops':>7}{'actual/est':>12}")
for depth, node in nodes(plan):
name = " " * depth + node["Node Type"]
if "Relation Name" in node:
name += f" on {node['Relation Name']}"
estimated, actual = node["Plan Rows"], node["Actual Rows"]
print(f"{name:<30}{estimated:>10}{actual:>9.0f}{node['Actual Loops']:>7}{actual / estimated:>12.1f}")
print()Output
$ psql -X -q -f setup.sql
$ psql -X -a -f options.sql
-- GENERIC_PLAN: the plan for a statement with a parameter, without a value for it (PostgreSQL 16 and later).
EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE customer_id = $1;
QUERY PLAN
------------------------------------------------------------------------------------
Bitmap Heap Scan on orders (cost=5.07..319.59 rows=100 width=32)
Recheck Cond: (customer_id = $1)
-> Bitmap Index Scan on orders_customer_idx (cost=0.00..5.04 rows=100 width=0)
Index Cond: (customer_id = $1)
(4 rows)
-- SERIALIZE and MEMORY (PostgreSQL 17 and later): the bytes sent to the client and the planner's own memory.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, SERIALIZE, MEMORY) SELECT * FROM orders WHERE customer_id = 42;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on orders (cost=5.07..319.59 rows=100 width=32) (actual rows=100.00 loops=1)
Recheck Cond: (customer_id = 42)
Heap Blocks: exact=100
Buffers: shared hit=100 read=2
-> Bitmap Index Scan on orders_customer_idx (cost=0.00..5.04 rows=100 width=0) (actual rows=100.00 loops=1)
Index Cond: (customer_id = 42)
Index Searches: 1
Buffers: shared read=2
Planning:
Memory: used=13kB allocated=32kB
Serialization: output=6kB format=text
(11 rows)
-- SETTINGS: every planner setting that differs from its default.
EXPLAIN (SETTINGS) SELECT * FROM orders WHERE customer_id = 42;
QUERY PLAN
------------------------------------------------------------------------------------
Bitmap Heap Scan on orders (cost=5.07..319.59 rows=100 width=32)
Recheck Cond: (customer_id = 42)
-> Bitmap Index Scan on orders_customer_idx (cost=0.00..5.04 rows=100 width=0)
Index Cond: (customer_id = 42)
Settings: max_parallel_workers_per_gather = '0', jit = 'off'
(5 rows)
-- ANALYZE runs a DELETE for real, so run it in a transaction and roll it back; WAL shows what it wrote.
BEGIN;
BEGIN
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, WAL) DELETE FROM orders WHERE status = 'pending';
QUERY PLAN
------------------------------------------------------------------------------------------------
Delete on orders (cost=0.00..3971.00 rows=0 width=0) (actual rows=0.00 loops=1)
Buffers: shared hit=3487
WAL: records=2000 bytes=108128
-> Seq Scan on orders (cost=0.00..3971.00 rows=2000 width=6) (actual rows=2000.00 loops=1)
Filter: (status = 'pending'::text)
Rows Removed by Filter: 198000
Buffers: shared hit=1471
Planning:
Buffers: shared hit=10 read=1
(9 rows)
ROLLBACK;
ROLLBACK
SELECT count(*) AS pending_orders FROM orders WHERE status = 'pending';
pending_orders
----------------
2000
(1 row)
-- EXPLAIN (ANALYZE, FORMAT JSON) of the pending-and-unpaid query, read by Python (rows per loop):
node estimated actual loops actual/est
Nested Loop 20 2000 1 100.0
Seq Scan on orders 20 2000 1 100.0
Index Scan on customers 1 1 2000 1.0
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 options_session.py
options.sql
-- GENERIC_PLAN: the plan for a statement with a parameter, without a value for it (PostgreSQL 16 and later).
EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE customer_id = $1;
-- SERIALIZE and MEMORY (PostgreSQL 17 and later): the bytes sent to the client and the planner's own memory.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, SERIALIZE, MEMORY) SELECT * FROM orders WHERE customer_id = 42;
-- SETTINGS: every planner setting that differs from its default.
EXPLAIN (SETTINGS) SELECT * FROM orders WHERE customer_id = 42;
-- ANALYZE runs a DELETE for real, so run it in a transaction and roll it back; WAL shows what it wrote.
BEGIN;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, WAL) DELETE FROM orders WHERE status = 'pending';
ROLLBACK;
SELECT count(*) AS pending_orders FROM orders WHERE status = 'pending';GENERIC_PLAN explained customer_id = $1 without a value, the plan a prepared statement can reuse for any customer: a
generic plan: A plan made for a statement with parameters ($1, $2 …) without knowing their values, so that it can be reused for any value. EXPLAIN (GENERIC_PLAN) shows one.. SERIALIZE measured the conversion of the result into text for the client,
output=6kB for 100 rows, without sending it, and MEMORY the planner’s own memory, used=13kB. SETTINGS listed
the planner settings that differ from their defaults on this server, max_parallel_workers_per_gather = '0' and
jit = 'off', which is how a plan copied from one server tells you why it differs on another.
EXPLAIN ANALYZE executes the statement, so a DELETE really deletes. The run wrapped one in BEGIN and ROLLBACK:
the plan reported WAL: records=2000 bytes=108128, one record per deleted row, and afterwards all 2,000 pending orders
were still there. Finally, FORMAT JSON gave the plan of the pending-and-unpaid query as data, and a few lines of
Python printed each node’s estimate beside its actual rows: 100.0 times the estimate on the scan and the join, 1.0 on
the index lookup.
Answer two questions about what you are investigating:
Answer the questions above: the table then marks what fits your case and says why the rest does not.
| Form | Leaves the data alone | Shows what really happened | Works without parameter values |
|---|---|---|---|
| The plan only Statement: EXPLAIN | Good plans the statement and runs nothing | Poor estimates only: costs, rows and widths | Poor a $1 without a value is an error |
| The plan run and measured Statement: EXPLAIN (ANALYZE) | Poor runs the statement: an UPDATE really updates | Good actual rows, loops and, in 18, buffers | Poor needs real values |
| The WAL a change writes Statement: EXPLAIN (ANALYZE, WAL) | Poor runs the change; wrap it in BEGIN and ROLLBACK | Good WAL records and bytes, 2,000 records for the recorded DELETE | Poor needs real values |
| Rows converted to text Statement: EXPLAIN (ANALYZE, SERIALIZE) | Mixed runs the statement; safe for a SELECT | Good the bytes converted for the client | Poor needs real values |
| A plan for any value Statement: EXPLAIN (GENERIC_PLAN) | Good runs nothing | Poor estimates only, for an unknown value | Good made for $1, $2 … |
| A plan for a program Statement: EXPLAIN (ANALYZE, FORMAT JSON) | Mixed with ANALYZE it runs the statement, as the text form does | Good the same numbers as keys a program can read | Poor needs real values |
How the answers decide
- “Does the statement change data?” Yes: INSERT, UPDATE or DELETE: adds a caveat to The plan run and measured, The WAL a change writes and A plan for a program. ANALYZE executes the change. Run it inside BEGIN and ROLLBACK, as the recorded DELETE did, or on a copy of the data.
- “What do you need to know?” Why a query is slow: rules out The plan only, A plan for any value and The WAL a change writes. Only a measured run shows where the rows and the pages really went; estimates alone cannot show a wrong estimate.
- “What do you need to know?” How much WAL a change writes: rules out The plan only, A plan for any value, Rows converted to text and A plan for a program. The WAL option adds the records and bytes a statement wrote, which no other form reports.
- “What do you need to know?” The plan for a statement with $1: rules out The plan only, The plan run and measured, The WAL a change writes, Rows converted to text and A plan for a program. Every other form needs a value for $1; GENERIC_PLAN shows the plan that suits any value.
- “What do you need to know?” Numbers a script can check: rules out The plan only, The WAL a change writes, Rows converted to text and A plan for any value and adds a caveat to The plan run and measured. FORMAT JSON gives the same numbers as named keys, which a program reads without parsing text.
To see plans of slow statements in production without running EXPLAIN yourself, the auto_explain module writes the
plan of every statement slower than a threshold to the server log; this track’s monitoring module sets it up.
Checkpoint
Check: EXPLAIN options
Practise: read the plans of the shop
Each question below refers to one of the recorded plans: the estimate check, the nested loop, the buffers and the spills. Before you begin, take the pending-and-unpaid plan and say, node by node, which number you would show a colleague first and why:
Practice that adapts to you
Practise this lesson
10 questions about 5 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
On your own server
Pick a slow query from your application and run EXPLAIN (ANALYZE, BUFFERS) on it inside BEGIN and ROLLBACK.
Write down the node whose actual rows differ most from its estimate, and the node with the most buffers.
Interview questions
Warm-up (fresher): How do EXPLAIN and EXPLAIN ANALYZE differ, and when is EXPLAIN ANALYZE unsafe to run?
EXPLAIN only plans the statement: it prints the tree of plan nodes the planner chose, with each node’s estimated cost in planner units, the rows it expects to return and their width, and it runs nothing. EXPLAIN ANALYZE also executes the statement and adds what really happened at every node: the actual rows, the number of loops, and in PostgreSQL 18 the buffers it touched without asking for them. Because it executes the statement, EXPLAIN ANALYZE of an INSERT, UPDATE or DELETE changes data, takes the same locks and fires the same triggers, so on real data it belongs inside BEGIN … ROLLBACK, or on a copy. On a long-running query it also takes as long as the query itself.
Two questions go further: a plan whose estimate is 100 times too low, and a report that writes gigabytes of temporary files. The module’s question page gives each one a first step and the follow-ups to expect:
Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.
-
Mid to senior
A join of orders to customers takes 40 seconds. EXPLAIN ANALYZE shows a Nested Loop whose outer scan estimated 20 rows and returned 2,000,000, and whose inner index scan ran with loops=2000000. Explain what went wrong and how you would fix it without forcing a plan.
-
Mid to senior
A nightly report sorts and groups 50 million rows. Its EXPLAIN ANALYZE shows Sort Method external merge with Disk 3.1GB and a HashAggregate with Batches 256. The server has 64 GB of RAM and work_mem = 4MB for everyone. What do you change, and what do you check before and after?
Flashcards · 5 cards
Recall it, then check
Say the answer to yourself before you look: recalling it is what makes it stick.
-
What do the costs in EXPLAIN mean?
EXPLAIN shows the plan with estimated costs, rows and widths. Costs are planner units built from page and row costs, never milliseconds.
-
How do you read EXPLAIN ANALYZE output?
It runs the statement and adds actual rows and loops. Read a plan from the deepest node up; each node's total includes the nodes below it, and a node's real output is actual rows times loops.
-
What buffer counts does EXPLAIN ANALYZE show in PostgreSQL 18?
Hits came from shared buffers, reads from the operating system, and temp pages from spilled sorts and hashes. PostgreSQL 18 includes these buffer counts by default.
-
What are the first warning signs in a plan?
Estimates far from the actual rows, large "Rows Removed by Filter" counts, and sorts or hashes that report disk use.
-
Which EXPLAIN options answer specific questions?
GENERIC_PLAN for $1 parameters; SERIALIZE, MEMORY, SETTINGS and WAL for their own costs; FORMAT JSON for programs; and wrap any change you analyse in BEGIN ... ROLLBACK.
Key takeaways
EXPLAINshows the plan with estimated costs, rows and widths; costs are planner units built from page and row costs, never milliseconds.EXPLAIN ANALYZEruns the statement and adds actual rows and loops; a plan is read from the deepest node up, and each node’s total includes the nodes below it.- Actual rows are averages per loop, printed with two decimals in PostgreSQL 18, so a node’s real output is rows times loops.
- PostgreSQL 18 includes buffer counts in
EXPLAIN ANALYZE: hits came from shared buffers, reads from the operating system, temp pages from spilled sorts and hashes. - The first warning signs are estimates far from the actual rows, large
Rows Removed by Filtercounts and sorts or hashes that report disk use. - Options answer specific questions:
GENERIC_PLANfor$1parameters,SERIALIZE,MEMORY,SETTINGSandWALfor their own costs,FORMAT JSONfor programs, andBEGIN … ROLLBACKaround any change you analyse.
Terms in this lesson
- generic plan
- A plan made for a statement with parameters ($1, $2 …) without knowing their values, so that it can be reused for any value. EXPLAIN (GENERIC_PLAN) shows one.
- plan node
- One step of a query plan, such as a Seq Scan, an Index Scan, a Hash Join or a Sort. It receives rows from the nodes below it and passes its own rows to the node above.
- query plan
- The tree of steps PostgreSQL's planner chooses to run one statement: scans at the leaves, then joins, aggregates and sorts above them. EXPLAIN prints it, with estimated costs and rows for every step.
References
- PostgreSQL 18 documentation: 14.1 Using EXPLAIN (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: EXPLAIN (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 14.2 Statistics Used by the Planner (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 19.7 Query Planning (planner cost constants) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 19.4 Resource Consumption (work_mem, hash_mem_multiplier) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: F.3 auto_explain (The PostgreSQL Global Development Group)
- PostgreSQL 18 release notes (The PostgreSQL Global Development Group)
Report a problem with this lesson
Kept only in this browser. Your Learn progress