ExplainSQL

Find out why your PostgreSQL query is slow, get a fix, and prove that it works, without leaving the terminal.

What it is

ExplainSQL reads the plans PostgreSQL prints for EXPLAIN (ANALYZE, BUFFERS) and tells you what they mean. Instead of handing you a prettier tree and leaving the rest to you, it goes all the way from the plan to a fix you can trust:

1. Diagnose. It works out the time and pages spent in each node, including the cases where simple subtraction is wrong: parallel workers, CTEs, InitPlans and triggers. The first line on the screen is a verdict, such as “20.0 ms. 99% of it in Seq Scan on orders o, which reads 200,000 rows to keep 10.”
2. Suggest. Thirteen rules flag well-known problems, each with its evidence and what to do. An index advisor writes CREATE INDEX CONCURRENTLY statements, and when no index would help, it says why.
3. Prove. Connected to a database, it runs the statement in a transaction that is always rolled back, tests the suggested index with HypoPG or by building it inside that transaction, and shows before and after, pages first.

On top of that loop, it answers the questions that usually come next. Why did the planner not use my index? Is the generic plan of this prepared statement bad for some values? Which locks does this query take, and what would wait for them? Why was this update not HOT? Did a plan get worse in this pull request? When did the plan of this statement change last night? Which requests run the same query fifty times?

How it fits into your day

You can use it in three ways, and they all lead to the same screen:

• Offline, on a plan you already have: a file, the clipboard, or psql’s output piped in. No credentials and no network.
• As psql’s pager: PSQL_PAGER='explainsql --pager', and every EXPLAIN you run in psql opens in the viewer.
• Connected: explainsql -d "$DATABASE_URL" -f slow.sql runs the query safely and unlocks the proof, the planner questions, locks and writes.

It also has commands for the rest of the team’s workflow: check and a GitHub Action for CI, diff to compare two plans, top for pg_stat_statements, logs and requests for server logs, and anonymize to share a plan without giving away your schema.

Where to go next

• New here? Getting started takes you from install to your first proven fix in a few minutes.
• Looking for a feature? The user guide has a chapter for each one.
• Need a flag? The command-line reference lists them all.
• Puzzled by a finding? The rule catalog explains every one, with an example from a real plan.
• Something odd? Try troubleshooting.
• Curious how it works, or want to help? Read the architecture and the contributing guide.

ExplainSQL supports PostgreSQL 12 to 18. It is free software under the MIT or Apache-2.0 license, at your option.

Getting started

This page takes you from nothing to your first proven fix. It should take a few minutes.

Install

The quickest way is the install script. It downloads the right binary for your platform from the latest GitHub release, checks its SHA-256 checksum, and puts explainsql in ~/.local/bin:

curl -fsSL https://github.com/onplt/explain-sql/releases/latest/download/install.sh | sh

On Windows, in PowerShell, it goes to %LOCALAPPDATA%\explainsql\bin:

irm https://github.com/onplt/explain-sql/releases/latest/download/install.ps1 | iex

Both scripts take options. To pin a version or choose where the binary goes:

curl -fsSL https://github.com/onplt/explain-sql/releases/latest/download/install.sh | sh -s -- --version 0.3.0 --to ~/bin

& ([scriptblock]::Create((irm https://github.com/onplt/explain-sql/releases/latest/download/install.ps1))) -Version 0.3.0 -To C:\tools

Prebuilt binaries cover Linux (x86_64 and aarch64, fully static, so they run on any distribution, Alpine included), macOS (Intel and Apple silicon) and Windows (x86_64). You can also download an archive from the releases page and unpack it yourself; each archive comes with a .sha256 file, and the release has a SHA256SUMS list.

If you have Rust 1.85 or later, Cargo works too:

cargo install explainsql --locked                                           # the latest release, from crates.io
cargo install --git https://github.com/onplt/explain-sql explainsql --locked # the latest commit

Check that it runs, then open the bundled example plan:

explainsql --version
explainsql --demo

The demo is a real plan with a real problem: a nested loop that scans a whole table once per outer row. Move around with the arrow keys or j and k, press ? for the keys and q to quit.

Read your first plan

Give it any plan you have:

explainsql plan.json                 # a file, JSON or text
pbpaste | explainsql                 # the clipboard (xclip -o or wl-paste on Linux)
psql -XAtq -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT …" | explainsql

You do not have to clean the plan up first. ExplainSQL finds it inside psql’s aligned, wrapped, expanded or CSV output, inside server log lines (stderr, csvlog or jsonlog), inside a Markdown code fence, or inside a result cell copied from pgAdmin or another GUI client, with or without the QUERY PLAN header and the (N rows) footer.

Capture plans that say more

The more the plan contains, the more ExplainSQL can tell you. The best capture is:

SET track_io_timing = on;   -- a superuser setting; or turn it on in postgresql.conf
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON) SELECT …;

• ANALYZE runs the statement and gives real times and row counts. Without it you get estimates only, and findings are based on costs.
• BUFFERS gives the pages each node read. ExplainSQL leans on pages because, unlike times, they do not depend on what happens to be cached.
• VERBOSE gives output columns and qualified names, which makes the advice more precise.
• SETTINGS lists planner settings changed from their defaults, which is how ES013 catches a forgotten enable_seqscan = off.
• track_io_timing lets ExplainSQL say how much of the time went to reading from disk, and whether the cache was cold.

JSON and text are equally welcome. Text is what psql and auto_explain print by default, and it is what people usually paste into chats and issues.

Careful: EXPLAIN ANALYZE really runs the statement. For an UPDATE or DELETE, wrap it in BEGIN; … ROLLBACK;, or let ExplainSQL run it for you in connected mode, which always rolls back.

Make sense of the screen

From top to bottom:

• The verdict. One sentence: how long the statement took, where most of the time went, and the finding about that node. Often that is all you need to read.
• The statement line. Planning and execution time, pages read and the share that came from the cache, and when they matter, time in triggers, in JIT compilation, outside the plan tree, and spent reading from disk.
• The plan. One row per node: its share of the runtime, its own time, a bar, the node, actual rows (with ×loops when it ran more than once), the estimate, and the pages it read itself. A ▲ or ▼ marks rows 10× or more above or below the estimate, and a ! in the margin marks a node with a finding. Similar siblings, such as the scans of 300 partitions, start folded into one row.
• The details of the selected node: every figure, its conditions, its findings, and what the planner said when you asked it why.
• Findings and advice. f shows the findings, i shows the suggested indexes and rewrites. Tab moves into the list, and Enter jumps to the node.

All the times are the node’s own, which is the time spent in the node minus the time spent in its children, worked out correctly for parallel plans, CTEs and InitPlans. Press x to see times including children, w for CPU time summed across parallel workers, and b to rank nodes by pages instead of time. The viewer chapter covers every key.

Or get a report

When the output is not a terminal, or with --print, you get a static report instead of the viewer:

explainsql --print plan.json                    # text, for the terminal
explainsql --print --format md plan.json        # Markdown, for an issue or a pull request
explainsql --print --format json plan.json      # JSON, for scripts and other tools

See reports and output for what each format holds.

Run your first query

Connected mode is where ExplainSQL can do the most. Point it at a database and a statement:

explainsql -d "$DATABASE_URL" -f slow.sql
explainsql -d shop -c "SELECT * FROM orders WHERE customer_id = 42"

-d takes whatever psql takes: a URL such as postgresql://app@db.internal/shop, key=value settings, or just a database name. The PG* environment variables, ~/.pg_service.conf and ~/.pgpass work exactly as they do for psql. If psql connects, ExplainSQL connects.

The viewer shows the estimated plan right away, runs EXPLAIN ANALYZE in the background (press Esc to cancel), then swaps in the measured plan. The statement runs inside a transaction that is always rolled back, and it runs read-only unless you pass --allow-dml. Connected mode explains every safety rule.

Now try the loop from the demo at the top of the page:

1. Press 1 to go to the slowest node.
2. Press y to ask the planner why it chose that node. For a sequential scan, it plans the statement again with sequential scans turned off, and tells you whether any index could serve the condition at all.
3. Press i to see the suggested index.
4. Press t to test it. With HypoPG installed, the test is instant and nothing is built. Without it, start ExplainSQL with --allow-ddl and it will offer to build the index inside the rolled-back transaction and measure the query with it.
5. Read the result in the details: “Before → after: Pages 2,420 → 15 (161× fewer), execution 9.98 ms → 0.061 ms (164× faster)”. Press c to copy the CREATE INDEX CONCURRENTLY statement.

The same, as a report you can paste into a ticket:

explainsql -d "$DATABASE_URL" -f slow.sql --allow-ddl --print --prove --format md

Where next

• Use it as psql’s pager, so that it is there whenever you type EXPLAIN.
• Learn what the rules look for.
• Browse the user guide for the rest: parameters, locks, writes, CI, logs and more.

User guide

This guide has one chapter per thing ExplainSQL does. You do not need to read it in order: getting started covers the basics, and every chapter after that stands on its own.

Reading plans

• The viewer: the screen, every key, folding, search, the icicle view and colors.
• As psql’s pager: open every EXPLAIN from psql in the viewer.
• Reports and output: text, Markdown and JSON reports, exit codes, and --debug-parse.

Working against a database

• Connected mode: running statements safely, connection settings, and testing a suggested index.
• Ask the planner why: why it chose a sequential scan, a nested loop or a spilling sort, and whether it was right.
• Statements with parameters: custom and generic plans, and the values that make a prepared statement slow.
• What a statement locks: relation locks, the fast path, unused indexes, and the migrations that would wait.
• What a write costs: HOT updates, the indexes that prevent them, index entries and WAL.

Finding the queries worth looking at

• The costliest statements: explainsql top, on top of pg_stat_statements.
• Plan changes in server logs: explainsql logs, on top of auto_explain.
• N+1 loops in requests: explainsql requests, on top of statement logging.

Working as a team

• Compare two plans: explainsql diff, node by node.
• Check plans in CI: explainsql check, locked plans, SARIF, and the GitHub Action.
• Share a plan: explainsql anonymize, to paste a plan anywhere without your schema and data.

Every option is also listed in the command-line reference.

The viewer

In a terminal, ExplainSQL opens plans in an interactive viewer. It is built to answer “why is this slow?” on the first screen, and to let you dig in from there.

The screen

• The verdict sits on the top line: the statement’s time, where most of it went, and why. For example: “20.0 ms. 99% of it in Seq Scan on orders o, which reads 200,000 rows to keep 10.”
• The statement line under it: planning and execution time, pages read and how many came from the cache, and when they matter, time in triggers, in JIT, outside the plan tree, and reading from disk.
• The plan tree: each node’s share of the runtime, its own time, a bar, the node, actual rows, the estimate, and the pages it read itself. ▲ and ▼ mark rows that are 10× or more above or below the estimate. ! in the margin marks a node with a finding.
• The details of the selected node: every figure the plan has for it, its conditions, its findings, and the answer when you ask the planner about it.
• Findings or advice, at the bottom, with the status line under them.

On a wide terminal (110 columns or more), the details sit next to the tree; on a narrower one, below it. Columns are dropped before node names get too short to read, so the viewer stays usable at 80×24.

Keys

Times, shares and views

By default, each node shows its own time: the time spent in the node, minus the time spent in its children. That sounds like simple subtraction, but it is not. Times in a plan are averages per loop while buffers are totals; under a Gather, several processes run a node side by side; a CTE’s work shows up inside whichever scan pulls its rows first; an InitPlan runs inside the node that needs its result, not the one it is listed under. ExplainSQL handles all of these, so the shares add up to the statement’s time. The architecture has the details.

Three keys change the view:

• x shows time including children, which is what EXPLAIN prints as “actual time”.
• w shows CPU time, summed over the leader and its parallel workers, instead of wall-clock time.
• b ranks nodes by pages instead of time. Pages do not change with the state of the cache, which makes them the steadier measure of how much work a node does.

A plan without timing (captured with TIMING OFF, or not run at all) has no times to show, so the hotspots are ranked by pages instead. The viewer never fills in a time from an estimate.

Folding and search

Any node can be folded with h or ← and opened again with l or →. Runs of four or more similar leaves start folded into a single row that adds up their time, rows and pages, so a plan that scans 1,000 partitions shows one line, not a thousand. Leaves are similar when they have the same type and the same relation and conditions once numbers are blanked out.

/ searches node names and conditions, and n and N move between matches. A match hidden inside a fold is opened for you.

Findings and advice

f lists the findings, most severe first, and i lists the advice: suggested indexes, rewrites of conditions that wrap a column in a function or a cast, and the reasons a slow scan gets no index. Press Tab to move into the list, then Enter to jump to the node.

On a suggested index, c copies its CREATE INDEX CONCURRENTLY statement. It uses the OSC 52 escape sequence, so it reaches your local clipboard even over SSH and inside tmux, as long as your terminal supports it.

The icicle view

F replaces the tree with an icicle: the root across the top, and each node in a box under its parent, as wide as the CPU time spent in it and below it, and colored by its own share. It is the quickest way to see where the time goes in a big plan.

• Workers of a parallel plan add up under the node that gathers them.
• InitPlans, SubPlans and CTEs sit under the node they belong to.
• Time in triggers, which happens outside the tree, is shown in the title.
• A plan without timing is drawn by estimated cost, and the title says so.

In the icicle, k and j go to the parent and to the widest child, and h and l move along the row. Enter zooms in on a box so that it fills the width; Enter on it again zooms back out, and g returns to the whole plan. Nodes too narrow for a single column are folded into their parent and shown as …, and zooming opens them. The details, search and the hotspot keys work as in the tree, and F takes you back to the tree on the same node.

In connected mode

When ExplainSQL ran the statement itself (connected mode), the viewer can do more:

• It shows the estimated plan at once, runs EXPLAIN ANALYZE in the background with a counter in the status line, and swaps in the measured plan when it is ready. Esc cancels the run.
• r runs the statement again. e opens it in $VISUAL or $EDITOR; when you save and close, the viewer shows the estimated plan of the edited statement and runs it. After either, the status line compares the new run with the previous one, pages first.
• t, y, L and W test an index, ask the planner, and show the locks and the writes, as described in their chapters.

Colors and themes

The viewer follows your terminal: true color when COLORTERM says so, otherwise 256 or 16 colors. With NO_COLOR set, it falls back to bold, dim and reverse video. Use --theme light on a light background. Severities and misestimates always carry a word or a symbol as well as a color, so nothing depends on color alone.

The viewer reads keys from the terminal even when the plan came in on standard input, so pbpaste | explainsql is fully interactive.

As psql’s pager

An EXPLAIN tool is something you reach for now and then, which makes it easy to forget. The pager mode fixes that: you keep working in psql exactly as before, and every plan simply opens in the viewer.

Set it up

Add this to your shell’s profile:

export PSQL_PAGER='explainsql --pager'

and this to your ~/.psqlrc, so that short plans, which fit on the screen, go to the pager too:

\pset pager always

Now run an EXPLAIN in psql:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

The plan opens in the viewer. Press q and you are back at the psql prompt.

What happens to everything else

ExplainSQL looks at what psql sends to the pager. A plan opens in the viewer. Anything else, such as the result of a SELECT, goes on to your usual pager: $EXPLAINSQL_PAGER if set, otherwise $PAGER, otherwise less -S. It never hands output back to explainsql, so setting PAGER to ExplainSQL itself cannot loop. If none of those pagers can run, the output is printed directly.

When the output is not a terminal, for instance when you redirect psql’s output to a file, everything passes through unchanged.

So if you like pspg for result sets, keep it:

export PSQL_PAGER='explainsql --pager'
export EXPLAINSQL_PAGER='pspg'

Tips

• Use EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) for the richest analysis, and turn on track_io_timing in the session if you can. Getting started explains what each option adds.
• Both the text and JSON formats work, as does psql’s expanded mode (\x).
• Without the pager, you can still send a single result to ExplainSQL with psql’s \g | explainsql.
• The pager mode reads plans only. To test an index or ask the planner why, run the statement in connected mode.

Reports and output

The viewer is for exploring. Reports are for everything else: pasting into a ticket, posting on a pull request, feeding another program, or failing a script.

When you get a report

ExplainSQL opens the viewer only when standard output is a terminal (and TERM is not dumb). Otherwise, or whenever you pass --print, it prints a report:

explainsql --print plan.json
explainsql plan.json > report.txt                 # not a terminal: a report
explainsql -d shop -f slow.sql --print --prove    # some options only make sense in a report

--prove, --why-not and --params print reports; in the viewer you use t and y instead.

Formats

Text (--format text, the default) is made for the terminal: the verdict, the statement’s figures, the plan tree with each node’s share, time, a bar and misestimate marks, then the findings, the advice, and any sections you asked for (why not, parameters, locks, writes). Colors are used when the output is a terminal and NO_COLOR is not set; --color always or --color never overrides that.

Markdown (--format md) is ready to paste into a GitHub or GitLab issue, a pull request or a wiki. The plan becomes a table, findings link to their rule’s page, and suggested indexes sit in SQL code blocks.

JSON (--format json) is for other programs. It is the complete analysis, pretty-printed:

Keys that do not apply to a report are left out. Times are in milliseconds.

The other commands have their own JSON reports, described in their chapters: diff, check (which also speaks SARIF), logs, top and requests.

Confidence and severity

Findings have a severity that follows the share of the runtime involved: half or more is high, a fifth or more is medium, anything less is low. The rule catalog has the details.

Suggestions have a confidence, shown as SURE, LIKELY or MAYBE in text reports and as high, medium or low in JSON. A suggestion starts at high and is lowered for an inferred column, an operator class that depends on the collation, pg_trgm, or a comparison with a value only known at run time. One that was tested and did not help drops to low.

Exit codes

explainsql check has its own: 0 when every plan passed, 1 when one failed, 2 when the check could not run. A reader that closes the pipe early, such as head, is not an error.

Checking how a plan was read

If a report looks wrong, the first question is whether the plan was read correctly. --debug-parse prints what the parser understood instead of the analysis: the format it detected, the tree with estimates and actuals, the statement’s summary and any warnings. With --format json, it prints the parsed plan as JSON.

$ explainsql --demo --debug-parse
format: Text
Nested Loop  [estimated rows 15 cost 91527.00]  [actual rows 14 × 1 loops, 369.223 ms per loop]
  Seq Scan on public.orders o  [estimated rows 10 cost 4917.00]  [actual rows 10 × 1 loops, 12.134 ms per loop]
  Seq Scan on public.order_items oi  [estimated rows 300000 cost 4911.00]  [actual rows 300000 × 10 loops, 18.926 ms per loop]
statement: planning 0.526 ms; execution 369.281 ms; settings: enable_hashjoin=off, enable_material=off, enable_mergejoin=off, max_parallel_workers_per_gather=0
warnings: none

ExplainSQL never gives up on a plan it partly understands: unfamiliar properties are kept, unreadable lines are set aside with a warning, and a truncated plan keeps the nodes before the cut. If you find a plan it reads wrongly, please open an issue with it, anonymized with explainsql anonymize if needed.

Connected mode

A plan on its own can only tell you so much. With a connection, ExplainSQL can run the statement itself, check its advice against your catalog, test a suggested index before anyone creates it, and answer the questions in the next few chapters. This chapter covers how it connects, what it promises when it runs your statement, and how it tests suggestions.

explainsql -d "$DATABASE_URL" -f slow.sql
explainsql -d shop -c "SELECT * FROM orders WHERE customer_id = 42"
explainsql -d shop -f slow.sql --print          # a report instead of the viewer

Connecting

-d takes what psql takes:

• a URL: postgresql://app@db.internal:5432/shop?sslmode=verify-full;
• key=value settings: "host=db.internal dbname=shop user=app";
• or just a database name: shop.

Anything -d leaves out comes from the same places libpq looks, in the same order: the service file (PGSERVICE, ~/.pg_service.conf, then the system file), the PG* environment variables (PGHOST, PGPORT, PGUSER, PGDATABASE, PGSSLMODE and the rest), and finally the defaults: the local Unix socket or localhost, port 5432, and your user name.

Passwords come from ~/.pgpass (%APPDATA%\postgresql\pgpass.conf on Windows). Like libpq, ExplainSQL ignores that file when other users can read it.

TLS follows libpq’s sslmode:

What ExplainSQL promises when it runs your statement

This is the heart of connected mode, so it is worth reading once:

• Every run happens inside a transaction that is rolled back, with a statement_timeout (--timeout, 30 seconds by default). The code has no path that commits.
• Statements that modify data or lock rows run only with --allow-dml: INSERT, UPDATE, DELETE, MERGE, and SELECT … FOR UPDATE or FOR SHARE, including inside a WITH. ExplainSQL finds out from the estimated plan, which it gets first: a ModifyTable or LockRows node means the statement writes or locks.
• Everything else runs in a READ ONLY transaction, so even a function that writes behind your back fails.
• One statement at a time, and only kinds that EXPLAIN accepts. A statement must start with SELECT, WITH, VALUES, TABLE, INSERT, UPDATE, DELETE or MERGE. DDL, CREATE TABLE AS and a pasted EXPLAIN are refused before anything runs, and the extended query protocol refuses several statements in one string.

A rollback cannot undo everything, and you should know what slips through:

• sequences keep the values they handed out;
• dblink calls and foreign data wrappers reach other systems, which have no idea about your rollback;
• rolled-back rows leave dead tuples behind until the next VACUUM;
• an UPDATE or DELETE holds its row locks until the rollback, so other sessions writing the same rows wait for it.

Use --allow-dml on development and staging databases, or on production only when you understand those effects.

--no-analyze shows the estimated plan only, without running the statement at all.

What the connection adds to the advice

Offline, every suggestion is labeled as unchecked: ExplainSQL cannot know your existing indexes, your collations or your write load. Connected, it reads the catalog for the tables in the plan, in a read-only transaction, and refines the advice:

• a suggested index that an existing valid index already covers becomes an explanation of why the planner probably did not use the existing one;
• a slow foreign-key check becomes a CREATE INDEX on the constraint’s referencing columns;
• text_pattern_ops is dropped for columns with the C collation, and the pg_trgm caveat is dropped when the extension is installed;
• each suggestion says how large the table is, so you know what building the index will cost.

Test a suggested index

A suggestion is a guess until it is measured. In the viewer, select a suggested index (press i, then Tab to the list if there are several) and press t. For a report, add --prove:

explainsql -d shop -f slow.sql --print --prove
explainsql -d shop -f slow.sql --print --prove --allow-ddl --runs 5

ExplainSQL tests it in one of two ways.

With HypoPG. If the extension is installed in the database (CREATE EXTENSION hypopg), ExplainSQL creates a hypothetical index in a read-only transaction and asks the planner for the plan with it. Nothing is built and nothing is locked, so it is safe anywhere and instant. The result is an estimate: the planner’s opinion of the plan, not a measurement. HypoPG is available on Amazon RDS and many other managed services.

By building it, with --allow-ddl. Without HypoPG, ExplainSQL can build the index for real inside a transaction that is rolled back, run the statement with EXPLAIN ANALYZE, and measure the difference. CREATE INDEX CONCURRENTLY cannot run inside a transaction, so this uses a plain CREATE INDEX, which blocks writes to the table while it builds. The viewer therefore shows the table’s size and asks before it starts, and the build gives up if it waits more than 2 seconds for its lock (lock_timeout). This is meant for development and staging databases.

Either way, you get before and after:

Before → after: Pages 2,420 → 15 (161× fewer), execution 9.98 ms → 0.061 ms (164× faster)
The plan uses: orders_customer_id_idx
Verification: measured with the index created and rolled back

How the comparison decides:

• Pages decide first. Unlike times, they do not depend on what the cache happens to hold.
• Then pages written to temporary files, for sorts and hashes that spill.
• Then time, or the estimated cost when nothing was run, and only for a change of more than 10% and more than 0.1 ms. Fewer pages but a slower run is reported as mixed.
• Each measured side runs once first, only to warm the cache. Without that, the run without the index would often meet a colder cache than the run with it, which comes right after a build that has just read the whole table.
• --runs N measures each side N times and compares the medians.

A suggestion that the planner does not use, or that does not make the statement better by more than the noise, drops to low confidence and says so. That is the point of the test: the advice you end up with has been checked against your data.

When you are happy with a result, press c to copy the CREATE INDEX CONCURRENTLY statement, and build it the usual way.

Ask the planner why

A plan shows what the planner chose, never what it turned down. You see a sequential scan and know there is an index on that table. You see a nested loop that runs 50,000 times. You see a sort spilling to disk. Is the planner wrong, or does it know something you do not?

ExplainSQL finds out by asking it again, with its choice taken away, and comparing the two plans.

explainsql -d shop -f slow.sql --print --why-not             # the slowest nodes
explainsql -d shop -f slow.sql --print --why-not orders      # the scans of a table, or of an index's table
explainsql -d shop -f slow.sql --print --why-not --measure --runs 3

In the viewer, select a node and press y.

What it asks

Without a table name, --why-not asks about the hot nodes, at most three: sequential scans with a condition that take 10% or more of the runtime, nested loops that ES005 flags or that follow an underestimated outer side, and, with --measure, sorts and hashes that spilled. With a name, it asks about the sequential scans of that table, or of the table an index belongs to.

The answers

UNUSABLE: no index can serve the condition. Even with sequential scans off, the planner still reads the whole table. ExplainSQL looks at the condition and the catalog to tell you why, which is usually the most useful thing on this page:

• the condition casts the column or wraps it in a function, so an index on the plain column does not apply (WHERE created_at::date = …, WHERE lower(email) = …);
• it ORs conditions on different columns;
• it uses <>, or an operator the index’s type does not serve (@> needs GIN, not b-tree);
• it is a LIKE pattern that starts with a wildcard, or a prefix LIKE on a column whose collation is not C, without a text_pattern_ops index;
• the indexes on the table start with another column, are invalid, or are partial with a predicate that does not match.

COSTLIER: the alternative is possible, but the planner estimates it more expensive. The answer says by how much. Within 10% is a close call that a small change in the data can flip. Without --measure, ExplainSQL also checks whether random_page_cost = 1.1, a common value for SSDs and cloud volumes, would make the planner choose the index by itself.

With --measure, it runs both plans the same number of times (--runs N), after one run that warms the cache, and compares them, pages first. Then it can tell you whether the planner was right:

• RIGHT: the alternative is not better. The planner knew what it was doing.
• MISESTIMATE: the alternative is better, and the scan’s rows were overestimated tenfold or more (for a nested loop, its input was underestimated). Fix the statistics: ANALYZE, a higher statistics target, or CREATE STATISTICS for correlated columns.
• COST MODEL: the alternative is better, the estimates were close, and with random_page_cost = 1.1 the planner picks the index on its own. That plan runs too, and the setting is suggested only if it is measured better as well.
• WRONG: the alternative is better, for a reason ExplainSQL could not pin down.
• HELPS or NO HELP, for a spill: whether enough work_mem to stay in memory makes the statement faster.
• UNSURE: the plans compare both ways (fewer pages but slower, say), or could not be matched.

An alternative that runs past the statement timeout, while the planner’s choice finished, counts as the planner being right. For a spill, the question is time: staying in memory but running slower does not help.

When the planner did not use an index that exists, the advice shows the reason found here instead of the usual list of possible reasons.

Here is one

Why not: the planner asked again (1)

  UNUSABLE     Why does Seq Scan on orders o not use an index?
               No index can serve the condition of Seq Scan on orders o: even with sequential scans
               off, the planner still reads all of orders.
               Why: no index on orders starts with customer_id
               → Create an index that serves the condition: see the advice.
               Planned again with enable_seqscan = off; estimated.

Safety and limits

The settings come from a fixed list of planner settings only (the enable_* switches, the cost constants, work_mem, hash_mem_multiplier, effective_cache_size, the collapse limits, plan_cache_mode and jit). They are set with SET LOCAL semantics inside the transaction that is rolled back, so they never outlast the run and never touch other sessions.

enable_* settings apply to the whole statement, so other scans and joins can change too when one is turned off. When that happens, the answer lists the other changes and is marked approximate.

Before PostgreSQL 18, the planner adds a huge penalty (10¹⁰ per node) to plans that use a disabled node; ExplainSQL takes it out before comparing costs.

Statements with parameters

Here is a classic: a query is slow in production, you copy it into psql with a real value, and it runs in a millisecond. Nothing is wrong with the query. What differs is how your application sends it.

An application rarely sends WHERE customer_id = 4242. It sends WHERE customer_id = $1, or ? through JDBC, with the value on the side. PostgreSQL can plan such a statement in two ways:

• a custom plan, made for the values of one execution, which is what you get in psql;
• the generic plan, made once to work for any value.

A prepared statement gets custom plans for its first five executions. From the sixth on, PostgreSQL switches to the generic plan if it estimates it cheaper than the custom plans were on average, and then keeps it. pgJDBC prepares a statement on the server from its fifth execution (prepareThreshold), so a statement your Java service runs often can easily end up on the generic plan. That plan may suit most values and be terrible for a few, and you will never see it in psql.

--params shows you what the application gets.

explainsql -d shop -c "SELECT * FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT ?" --params --print
explainsql -d shop -f latest_orders.sql --params --measure --print
explainsql -d shop -f latest_orders.sql --bind 1=4242 --bind 2=20 --measure --print

How it works

ExplainSQL prepares the statement as the application does, then:

1. Maps each parameter to what the statement does with it: the column it is compared with in a scan’s conditions (with =, a range or an IN list), or the LIMIT or OFFSET it counts rows for.
2. Picks values to try, from your data:
   • for equality: the most common values, the least common of those, and a value outside them, from pg_stats (for a partition, from the partitioned table’s statistics);
   • for a range: bounds from across the histogram;
   • for a LIMIT: 1, 10, 100, 1,000 and 10,000 rows;
   • for an OFFSET: 0, 1,000 and 100,000.
3. Tries the values one parameter at a time, holding the others at a typical value (the most common value, a range that keeps every row, a page of 10 rows, or the first page), and compares the custom plan for each value with the generic plan.
4. With --measure, runs both plans for each value whose custom plan differs, --runs N times each, after one run that warms the cache.

The verdict

Here is a real one, for a customer’s latest orders:

Parameters  SENSITIVE
  The plan depends on the values: with $1 = 8468, $2 = 10000, the generic plan does worse than the
  custom plan: pages 2,474 → 200,985 (81× more), execution 10.2 ms → 55.0 ms (5.4× slower).

  $1  integer, compared with orders.customer_id (=), held at 8468
  $2  bigint, the LIMIT, held at 10

  $1 = 8468   most common, 0.023% of rows
              the generic plan
  …
  $2 = 100    row count
              Parallel Seq Scan on orders, cost 4,891
              measured, custom plan → generic plan: pages 2,474 → 200,985 (81× more), execution 11.7
              ms → 68.6 ms (5.9× slower); the generic plan does worse

The generic plan walks the index on created_at backwards, hoping to find the customer’s rows early. For a small LIMIT that works; for a larger one it reads most of the table.

Will PostgreSQL switch?

The report also predicts whether PostgreSQL would switch to the generic plan after five executions. It switches when the generic plan’s estimated cost is below the average cost of the custom plans so far, each with a charge for planning. So the outcome can depend on which values the first five executions happen to have, and the report says when it does.

When PostgreSQL could switch and the generic plan does badly, the advice is to plan every execution for its own values:

• set plan_cache_mode = force_custom_plan for the application’s connections (in a JDBC URL, options=-c%20plan_cache_mode=force_custom_plan) or for its role;
• or keep the driver from preparing the statement on the server: prepareThreshold=0 in pgJDBC, for the connection or for one statement, or prepare_threshold = None in psycopg 3.

Either way, each execution is planned again, which costs planning time. An index that serves every value well fixes the cause instead, and the report’s advice may suggest one.

Details worth knowing

The plan in the report. With --measure, it is the generic plan run with the values it does worst with, and the findings and advice are about that plan. Without --measure, it is the generic plan, estimated with the typical values.

Giving values. --bind N=VALUE sets the value of $N, which is then the only value tried for it (and implies --params). Give a value for every parameter to compare the custom and generic plans for exactly those values, such as one call taken from a log. explainsql logs prints such a command for statements it saw switch to a generic plan.

Placeholders.

• $1, $2, … are read as PostgreSQL and pg_stat_statements write them.
• JDBC’s ? is converted in order when the statement has no $n, and ?? becomes the ? operator.
• A statement with $n placeholders cannot run without values, so it needs --params or --bind.

Safety. Every run is rolled back, as in connected mode, and every statement ExplainSQL prepares is deallocated afterwards, whatever happens. --params needs PostgreSQL 12 or later, for plan_cache_mode. It prints a report; the viewer does not show it yet.

Limits.

• Values are tried one parameter at a time, so the way columns depend on each other is not taken into account.
• A parameter inside an expression (lower(email) = $1), an array (= ANY($1)) or a SET clause gets no value from the statistics. Give one with --bind.

What a statement locks

EXPLAIN never shows locks, yet locks are behind some of the nastiest production incidents: a migration that hangs behind a long query and takes every later query down with it, or hundreds of sessions slowing each other down on the lock manager because each one locks 200 partitions. --locks shows you what a statement locks before that happens.

explainsql -d shop -f report.sql --locks --print
explainsql -d shop -c 'SELECT * FROM events WHERE created_at > $1' --bind 1=2025-12-20 --locks --print

In the viewer, press L.

How it works

When ExplainSQL runs a statement in connected mode, the transaction it is about to roll back still holds every lock the statement took. So ExplainSQL reads them right there, from pg_lock_status(), just before the rollback releases them. It also reads pg_locks and pg_stat_activity for other sessions’ locks on the same relations, and, from a second connection, watches what the statement waits for while it runs.

What the report tells you

Here is a statement that counts the last 30 days of a table partitioned by month:

Locks the statement takes
  26 relation locks on 1 table, 10 of them outside the fast path (16 slots).
  events  26 locks: AccessShareLock on the table, 12 partitions (the plan has none), 13 indexes (the
          plan uses none); 10 outside the fast path

  MEDIUM  10 of the 26 relation locks did not fit in the backend's 16 fast-path slots. …

  MEDIUM  The statement locks 12 partitions of events and 13 indexes, but the plan has none of them:
          the planner could not rule the others out when planning, and run-time pruning dropped them
          after they were locked.
          → Compare the partition key with a constant, or a parameter of a custom plan, rather than
          an expression the planner cannot evaluate, such as one of now(): …

In detail:

• How many relation locks the statement takes, table by table: the table, its partitions and its indexes. The planner locks every index of every table it plans, used or not, and every partition it cannot rule out while planning, such as when the partition key is compared with now().
• How many fall outside the fast path. A backend takes weak relation locks (AccessShareLock, RowShareLock, RowExclusiveLock) in fast-path slots of its own: 16 of them before PostgreSQL 18, and from 18 as many as max_locks_per_transaction allows, 64 by default. Locks that do not fit go to the shared lock table. When many sessions run such a statement at once, they contend for it (wait event LWLock:LockManager) and slow each other down. The cure is to take fewer locks: drop the indexes nothing uses, or let the planner rule out partitions.
• Indexes nothing uses: locked by every run, not used by this plan, not scanned since the statistics were reset, and not enforcing a constraint. Replicas count their own scans, so check theirs before you drop one.
• What would wait for these locks: the commands whose locks conflict with the statement’s, such as ALTER TABLE on its tables, or REINDEX of any index it locks, including those the plan does not use. While such a command waits for a long statement, every later run of the statement queues up behind it. That is why the report recommends a lock_timeout for schema changes.
• Other sessions’ locks that conflict right now, such as a migration that is already waiting behind statements like this one.
• What the statement waited on as it ran. The second connection samples pg_stat_activity every 10 ms while EXPLAIN ANALYZE runs, for the backend and its parallel workers. If the statement waited for another session’s lock, the report says for how long and who held it, because that time belongs to the other session, not to the plan.

With --no-analyze, you see the locks that planning takes; EXPLAIN ANALYZE adds those of running.

Statements with parameters

With --params or --bind, the report shows the locks of one execution of the generic plan and of one custom plan, with the parameters held at their typical values. This matters for partitioned tables. PostgreSQL does not plan a cached generic plan again: it locks every partition in it before run-time pruning drops the ones the values rule out. A custom plan locks only the partitions the planner keeps. So as partitions pile up, every execution of the generic plan takes more and more locks.

To see this, ExplainSQL prepares the statement and makes its plan in one transaction, then executes it in a second one, whose locks it reads. Without --measure, the plans do not run, so the generic plan’s count leaves out the indexes its scans would open.

Runs that waited

With --locks, --measure or --prove, the second connection also watches measured runs. A run that waited for another session’s lock is run again, up to twice, and ExplainSQL says so, so that a lock wait is never mistaken for a slow plan.

Safety

--locks only reads: pg_lock_status(), pg_locks, pg_stat_activity and the catalog, inside the transaction that is rolled back. Reading pg_locks takes the lock manager’s internal locks for a moment, twice per run. The second connection uses the same settings as the first.

What a write costs

An UPDATE that touches one row looks cheap in its plan. But if it is not a HOT update, it also writes a new entry into every index of the table, with the WAL that comes with each one, and leaves dead index entries for VACUUM to clean up. Do that a few thousand times a second and it adds up, on the primary, on every replica, and in your backups. The plan does not tell you any of this. ExplainSQL does.

explainsql -d shop -c "UPDATE orders SET status = 'shipped' WHERE id = 42" --allow-dml --print
explainsql -d shop -f update.sql --allow-dml --allow-ddl --prove --print

In the viewer, press W once the measured plan is in.

How it works

With --allow-dml, a statement that writes runs inside the transaction that is rolled back. Just before the rollback, ExplainSQL reads what it wrote from the transaction’s own counters (pg_stat_xact_user_tables), and the WAL it wrote from EXPLAIN (ANALYZE, WAL). Then everything is rolled back as usual.

Writes
  1 row updated in orders, not HOT: 2 index entries and 209 B of WAL per row.
  orders  1 updated (0 HOT); 2 index entries in 2 indexes
  WAL: 3 records, 209 B.

  LOW     The update of orders was not HOT: the statement sets created_at (orders_created_at_idx),
          which an index refers to, so when the value changes, each such update writes a new entry
          in every index of the table: 2 index entries per row.

What the report tells you

• The rows each table got, including those written by triggers and foreign-key cascades.
• Whether updates were HOT. An update is HOT (a “heap-only tuple”) when the new version of the row fits on the same page as the old one, and no index refers to a column whose value changed. Then no index gets a new entry at all. Otherwise every index of the table gets one, for every row, plus the WAL for each.
• What kept them from being HOT. The columns the statement sets (in UPDATE … SET, INSERT … ON CONFLICT DO UPDATE and MERGE) that an index refers to, whether in its keys, its INCLUDE list, its expressions or its predicate. From PostgreSQL 16, BRIN indexes do not count. If such an index has not been scanned since the statistics were reset, the finding becomes medium: dropping it would let these updates be HOT. If no index refers to any column the statement sets, the problem is room on the page: the report says how many new versions went to another page (from PostgreSQL 16) and shows the table’s fillfactor, which you can lower to leave room.
• Index entries and WAL per row. From PostgreSQL 13, ExplainSQL adds WAL to EXPLAIN ANALYZE for statements that write. The first change to a page after a checkpoint writes the whole page to WAL (a full-page image). When those images are most of the records, the report says so, because running the statement again soon after writes far less.

The proof

Like an index suggestion, the claim “this index is costing you HOT updates” can be tested. With --prove --allow-ddl, ExplainSQL drops the indexes that kept the updates from being HOT inside a transaction, runs the statement again there, reads what it wrote, and rolls back, which brings the indexes back:

Without orders_created_at_idx (dropped in a transaction that was rolled back): 1 of 1 update HOT,
no index entries, 81 B of WAL per row.

It leaves out indexes that enforce a constraint, and partitions’ indexes that belong to an index of the partitioned table. Dropping an index locks its table against both reads and writes until the rollback, so ExplainSQL gives up if it waits more than 2 seconds for that lock. As with every --allow-ddl test, use it on development or staging databases.

If the updates are still not HOT without those indexes, the report says why: the pages had no room for the new versions, or a column changed that another index refers to.

Limits

The writes are read for a statement run as it is, not under --params. In the JSON report, they are under writes.

The costliest statements

Sometimes you do not have a slow query yet, just a database that feels busy. pg_stat_statements already knows which statements cost the most. explainsql top turns that list into a starting point: pick a statement and see its plan, without copying anything around.

explainsql top -d shop
explainsql top -d shop --limit 50 --print
explainsql top -d shop --format json > statements.json

The list

top lists the statements that took the most execution time in the current database:

The 5 costliest statements in app@db.internal:5432/shop, PostgreSQL 16.15, by total execution time

#     Total   Share  Calls      Mean   Pages  Temp  Statement
1  131.1 ms     63%      5   26.2 ms  13,130        select c.name, count(*) from customers c join o…
2   74.3 ms     36%      5   14.9 ms  12,085        select count(*) from orders where customer_id =…
3  0.513 ms   0.25%      5  0.103 ms      35        select * from orders where status=$1 limit $2

For each one: its total time and share of all statements’ time, the number of calls, the mean time, pages read from the cache or from disk, and pages written to temporary files.

Picking a statement

In a terminal, the list is interactive:

• Enter opens the statement’s plan in the viewer, estimated: nothing runs. pg_stat_statements replaces the constants of a statement with $1, $2 and so on. From PostgreSQL 16, such a statement gets its generic plan, the plan made for any value (EXPLAIN (GENERIC_PLAN)).
• p tries values for its parameters, exactly as --params does, and shows the report. Before PostgreSQL 16, Enter does this too, since there is no other way to plan a statement with parameters.
• q goes back from the viewer to the list, and quits from the list.

Only with --measure do the plans actually run, in a transaction that is rolled back, as in connected mode. --allow-dml lets it measure statements that write, too.

Outside a terminal, or with --print, the list is printed as text, Markdown or JSON (--format).

Statements it cannot plan

The list marks those, and says why:

• commands without a plan, such as VACUUM, SET or EXPLAIN, ExplainSQL’s own included;
• other users’ statements, whose text pg_stat_statements shows only to superusers and members of pg_read_all_stats;
• texts cut short at track_activity_query_size bytes (1024 by default).

Requirements

pg_stat_statements must be loaded when the server starts and created in the database:

shared_preload_libraries = 'pg_stat_statements'    # in postgresql.conf, then restart

CREATE EXTENSION pg_stat_statements;

ExplainSQL tells you which of the two is missing. The list holds the current database’s statements. From PostgreSQL 14, it holds only those the application sent: a statement run inside a function counts in the call to that function. Reading the list happens in a READ ONLY transaction that is rolled back.

Limits

The text pg_stat_statements keeps does not always parse again. A constant written with its type, such as timestamptz '2026-01-01', becomes timestamptz $1, which PostgreSQL refuses. Planning such a statement shows the server’s error; put the constant back and run the statement with explainsql -d … -c.

Plan changes in server logs

“It was fast yesterday” is very often a plan that changed: after an ANALYZE, as the data grew, when a prepared statement switched to its generic plan, or after an upgrade. With auto_explain, the server logs the plans it actually ran. explainsql logs reads them and tells you, for each statement, which plans it got, when its plan changed, what changed, and what it cost.

explainsql logs /var/log/postgresql/postgresql-16-main.log
explainsql logs postgresql.json --changed --since 24h
explainsql logs postgresql.csv --query OrderController --format md > incident.md
explainsql logs postgresql.log --trace 4bf92f3577b34da6a3ce929d0e0e4736

What you get

Statements whose plan changed come first, the costliest change first, where the cost of a change is the extra time the new plan added over all the runs it had. Here is one from a real session:

CHANGED      SELECT id, status, amount FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT ?
             prepared as latest · query id -1243494815637630641 · 16 runs, 468.2 ms in all
             plan 1  Seq Scan on orders · 10 runs, median 12.2 ms
             plan 2  Index Scan Backward using orders_created_at_idx on orders · the generic plan ·
             6 runs, median 57.9 ms
  06:35:13.659 UTC, line 264
             plan 1 → plan 2 after 5 runs: the generic plan, with $1 = '777', $2 = '10'; median 13.2
             ms → 58.7 ms (4.4× slower)
             Worse: pages 2,417 → 186,053 (77× more), estimated cost 4917 → 1517 (3.2× cheaper). Seq
             Scan on orders became Index Scan Backward using orders_created_at_idx on orders. 1
             other change in the plan.
             REMOVED Sort removed
             → PostgreSQL switched to the generic plan after five executions. With the statement in
             a file, explainsql -d DATABASE -f FILE --params --measure shows which values the
             generic plan suits and what to do; …

For each statement:

• Its plans, each with how its tables are read, how many runs it had and their median duration.
• Each change of plan: when it happened and on which line of the log, after how many runs, whether in another session, the median duration before and after, how the new plan compares (pages first, as explainsql diff would say), and what changed.

A statement whose plans go back and forth many times is marked ALTERNATING, which often means a plan that depends on the parameter values.

Generic plans. A plan that keeps a prepared statement’s parameters ($1) is its generic plan. The report says when a statement switched to one, with the values it ran with, and gives you the --params and --bind command that tests exactly those values.

Reading the logs

• Formats. Any of the server’s formats: stderr with any log_line_prefix, csvlog or jsonlog. Plans can be logged in text or JSON, and several files can be read together. - reads standard input.
• What each entry says. From jsonlog and csvlog records, and from the common stderr prefixes (%m [%p] %u@%d, or user=%u,db=%d,app=%a), ExplainSQL takes the time, the process, the user, the database and the application. It also reads the duration, the query text and, from PostgreSQL 16, the parameter values a prepared statement ran with.
• Telling statements apart. A statement is known by its query identifier, which plans carry when compute_query_id is on and auto_explain logs with log_verbose. Without one, it is known by its text, with comments, literal values and parameters left out. A prepared statement is known by its query, not by the PREPARE around it.

sqlcommenter tags

Tags in a statement’s comment, such as /*controller='OrderController',action='latest',traceparent='00-…'*/, say where in the application the statement comes from. sqlcommenter integrations for Spring and Hibernate, Django, Rails and others add them, OpenTelemetry’s among them. The report lists each statement’s tags, and the filters can use them.

Filters

The report comes as text, Markdown (--format md, good for an incident write-up) or JSON.

Setting up auto_explain

Load it for the whole server with shared_preload_libraries = 'auto_explain', or for one session with LOAD 'auto_explain'. Then set:

• auto_explain.log_min_duration to the duration from which to log, 0 for every statement;
• auto_explain.log_analyze, log_buffers and log_settings to on;
• auto_explain.log_verbose to on, together with compute_query_id = on, for the query identifier;
• auto_explain.log_format to whichever you like.

A word of caution: with log_analyze, every statement is instrumented, whether it ends up logged or not, and that slows it down. On a busy server, set auto_explain.log_timing = off, or instrument only a sample of statements with auto_explain.sample_rate.

N+1 loops in requests

An ORM that loads related rows one parent at a time runs the same statement again and again in a single request, with a different value each time: a customer’s orders, then the items of each order, one order at a time. Each run is fast, so its plan looks fine, and so does any list of statements sorted by mean time. The cost only shows when you look per request, and that is exactly what explainsql requests does. It groups the statements in your server logs into requests, finds these loops, and writes the batched statement that does the work of all the runs at once.

explainsql requests /var/log/postgresql/postgresql-16-main.log
explainsql requests postgresql.json -d shop               # and measure each batched statement
explainsql requests postgresql.csv --min-runs 10 --format md > n-plus-one.md

What you get

Loops come first, those with the most runs first. Here is a real one, from a Spring application:

LOOP    SELECT id, product_id, quantity FROM order_items WHERE order_id = ?
        5 runs in each of 2 requests, 10 in all, 224.1 ms in the database; $1 changed from run to
        run (order_id).
        After SELECT id, status, created_at FROM orders WHERE customer_id = ? ORDER BY created_at
        DESC LIMIT ?
        In the request of OrderController#latest, trace 4bf92f3577b34da6a3ce929d0e0e4736, at
        2026-10-06 16:04:12.325 UTC: 9 statements, 146.8 ms in the database:
           1× SELECT id, status, created_at FROM orders WHERE customer_id = ? ORDER…    32.4 ms
           5× SELECT id, product_id, quantity FROM order_items WHERE order_id = ?      113.6 ms ◀
           3× SELECT id, name, price FROM products WHERE id = ?                        0.730 ms
        Batched:
          SELECT id, product_id, quantity FROM order_items WHERE order_id = ANY($1)
        Then select order_id too, to tell which rows go with which value.
        → JPA: JOIN FETCH or an @EntityGraph on the association, or @BatchSize on it
        (hibernate.default_batch_fetch_size for all).

A statement that ran --min-runs times or more (3 by default) in one request is a loop. It is a LOOP when its values changed from run to run, and a REPEAT when they were the same every time; a repeat is best fixed by reading the value once per request and keeping it. For each loop, the report shows:

• how many runs it had in how many requests, and their time in the database: parse, bind and execute together;
• the parameter that changed, and the column it is compared with;
• the statement just before the loop, which is often the one that read the parents;
• the request it looped most in, statement by statement;
• the batched statement:
  • col = ANY($1) in place of col = $1 or col IN ($1), when that comparison is a plain term of the statement’s own WHERE. This is what an ORM’s batch fetching sends.
  • When each value must keep its own rows, because of a LIMIT, an aggregate, a GROUP BY, a DISTINCT or a window function, or when the value is cast or computed, the statement goes into a LATERAL subquery over unnest($1), so that each value keeps its own LIMIT or count.
  • An INSERT per row is not rewritten; the report tells you how to send the rows together instead. An UPDATE or DELETE is rewritten only with = ANY.
  • A loop whose log has no values for its parameters is shown, but not batched.
• what to change in the application: JPA (JOIN FETCH, @EntityGraph, @BatchSize), Django (select_related, prefetch_related) or Rails (includes). When the statements carry sqlcommenter’s framework tag, only that framework’s fix is shown.

Measuring the batched statement

With -d, ExplainSQL runs each loop’s batched statement with all the values from the request it looped most in, and the runs one by one (at most 20 of them, scaled up to all). Every run is prepared the way the application ran it, and rolled back, READ ONLY unless --allow-dml. The batched statement runs first, after a warm-up run, so both sides find the data in the cache.

The report then compares their time and pages, says when the batched statement reads its tables differently (a large array can turn index scans into a sequential scan or a hash join), and adds the network round trips: the median time of a SELECT 1 from your machine, once for the batched statement and once per run. It also names the foreign key behind the loop, from the column in the generic plan: the rows that reference one parent (a collection, @OneToMany), or the parent of each row (@ManyToOne).

Measuring needs PostgreSQL 12 or later. --runs takes the median of several runs of the batched statement, and --limit sets how many loops are shown and measured (10 by default).

Logging the statements

The server must log every statement of the requests, with its duration:

• On a staging server, set log_min_duration_statement = 0. Statements that the driver prepares (the extended query protocol, which JDBC, psycopg 3 and most drivers use) are logged with their values in a DETAIL: parameters: line.
• On production, log_transaction_sample_rate (PostgreSQL 12 and later) logs a sample of whole transactions, which is exactly the unit you need.
• log_statement = all with log_duration = on works too, and so do logs with auto_explain entries at auto_explain.log_min_duration = 0.

Logs can be stderr with any log_line_prefix, csvlog or jsonlog, and several files can be read together.

How statements are grouped into requests

Statements go together:

1. by the trace id of their sqlcommenter traceparent tag, such as /*controller='OrderController',action='latest',traceparent='00-4bf9…-00f0…-01'*/, which sqlcommenter and OpenTelemetry integrations for Spring and Hibernate, Django, Rails and others add;
2. otherwise, by the transaction they ran in, within their session: %v in log_line_prefix, or the field in jsonlog and csvlog;
3. otherwise, by their session (%c, or the process %p), split wherever it sat idle for longer than --gap (50 ms by default).

For the second and third, add %c %v to log_line_prefix, for instance '%m [%p] %q%u@%d %c %v '. Behind a connection pool, statements outside a transaction and without a trace can only be grouped by idle time, which may put two requests together.

Privacy

The reports leave out the values the statements ran with, except those written into a statement’s text. Keep in mind that logging every statement with its values writes your application’s data to the log: keep such logs wherever that data is allowed to be.

Compare two plans

A plan changed after you added an index, refreshed statistics, upgraded PostgreSQL or rewrote the query. Reading two big plans side by side to see what moved is tedious and error-prone. explainsql diff does it for you, node by node.

explainsql diff before.json after.json
explainsql diff plans.txt                     # both plans in one input
explainsql diff before.json after.txt --format md

The two plans can come in any form ExplainSQL reads, and they do not have to match: JSON against text is fine. A single input can also hold both, one after the other: two plans pasted one below the other (a label such as After: between them is ignored), a JSON array or two JSON documents, two Markdown code fences, two psql results, or two auto_explain entries from a log.

The report

It opens with one sentence: how the second plan compares, pages first, then time (or the estimated cost when the plans were not run), and its main change. Then come the changes, the most significant first:

Last comes the plan after, with changed nodes marked ~ and new ones +, followed by the nodes only the plan before had.

Text is the default; --format md is ready for a pull request or an issue, and --format json holds the full diff with every node of both plans.

How nodes are matched

Nodes are matched by the work they do, not by their position in the tree. A scan is found again by the relation it reads, a join by the relations it combines, and any other node by its kind and the relations below it. Partitions that PostgreSQL named differently, as different versions do, still match. So do plans that grew or lost a node in the middle.

Plan shapes

Every plan has a shape: 16 hexadecimal digits that stand for its nodes, what they read and how, leaving out numbers, literal values and aliases. Two plans with the same shape are the same plan, whatever the parameters, the data or the cache, and whether they were printed as JSON or text. The diff shows both shapes, and explainsql check and explainsql logs use them to notice when a plan changes.

In the viewer

In connected mode, after r or e runs the statement again, the status line compares the new run with the previous one the same way.

Check plans in CI

Plans regress quietly. A new column in a WHERE, a dropped index in a migration, a rewritten ORM query: the tests still pass, and the slowdown shows up in production a week later. explainsql check makes plans part of continuous integration, so that a plan that got worse fails the build, like a failing test.

explainsql check -d "$DATABASE_URL" queries/ --update      # lock the plans as they are; commit explainsql.lock
explainsql check -d "$DATABASE_URL" queries/               # in CI: fail when a plan got worse
explainsql check -d "$DATABASE_URL" queries/ --fail-on high --prove --format md > comment.md
explainsql check plans/ --format sarif > explainsql.sarif  # captured plans, no database

How it works

You keep the statements you care about in files, one statement per .sql file, anywhere in the repository. Each is checked twice: against its findings, and against the plan locked for it in explainsql.lock.

What it reads. With -d, SQL files (directories are searched for *.sql), each run as in connected mode: in a transaction that is rolled back, READ ONLY unless --allow-dml, with EXPLAIN ANALYZE, or with EXPLAIN alone under --no-analyze. Without -d, plan files in any form ExplainSQL reads (directories are searched for *.json and *.txt).

The lock file. --update writes each plan to explainsql.lock (--lock FILE for another name), under its path relative to the lock file’s directory: its shape, its pages, its estimated cost, and the plan itself, with a JSON plan stored as JSON so that a change reads well in code review. Plans not checked in that run are kept as they are. Commit the file, and run --update again whenever you accept a change.

When a plan fails.

• It is worse than its locked plan by pages, by temporary files, or, when neither plan was run, by the planner’s estimated cost, by more than 10%. Time alone never fails a plan: on a shared CI runner it changes from one run to the next for the same work, so a change in time is reported as a note.
• With --fail-on SEVERITY, a finding at least that severe fails it, locked or not.
• With --strict, a plan whose shape changed fails even when it is not worse. The plan becomes a contract, changed on purpose with --update.

A plan that is not in the lock file yet is new, and fails only on its findings.

What it says. For each plan that failed: why, what changed in the plan (as explainsql diff tells it), and the suggested fix. With --prove and HypoPG installed in the database, each suggested index is tested and reported before and after.

Exit codes. 0 when every plan passed, 1 when at least one failed, and 2 when the check could not run. A file that cannot be read or run is reported on standard error and makes the exit code 2, after the other files are checked.

Report formats

• --format text (the default): one line per plan with its shape, then why it failed, notes and fixes.
• --format md: a pull request comment, with the plans in a table and the diff of each one that failed or changed folded below. It starts with the line <!-- explainsql check -->, which is invisible in a rendered comment, and stays under GitHub’s size limit for comments: when the plans do not fit, those that failed come first, then those that changed, and the rest are counted.
• --format sarif: SARIF 2.1.0 for code scanning. Each finding, and each plan worse than its lock, is a result on its file: an error when it fails the plan, otherwise a warning or a note by severity.
• --format json: every check with its findings and advice, for other programs.

--sarif FILE writes the SARIF report as well, alongside a report in another format, so one run gives you both.

The GitHub Action

The repository is also a GitHub Action. It installs ExplainSQL, runs explainsql check, and writes the report on the pull request as a comment. Later runs update that comment in place instead of adding new ones: a check that fails posts or updates it, and a check that passes edits an existing comment to say so, without posting a new one. The report also goes to the job summary, and the job fails when the check fails.

on: pull_request
permissions:
  contents: read
  pull-requests: write        # for the comment
  security-events: write      # only with upload-sarif
jobs:
  plans:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:17
        env:
          POSTGRES_PASSWORD: postgres
        ports: ["5432:5432"]
        options: --health-cmd pg_isready --health-interval 5s --health-retries 10
    steps:
      - uses: actions/checkout@v5
      - run: psql "$DATABASE_URL" -f schema.sql   # the tables, and data shaped like production's
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost:5432/postgres
      - uses: onplt/explain-sql@v0.3.0
        with:
          paths: queries/
          database-url: postgresql://postgres:postgres@localhost:5432/postgres
          fail-on: high
          upload-sarif: true

Without database-url, paths are captured plan files and no database is needed.

The outputs are result (passed, failed or error), exit-code (0, 1 or 2), and the paths of the reports, report (Markdown) and sarif. The action runs on Linux and macOS runners and needs ExplainSQL 0.2.0 or later.

A pull request from a fork gets no comment, because its token cannot write one, and pull_request_target, whose token can, would run the fork’s code with your repository’s secrets. Its report is still in the job summary.

Without the action

If you prefer plain steps, or another CI system, run the binary yourself:

- name: Check the plans
  run: explainsql check -d "$DATABASE_URL" queries/ --fail-on high --format sarif > explainsql.sarif
- name: Show them in code scanning
  if: always()
  uses: github/codeql-action/upload-sarif@v3
  with:
    sarif_file: explainsql.sarif

For a single plan, the main command takes --fail-on too: explainsql --print --fail-on high plan.json exits with 1 when a finding is at least that severe.

Make the data realistic

A plan from a database with a handful of rows says very little: the planner reads tiny tables whole, whatever indexes you have. Check against data shaped like production’s, even if it is generated. From PostgreSQL 18 you can also restore production’s statistics into the CI database (pg_restore_relation_stats, pg_restore_attribute_stats), although the planner still sees the actual size of each table on disk.

Share a plan

A plan says a lot about your database: the names of its tables, columns and indexes, and the values your statement looked for, which may be customer emails or order numbers. Before a plan goes into a bug report, an issue or a chat, explainsql anonymize replaces all of that, while keeping everything needed to analyze it.

explainsql anonymize plan.json > shared.json
pbpaste | explainsql anonymize | pbcopy
explainsql anonymize plan.txt --map names.json   # and keep a note of what each name became

What changes

• Names of tables, indexes, CTEs, aliases, schemas, columns, constraints and triggers become table_a, index_a, cte_a, alias_a, schema_a, column_a, constraint_a and trigger_a, then _b, _c and so on.
• Names that differ only in their numbers, as partitions do, stay alike: orders_2025_01 and orders_2025_02 become table_b_1 and table_b_2, so the viewer still folds them and explainsql diff still matches them.
• String literals become 'value_a', 'value_b' and so on, keeping a LIKE pattern’s % at either end. Numbers in conditions become other numbers of the same form.
• The statement’s text (Query Text) is anonymized the same way, and its comments are dropped.

The same name or value gets the same replacement everywhere, in every plan of the input, so the anonymized plan stays consistent.

What stays

Node types, estimates, timings, buffers and every other figure, so the plan reads and analyzes exactly as before: it gets the same findings, and compares with another plan as the original does. Function and type names, keywords, $n parameters and system names (pg_catalog, public, pg_… relations, ctid, the triggers behind foreign keys) are kept too.

Input and output

It reads anything ExplainSQL reads, with every plan in it. The plans come out in the format they went in, JSON or text, without whatever surrounded them (psql’s table, log lines, a Markdown fence). When an input holds plans in both formats, each comes out in its own Markdown fence.

To stay on the safe side, a line or JSON property it does not recognize has every name and value in it replaced. And if the anonymized plans do not read back with the same nodes, nothing is printed at all.

Options

• --keep-names replaces only the literal values and keeps the names, for when the schema is not secret but the data is.
• --map FILE writes what each name and value became, as JSON, so that you can translate an answer about the anonymized plan back. That file holds the originals, so keep it to yourself.

Command-line reference

Everything ExplainSQL accepts, in one place. explainsql --help and explainsql COMMAND --help print the same information in your terminal.

explainsql [OPTIONS] [FILE]       read a plan, or run a statement with -d
explainsql diff BEFORE [AFTER]    compare two plans
explainsql check PATHS…           check plans in CI
explainsql logs FILES…            plan changes in auto_explain logs
explainsql top -d DATABASE        the costliest statements from pg_stat_statements
explainsql requests FILES…        N+1 loops in statement logs
explainsql anonymize [FILE]       a plan with names and values replaced

explainsql

Reads a plan from FILE, or from standard input when FILE is missing or -, and opens it in the viewer or prints a report. With -d and -f or -c, it runs a statement itself instead (connected mode).

Reading and output

Connected mode

explainsql diff

explainsql diff [OPTIONS] BEFORE [AFTER]

Compares two plans of the same statement, node by node. Without AFTER, BEFORE must hold both plans, one after the other.

explainsql check

explainsql check [OPTIONS] PATHS…

Checks plans in CI: each against its findings and against the plan locked for it. Exits with 0 when every plan passed, 1 when one failed, 2 on an error.

explainsql logs

explainsql logs [OPTIONS] FILES…

Reads auto_explain plans from server logs (stderr, csvlog or jsonlog; - for standard input) and reports when each statement’s plan changed.

explainsql top

explainsql top [OPTIONS]

Lists a database’s costliest statements from pg_stat_statements, and plans the one you pick.

In the list: Enter plans the selected statement, p tries values for its parameters, q goes back or quits.

explainsql requests

explainsql requests [OPTIONS] FILES…

Groups the statements of server logs into requests and finds the loops (N+1), with the batched statement for each.

explainsql anonymize

explainsql anonymize [OPTIONS] [FILE]

Prints the plans of FILE (or standard input) with names and literal values replaced.

Environment variables

Files

Exit codes

Troubleshooting

Answers to the questions people run into most. If yours is not here, please open an issue.

Reading plans

“no EXPLAIN plan found in the input”

ExplainSQL could not find a plan in what you gave it. It reads JSON and text plans, including inside psql output, log lines, Markdown fences and copied result cells, but not the YAML or XML formats. Check that the input really holds the output of EXPLAIN, not just the query. --debug-parse shows what the parser made of it.

The plan has no times, and the findings talk about costs.

The plan was captured without ANALYZE, or with TIMING OFF. ExplainSQL then works from estimates and pages, and never presents an estimate as a measurement. Capture it again with EXPLAIN (ANALYZE, BUFFERS), or let connected mode run it for you.

The times in the viewer do not match what EXPLAIN printed.

EXPLAIN prints each node’s time including its children, averaged per loop. The viewer shows each node’s own time, for all its loops, with parallel workers, CTEs and InitPlans accounted for. Press x to see times including children. The viewer chapter explains the difference.

Times change every time I run the statement.

They do, because they depend on what is in the cache and how busy the server is. That is why every comparison in ExplainSQL looks at pages first, and why measured runs are preceded by a warm-up run. Use --runs 5 to compare medians.

A warning says some lines were not understood.

ExplainSQL keeps going when a plan has something it does not recognize, such as a property from a newer PostgreSQL version or an extension. The rest of the analysis is still valid. If it looks like something it should understand, please send the plan in an issue.

The viewer

It prints a report instead of opening the viewer.

The viewer opens only when standard output is a terminal and TERM is not dumb. Redirected output, pipes to another command, and CI logs get a report.

The colors look wrong.

Use --theme light on a light background. If the terminal shows odd colors, it may claim more colors than it supports: check COLORTERM and TERM. NO_COLOR=1 turns colors off.

c does not copy anything.

c uses the OSC 52 escape sequence, which most modern terminals support, sometimes behind a setting. In tmux, enable it with set -g set-clipboard on.

Connected mode

It cannot connect, but psql can.

ExplainSQL reads the same settings as psql: -d, the service file, the PG* variables and ~/.pgpass. Two things differ in practice. ~/.pgpass is ignored when other users can read it (chmod 600 ~/.pgpass), exactly as libpq does. And with sslmode=verify-ca or verify-full, the server’s certificate must be trusted by sslrootcert or the system’s trust store.

“the statement modifies data”.

Statements that write or lock rows run only with --allow-dml. They are still rolled back. See what ExplainSQL promises before you use it on a database that matters.

My DDL, or two statements at once, are refused.

Connected mode runs one statement at a time, of a kind EXPLAIN accepts: SELECT, WITH, VALUES, TABLE, INSERT, UPDATE, DELETE or MERGE.

A statement with $1 will not run.

A statement with placeholders cannot run without values. Add --params to try values from your data, or --bind 1=42 to give one. See statements with parameters.

t says to install HypoPG or use --allow-ddl.

Testing an index needs either the HypoPG extension in the database (CREATE EXTENSION hypopg), or permission to build the index in a rolled-back transaction, which you give by starting ExplainSQL with --allow-ddl.

Building the test index gave up.

The build waits at most 2 seconds for its lock, so that it never queues behind other sessions and blocks them in turn. Something else was using the table. Try again later, or on a quieter database.

A run was repeated, with a note about a lock.

With --locks, --measure or --prove, a second connection watches each measured run. If the run waited for another session’s lock, its time says nothing about the plan, so ExplainSQL runs it again, up to twice.

Does ExplainSQL change my database?

Not the data: every run is rolled back. But a rollback cannot undo everything. Sequences keep their new values, dblink and foreign tables reach other systems, rolled-back rows leave dead tuples until the next VACUUM, and while a statement runs with --allow-dml or --allow-ddl, it holds its locks. Pointing it at production with only read-only statements is safe; the write and DDL options are meant for development and staging.

The other commands

explainsql top says pg_stat_statements is missing.

It needs to be both loaded at server start (shared_preload_libraries = 'pg_stat_statements', then a restart) and created in the database (CREATE EXTENSION pg_stat_statements). The message says which part is missing.

explainsql top cannot plan some statements.

Commands like VACUUM and SET have no plan. Other users’ statements are hidden unless you are a superuser or a member of pg_read_all_stats. Long statements are cut at track_activity_query_size. And a constant written with its type, such as timestamptz '2026-01-01', becomes timestamptz $1, which PostgreSQL refuses to plan; put the constant back and use explainsql -d … -c.

explainsql logs finds no plans.

It reads auto_explain’s entries (duration: … ms plan:). Check that auto_explain is loaded and that auto_explain.log_min_duration is low enough for your statements to be logged. Statements are told apart best with compute_query_id = on and auto_explain.log_verbose = on.

explainsql requests puts several requests together, or finds none.

It needs to know where a request starts and ends. Add %c %v to log_line_prefix so that sessions and transactions show up in the log, or add sqlcommenter tags with a traceparent to your application. Behind a connection pool, statements outside a transaction and without a trace can only be grouped by idle time (--gap).

The GitHub Action did not comment on a pull request from a fork.

That is deliberate: a fork’s token cannot write comments, and the alternative, pull_request_target, would run the fork’s code with your secrets. The report is in the job summary.

Reporting a bug

The most useful bug report includes the plan. Please:

1. run explainsql --debug-parse on it, to check whether the parser read it as you expected;
2. anonymize it with explainsql anonymize plan.txt > shared.txt if it holds anything private;
3. attach it to an issue with explainsql --version and your PostgreSQL version.

Rule catalog

Rules turn plan data into findings. Every finding names its rule, the node it is about, a severity, the evidence that triggered it, and a suggested action. The bar for a rule is precision: a rule that is sometimes obviously wrong does more harm than a missing rule. So each rule also says when it deliberately stays silent.

The index advisor builds its CREATE INDEX suggestions on the findings of ES001, ES005, ES006 and ES009 (see “Index advisor” in ARCHITECTURE.md).

Each rule lives in its own file, crates/explainsql-core/src/rules/esNNN_*.rs, with its thresholds as named constants; they will become configurable. Every plan in the fixture corpus is checked against the rules its scenario expects, and no other rule may fire (see fixtures/README.md). Each rule has a page with its thresholds, when it stays silent, and an example from the corpus. To propose a new rule, see contributing.

Severity follows the share of the runtime involved: half or more is high, a fifth or more is medium, anything less is low. When the plan has no timing, the share is taken from buffers. Misestimates are low on their own and at least medium when they feed a join.

Shares are exclusive: the time spent in a node itself, as a fraction of the statement’s execution time. See “Metrics” in ARCHITECTURE.md for how parallel workers, CTEs, InitPlans and rounding are accounted for.

ES001: Selective sequential scan

• Signal: a Seq Scan (parallel or not) that keeps less than 5% of the rows it reads, on a table of at least 1,000 pages (or 50,000 rows per scan when the plan has no buffer counts), taking at least 10% of the runtime. Rows kept are those that pass the scan’s filter. When the scan is the inner side of a nested loop, they are the rows that pass the loop’s join filter.
• Evidence: rows kept of rows read, the filter, the table size in pages, loops, and the time in the node.
• Action: an index on the filtered columns. The finding says when the operator needs a trigram index (LIKE '%…'), text_pattern_ops (LIKE 'abc%') or GIN (@>, &&, @@). When the filter wraps the column in a cast or a function, an index on the column cannot help, and the action is to rewrite the condition or index the expression.
• Stays silent when: the table is small, the scan is cheap compared with the statement, a Limit, a semi or anti join, or a subquery can stop the scan early, or the filter ORs conditions on different columns (no single index serves it).

Example

The anti_join scenario of the test corpus, captured on PostgreSQL 16: NOT EXISTS subquery, planned as an anti join.

SELECT c.id
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'refunded');

Hash Right Anti Join  (cost=656.00..5580.54 rows=17987 width=4) (actual time=31.147..33.114 rows=19800 loops=1)
  Output: c.id
  Inner Unique: true
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=270 read=2353
  I/O Timings: shared read=4.088
  ->  Seq Scan on public.orders o  (cost=0.00..4917.00 rows=2013 width=4) (actual time=0.066..21.837 rows=2000 loops=1)
        Output: o.id, o.customer_id, o.status, o.created_at, o.amount, o.note
        Filter: (o.status = 'refunded'::text)
        Rows Removed by Filter: 198000
        Buffers: shared hit=64 read=2353
        I/O Timings: shared read=4.088
  ->  Hash  (cost=406.00..406.00 rows=20000 width=4) (actual time=7.994..7.996 rows=20000 loops=1)
        Output: c.id
        Buckets: 32768  Batches: 1  Memory Usage: 960kB
        Buffers: shared hit=206
        ->  Seq Scan on public.customers c  (cost=0.00..406.00 rows=20000 width=4) (actual time=0.008..3.140 rows=20000 loops=1)
              Output: c.id
              Buffers: shared hit=206
Settings: max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=239
Planning Time: 1.089 ms
Execution Time: 33.808 ms

explainsql reports:

HIGH  ES001 Selective sequential scan
Seq Scan on orders o reads 200,000 rows to keep 2,000.
Rows kept: 2,000 of 200,000 (1.0%)
Filter: (o.status = 'refunded'::text)
Table size: 2,417 pages (18.9 MB)
Time in the node: 21.8 ms (65% of the runtime)
→ An index on orders (status) would let PostgreSQL read only the matching rows.

All rules

ES002: Row misestimate

• Signal: actual and estimated rows per loop differ by 10× or more, and the error starts at this node rather than being passed up from a child. Each count is taken as at least one row.

• Evidence: estimated and actual rows, loops, and the join that consumes the node.

• Action: depends on the cause:

  • stale statistics: ANALYZE the table;
  • correlated columns in the condition: CREATE STATISTICS on them;
  • a single column: a higher statistics target;
  • a cast or a function of a column, which has no statistics: rewrite the condition, or CREATE STATISTICS on the expression (PostgreSQL 14 and later);
  • a comparison with a value known only at run time ($1, an InitPlan’s result): a default guess that ANALYZE cannot change.

• Stays silent when:

  • both counts are small: under 100 rows per loop, and under 10,000 over all loops;
  • the node returned fewer rows than estimated but a node above may have stopped it early;
  • the node passes its input’s rows through (Sort, Hash, Materialize, Memoize, Gather) or reports none (bitmap nodes);
  • the node is a recursive CTE’s Recursive Union, whose depth the planner cannot know.

  A CTE Scan inherits the error of the CTE it reads.

Example

The misestimate_stale_stats scenario of the test corpus, captured on PostgreSQL 16: statistics predate 50,000 inserted ‘active’ rows, so the estimate is off by orders of magnitude.

SELECT count(*) FROM shipments WHERE state = 'active';

Aggregate  (cost=1743.00..1743.01 rows=1 width=8) (actual time=9.397..9.399 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=589
  ->  Seq Scan on public.shipments  (cost=0.00..1743.00 rows=1 width=0) (actual time=2.647..7.317 rows=50000 loops=1)
        Output: id, state, weight
        Filter: (shipments.state = 'active'::text)
        Rows Removed by Filter: 50000
        Buffers: shared hit=589
Settings: max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=63
Planning Time: 0.260 ms
Execution Time: 9.452 ms

explainsql reports:

LOW  ES002 Row misestimate
Seq Scan on shipments returned 50,000 rows where the planner expected 1: 50,000× more.
Estimated rows: 1
Actual rows: 50,000
→ Run ANALYZE shipments: its statistics may be out of date. If the estimate stays off, raise the statistics target of state (ALTER TABLE shipments ALTER COLUMN … SET STATISTICS).

All rules

ES003: Sort spilled to disk

• Signal: a Sort or Incremental Sort, in the leader or in a parallel worker, whose method is external merge or external sort.
• Evidence: sort method, disk space used, sort key, and the time in the node.
• Action: raise work_mem for the statement rather than for the server. The finding names a value: a power of two megabytes, about three times the space written to disk, set with SET LOCAL in the statement’s transaction. It also says what that value may take: each sort, hash and other operation that uses work_mem may take that much, in each process that runs it, in every session that runs the statement at the same time. Alternatively, avoid the sort with an index that matches the sort order.
• Stays silent when: the spill is under 10 MB and the sort takes less than 5% of the runtime.

Example

The sort_external_merge scenario of the test corpus, captured on PostgreSQL 16: sort that spills to disk because work_mem is tiny.

SELECT id, note FROM orders ORDER BY note;

Sort  (cost=38438.14..38938.14 rows=200000 width=37) (actual time=340.230..372.433 rows=200000 loops=1)
  Output: id, note
  Sort Key: orders.note
  Sort Method: external merge  Disk: 9272kB
  Buffers: shared hit=2130 read=290, temp read=4609 written=4921
  I/O Timings: shared read=1.647, temp read=7.540 write=8.700
  ->  Seq Scan on public.orders  (cost=0.00..4417.00 rows=200000 width=37) (actual time=0.014..22.127 rows=200000 loops=1)
        Output: id, note
        Buffers: shared hit=2127 read=290
        I/O Timings: shared read=1.647
Settings: work_mem = '64kB', max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=128
Planning Time: 0.319 ms
Execution Time: 379.494 ms

explainsql reports:

HIGH  ES003 Sort spilled to disk
Sort spilled 9.1 MB to disk.
Sort method: external merge
Disk used: 9.1 MB
Sort key: orders.note
Time in the node: 350.3 ms (92% of the runtime)
→ Raise work_mem so that the sort fits in memory. For this statement alone: SET LOCAL work_mem = '32MB' in its transaction. Every session that runs the statement at the same time may take that much. Or avoid the sort with an index that returns rows ordered by orders.note.

All rules

ES004: Hash or aggregate spilled to disk

• Signal: a Hash split into more than one batch, or a hashed aggregate that reports several batches or disk usage.
• Evidence: batches (and the planned number), peak memory, data written to disk.
• Action: raise work_mem or hash_mem_multiplier for the statement. The finding estimates the hash table’s full size and names a work_mem that holds it, set with SET LOCAL in the statement’s transaction, with what that may take across the plan’s operations, processes and concurrent sessions. It points to ES002 when the input of the hash was underestimated.

Example

The hash_aggregate_spill scenario of the test corpus, captured on PostgreSQL 16: hash aggregate that spills to disk because work_mem is tiny (PostgreSQL 13 and later).

SELECT customer_id, count(*), sum(amount) FROM orders GROUP BY customer_id;

HashAggregate  (cost=41354.50..47462.68 rows=19904 width=44) (actual time=71.500..192.878 rows=20000 loops=1)
  Output: customer_id, count(*), sum(amount)
  Group Key: orders.customer_id
  Planned Partitions: 4  Batches: 306  Memory Usage: 173kB  Disk Usage: 7448kB
  Buffers: shared hit=2417, temp read=2524 written=3257
  I/O Timings: temp read=4.012 write=8.228
  ->  Seq Scan on public.orders  (cost=0.00..4417.00 rows=200000 width=10) (actual time=0.013..15.582 rows=200000 loops=1)
        Output: id, customer_id, status, created_at, amount, note
        Buffers: shared hit=2417
Settings: work_mem = '64kB', enable_sort = 'off', max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=103
Planning Time: 0.536 ms
Execution Time: 194.424 ms

explainsql reports:

HIGH  ES004 Hash or aggregate spilled to disk
HashAggregate wrote 7.3 MB to disk because its hash table did not fit in work_mem.
Batches: 306
Memory used: 173 kB
Disk used: 7.3 MB
Time in the node: 177.3 ms (91% of the runtime)
→ Raise work_mem (or hash_mem_multiplier) so that the aggregate's hash table fits in memory. For this statement alone: SET LOCAL work_mem = '16MB' in its transaction. Every session that runs the statement at the same time may take that much.

All rules

ES005: Expensive nested-loop inner side

• Signal: a Nested Loop whose inner side runs at least twice. The work it repeats (everything but producing the outer rows) must take at least half of the loop’s time, and the loop at least 10% of the runtime. The inner side must also be a sequential scan, or a scan that removes at least ten times the rows it keeps.
• Evidence: inner loops, inner time per loop, the repeated work, and the rows removed per loop.
• Action: an index on the inner side’s join key, taken from the join filter or from the inner scan’s conditions on the outer side. The finding points to ES002 when the planner expected far fewer outer rows.
• Stays silent when: a Materialize or Memoize caches the inner side.

Example

The lateral_join_top_n scenario of the test corpus, captured on PostgreSQL 16: LATERAL top-3 per customer; without an index on customer_id, every loop walks the created_at index backwards and discards most of the rows it reads.

SELECT c.id, o.id, o.created_at
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, created_at
    FROM orders
    WHERE orders.customer_id = c.id
    ORDER BY created_at DESC
    LIMIT 3
) o
WHERE c.id <= 20;

Nested Loop  (cost=0.71..90994.50 rows=60 width=16) (actual time=1.612..312.552 rows=60 loops=1)
  Output: c.id, orders.id, orders.created_at
  Buffers: shared hit=1109606
  ->  Index Only Scan using customers_pkey on public.customers c  (cost=0.29..4.64 rows=20 width=4) (actual time=0.016..0.052 rows=20 loops=1)
        Output: c.id
        Index Cond: (c.id <= 20)
        Heap Fetches: 0
        Buffers: shared hit=3
  ->  Limit  (cost=0.42..4549.46 rows=3 width=12) (actual time=4.412..15.616 rows=3 loops=20)
        Output: orders.id, orders.created_at
        Buffers: shared hit=1109603
        ->  Index Scan Backward using orders_created_at_idx on public.orders  (cost=0.42..15163.90 rows=10 width=12) (actual time=4.410..15.612 rows=3 loops=20)
              Output: orders.id, orders.created_at
              Filter: (orders.customer_id = c.id)
              Rows Removed by Filter: 55201
              Buffers: shared hit=1109603
Settings: max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=216
Planning Time: 0.619 ms
Execution Time: 312.622 ms

explainsql reports:

HIGH  ES005 Expensive nested-loop inner side
Nested Loop repeats Index Scan using orders_created_at_idx on orders 20 times; that takes 100% of the loop's time.
Inner loops: 20
Inner side per loop: 15.6 ms
Repeated work: 312.5 ms of the loop's 312.6 ms, 312.3 ms of it in the inner scans
Rows removed per loop: 55,201 by the filter of Index Scan using orders_created_at_idx on orders
→ An index on orders (customer_id) would turn each of the 20 inner scans into an index lookup.

All rules

ES006: Index scan that filters most rows

• Signal: an Index Scan, Index Only Scan or Bitmap Heap Scan whose filter removes at least 90% of the rows found through the index. It must remove at least 100 rows per loop (or 1,000 over all loops), and the scan, including its index, must take at least 10% of the runtime.
• Evidence: the index, the index condition, the filter, and rows removed of rows found.
• Action: a composite index that covers the filtered columns as well as the index condition’s.

Example

The index_scan_filter scenario of the test corpus, captured on PostgreSQL 16: index range scan on created_at that discards most rows with a filter on status.

SELECT * FROM orders
WHERE created_at >= timestamptz '2024-06-01 00:00:00+00'
  AND created_at < timestamptz '2024-06-15 00:00:00+00'
  AND status = 'refunded';

Bitmap Heap Scan on public.orders  (cost=77.05..2671.77 rows=37 width=64) (actual time=0.254..2.127 rows=27 loops=1)
  Output: id, customer_id, status, created_at, amount, note
  Recheck Cond: ((orders.created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (orders.created_at < '2024-06-15 00:00:00+00'::timestamp with time zone))
  Filter: (orders.status = 'refunded'::text)
  Rows Removed by Filter: 3809
  Heap Blocks: exact=319
  Buffers: shared hit=332
  ->  Bitmap Index Scan on orders_created_at_idx  (cost=0.00..77.04 rows=3662 width=0) (actual time=0.181..0.182 rows=3836 loops=1)
        Index Cond: ((orders.created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (orders.created_at < '2024-06-15 00:00:00+00'::timestamp with time zone))
        Buffers: shared hit=13
Settings: max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=130
Planning Time: 0.423 ms
Execution Time: 2.173 ms

explainsql reports:

HIGH  ES006 Index scan that filters most rows
Bitmap Heap Scan on orders finds 3,836 rows through orders_created_at_idx and its filter throws away 3,809.
Index: orders_created_at_idx
Index condition: ((orders.created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (orders.created_at < '2024-06-15 00:00:00+00'::timestamp with time zone))
Filter: (orders.status = 'refunded'::text)
Rows removed by the filter: 3,809 of 3,836 found
→ A composite index on orders (status, created_at) would let the index do the filtering.

All rules

ES007: Index-only scan with many heap fetches

• Signal: an Index Only Scan with heap fetches amounting to at least 25% of the rows returned and at least 100 in total, taking at least 10% of the runtime.
• Evidence: heap fetches and rows returned.
• Action: VACUUM the table to update its visibility map, and check that autovacuum keeps up with how often the table changes.

Example

The index_only_scan_heap_fetches scenario of the test corpus, captured on PostgreSQL 16: index-only scan on a table whose visibility map is out of date, so most rows need a heap fetch.

SELECT page FROM page_views WHERE page BETWEEN 10 AND 19;

Index Only Scan using page_views_page_idx on public.page_views  (cost=0.29..275.45 rows=2158 width=4) (actual time=0.399..1.224 rows=2000 loops=1)
  Output: page
  Index Cond: ((page_views.page >= 10) AND (page_views.page <= 19))
  Heap Fetches: 2200
  Buffers: shared hit=2060
Settings: enable_bitmapscan = 'off'
Planning:
  Buffers: shared hit=81
Planning Time: 0.350 ms
Execution Time: 1.311 ms

explainsql reports:

HIGH  ES007 Index-only scan with many heap fetches
Index Only Scan using page_views_page_idx on page_views visited the table 2,200 times for 2,000 rows: the visibility map of page_views is out of date.
Heap fetches: 2,200
Rows returned: 2,000
Time in the node: 1.22 ms (93% of the runtime)
→ VACUUM page_views to update its visibility map, and check that autovacuum keeps up with how often the table changes.

All rules

ES008: Lossy bitmap or heavy recheck

• Signal: a Bitmap Heap Scan with lossy heap blocks, taking at least 5% of the runtime.
• Evidence: lossy and exact blocks, rows removed by the recheck.
• Action: raise work_mem so that the bitmap stays exact.
• Stays silent when: rows are rechecked without any lossy blocks. That comes from lossy operator classes (trigram indexes, for example), which work_mem does not help.

Example

The bitmap_lossy scenario of the test corpus, captured on PostgreSQL 16: bitmap heap scan over most of the table with a tiny work_mem, so the bitmap becomes lossy and rows are rechecked.

SELECT sum(amount) FROM orders
WHERE created_at >= timestamptz '2024-01-01 00:00:00+00'
  AND created_at < timestamptz '2025-07-01 00:00:00+00';

Aggregate  (cost=8663.35..8663.36 rows=1 width=32) (actual time=58.985..58.988 rows=1 loops=1)
  Output: sum(amount)
  Buffers: shared hit=2454
  ->  Bitmap Heap Scan on public.orders  (cost=3031.54..8288.93 rows=149768 width=6) (actual time=5.351..34.510 rows=149877 loops=1)
        Output: id, customer_id, status, created_at, amount, note
        Recheck Cond: ((orders.created_at >= '2024-01-01 00:00:00+00'::timestamp with time zone) AND (orders.created_at < '2025-07-01 00:00:00+00'::timestamp with time zone))
        Rows Removed by Index Recheck: 14472
        Heap Blocks: exact=532 lossy=1549
        Buffers: shared hit=2454
        ->  Bitmap Index Scan on orders_created_at_idx  (cost=0.00..2994.10 rows=149768 width=0) (actual time=5.263..5.264 rows=149877 loops=1)
              Index Cond: ((orders.created_at >= '2024-01-01 00:00:00+00'::timestamp with time zone) AND (orders.created_at < '2025-07-01 00:00:00+00'::timestamp with time zone))
              Buffers: shared hit=373
Settings: work_mem = '64kB', enable_seqscan = 'off', enable_indexscan = 'off', max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=98
Planning Time: 0.431 ms
Execution Time: 59.097 ms

explainsql reports:

HIGH  ES008 Lossy bitmap or heavy recheck
Bitmap Heap Scan on orders kept only page numbers for 1,549 of 2,081 pages, so it rechecked every row on them.
Heap blocks: 1,549 lossy, 532 exact
Rows removed by the recheck: 14,472
Time in the node: 29.2 ms (49% of the runtime)
→ Raise work_mem for this query so that the bitmap stays exact.

All rules

ES009: Slow foreign-key trigger

• Signal: a foreign-key action trigger on the referenced side that takes at least 20% of the execution time. That is a RI_ConstraintTrigger_a_… trigger, or a constraint trigger of a DELETE when the plan does not name it (text plans without VERBOSE).
• Evidence: trigger, constraint, calls, total time and time per call.
• Action: an index on the referencing columns of the child table.
• Stays silent when: the trigger is an INSERT’s check trigger (RI_ConstraintTrigger_c_…), which looks up the referenced table’s unique index.

Example

The delete_fk_trigger scenario of the test corpus, captured on PostgreSQL 16: DELETE on a referenced table; the foreign-key trigger scans the unindexed order_items.order_id for every deleted row.

DELETE FROM orders WHERE id > 199980;

Delete on public.orders  (cost=0.42..8.77 rows=0 width=0) (actual time=0.098..0.098 rows=0 loops=1)
  Buffers: shared hit=47
  ->  Index Scan using orders_pkey on public.orders  (cost=0.42..8.77 rows=20 width=6) (actual time=0.009..0.013 rows=20 loops=1)
        Output: ctid
        Index Cond: (orders.id > 199980)
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=92
Planning Time: 0.333 ms
Trigger RI_ConstraintTrigger_a_16417 for constraint order_items_order_id_fkey: time=305.286 calls=20
Execution Time: 305.451 ms

explainsql reports:

HIGH  ES009 Slow foreign-key trigger
The foreign-key trigger for order_items_order_id_fkey took 100% of the execution time.
Trigger: RI_ConstraintTrigger_a_16417
Constraint: order_items_order_id_fkey
Calls: 20
Time: 305.3 ms (100% of the execution)
Time per call: 15.3 ms
→ Index the referencing columns of order_items_order_id_fkey on the referencing table: each deleted or updated row looks up the rows that refer to it, and without an index every lookup scans that table.

All rules

ES010: Cartesian product

• Signal: a Nested Loop with no join filter that pairs each outer row with all inner rows. Its output must be at least 90% of outer rows × inner rows, and at least 1,000 rows.
• Evidence: outer rows, inner rows per loop, rows produced.
• Action: check the query for a missing join condition between the relations on either side.
• Stays silent when: either side returns a single row (as in a deliberate cross join with a one-row subquery), the inner side refers to the outer side (a parameterized scan), or the join is a semi or anti join.

Example

The nested_loop_cartesian scenario of the test corpus, captured on PostgreSQL 16: missing join condition: every selected customer is paired with every book.

SELECT c.id, p.id FROM customers c, products p WHERE c.id <= 100 AND p.category = 'books';

Nested Loop  (cost=0.29..1355.79 rows=100000 width=8) (actual time=0.023..12.047 rows=100000 loops=1)
  Output: c.id, p.id
  Buffers: shared hit=40
  ->  Seq Scan on public.products p  (cost=0.00..99.50 rows=1000 width=4) (actual time=0.006..0.511 rows=1000 loops=1)
        Output: p.id, p.name, p.category, p.price
        Filter: (p.category = 'books'::text)
        Rows Removed by Filter: 4000
        Buffers: shared hit=37
  ->  Materialize  (cost=0.29..6.54 rows=100 width=4) (actual time=0.000..0.004 rows=100 loops=1000)
        Output: c.id
        Buffers: shared hit=3
        ->  Index Only Scan using customers_pkey on public.customers c  (cost=0.29..6.04 rows=100 width=4) (actual time=0.012..0.019 rows=100 loops=1)
              Output: c.id
              Index Cond: (c.id <= 100)
              Heap Fetches: 0
              Buffers: shared hit=3
Settings: max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=126
Planning Time: 0.426 ms
Execution Time: 15.101 ms

explainsql reports:

HIGH  ES010 Cartesian product
Nested Loop pairs each of 1,000 outer rows with all 100 inner rows, with no condition between them.
Outer rows: 1,000
Inner rows per loop: 100
Rows produced: 100,000
→ Check the query for a missing join condition between products and customers.

All rules

ES011: Fewer parallel workers than planned

• Signal: Workers Launched lower than Workers Planned on a Gather or Gather Merge.
• Evidence: planned and launched workers.
• Action: the worker pool was exhausted. Review max_parallel_workers and max_worker_processes against the number of parallel queries running at once.

Example

The parallel_workers_not_launched scenario of the test corpus, captured on PostgreSQL 16: the plan asks for two workers but none can start because max_parallel_workers is 0.

SELECT count(*) FROM orders WHERE amount > 500;

Finalize Aggregate  (cost=3562.50..3562.51 rows=1 width=8) (actual time=29.513..29.603 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=2417
  ->  Gather  (cost=3562.49..3562.50 rows=2 width=8) (actual time=29.508..29.598 rows=1 loops=1)
        Output: (PARTIAL count(*))
        Workers Planned: 2
        Workers Launched: 0
        Buffers: shared hit=2417
        ->  Partial Aggregate  (cost=3562.49..3562.50 rows=1 width=8) (actual time=29.234..29.235 rows=1 loops=1)
              Output: PARTIAL count(*)
              Buffers: shared hit=2417
              ->  Parallel Seq Scan on public.orders  (cost=0.00..3458.67 rows=41528 width=0) (actual time=0.015..24.602 rows=99998 loops=1)
                    Output: id, customer_id, status, created_at, amount, note
                    Filter: (orders.amount > '500'::numeric)
                    Rows Removed by Filter: 100002
                    Buffers: shared hit=2417
Settings: max_parallel_workers = '0', parallel_setup_cost = '0', parallel_tuple_cost = '0', min_parallel_table_scan_size = '0'
Planning:
  Buffers: shared hit=83
Planning Time: 0.329 ms
Execution Time: 29.642 ms

explainsql reports:

HIGH  ES011 Fewer parallel workers than planned
Gather started 0 of the 2 parallel workers it planned.
Workers planned: 2
Workers launched: 0
→ The pool of parallel workers was exhausted: check max_parallel_workers and max_worker_processes against the number of parallel queries running at once.

All rules

ES012: JIT overhead dominates

• Signal: JIT compilation takes at least half of the execution time.
• Evidence: functions compiled, the time of each compilation step, and the execution time.
• Action: raise jit_above_cost, or set jit = off for OLTP workloads. When inlining and optimization dominate, raising jit_inline_above_cost and jit_optimize_above_cost keeps JIT but drops its most expensive steps.

Example

The jit_overhead scenario of the test corpus, captured on PostgreSQL 16: JIT compilation forced on a tiny query, so compiling takes far longer than executing.

SELECT count(*), sum(amount) FROM orders WHERE id <= 100;

Aggregate  (cost=11.54..11.55 rows=1 width=40) (actual time=99.751..99.753 rows=1 loops=1)
  Output: count(*), sum(amount)
  Buffers: shared hit=5
  ->  Index Scan using orders_pkey on public.orders  (cost=0.42..11.06 rows=94 width=6) (actual time=0.035..0.068 rows=100 loops=1)
        Output: id, customer_id, status, created_at, amount, note
        Index Cond: (orders.id <= 100)
        Buffers: shared hit=5
Settings: jit_above_cost = '0', jit_inline_above_cost = '0', jit_optimize_above_cost = '0', max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=103
Planning Time: 0.533 ms
JIT:
  Functions: 5
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.334 ms, Inlining 65.541 ms, Optimization 19.775 ms, Emission 14.332 ms, Total 99.982 ms
Execution Time: 117.904 ms

explainsql reports:

HIGH  ES012 JIT overhead dominates
JIT compilation took 100.0 ms of the 117.9 ms execution (85%).
Functions compiled: 5
Compilation: generation 0.334 ms, inlining 65.5 ms, optimization 19.8 ms, emission 14.3 ms
Execution time: 117.9 ms
→ Raise jit_above_cost so that JIT only starts for queries that run long enough to benefit, or set jit = off for OLTP workloads. Most of the time went to inlining and optimization: raising jit_inline_above_cost and jit_optimize_above_cost keeps JIT but drops its most expensive steps.

All rules

ES013: Planner settings force the plan

• Signal: the plan was made with an enable_* setting turned off, such as enable_seqscan = off, which the plan’s Settings show (EXPLAIN (SETTINGS)), or the planner used a node that such a setting disables because it found no other way: PostgreSQL 18 marks the node Disabled: true, and before 18 its estimated cost includes the 10¹⁰ PostgreSQL adds for each disabled node.
• Evidence: the settings, how the node is known to be disabled, and the time in the node.
• Action: reset the settings (RESET enable_seqscan, or a new session) and run EXPLAIN again: applications that plan with the defaults may get another plan. A setting made for a role or a database (ALTER ROLE or ALTER DATABASE … SET) applies to every session. To test a plan, SET LOCAL keeps a setting to one transaction.
• Severity: always medium. The finding says nothing about the plan’s speed, but the plan may not be the one applications get.
• Stays silent when: neither such settings nor a disabled node show. Settings that tune rather than take choices away, such as work_mem, random_page_cost or max_parallel_workers_per_gather, are not flagged.

Example

The group_aggregate scenario of the test corpus, captured on PostgreSQL 16: sorted GROUP BY (group aggregate) with hash aggregation disabled.

SELECT country, count(*) FROM customers GROUP BY country;

GroupAggregate  (cost=1834.77..1984.87 rows=10 width=11) (actual time=8.896..10.953 rows=10 loops=1)
  Output: country, count(*)
  Group Key: customers.country
  Buffers: shared hit=209
  ->  Sort  (cost=1834.77..1884.77 rows=20000 width=3) (actual time=8.627..9.450 rows=20000 loops=1)
        Output: country
        Sort Key: customers.country
        Sort Method: quicksort  Memory: 1081kB
        Buffers: shared hit=209
        ->  Seq Scan on public.customers  (cost=0.00..406.00 rows=20000 width=3) (actual time=0.008..2.751 rows=20000 loops=1)
              Output: country
              Buffers: shared hit=206
Settings: enable_hashagg = 'off', max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=76
Planning Time: 0.368 ms
Execution Time: 11.102 ms

explainsql reports:

MEDIUM  ES013 Planner settings force the plan
The plan was made with enable_hashagg = off, which keeps the planner from some of its usual choices.
Settings: enable_hashagg = off
→ Applications that plan with the defaults may get another plan: reset it (RESET enable_hashagg, or a new session) and run EXPLAIN again. If a role or a database sets it (ALTER ROLE or ALTER DATABASE … SET), every session plans this way; to test a plan, SET LOCAL keeps a setting to one transaction.

All rules

Architecture

This document describes how ExplainSQL is built and why it is built that way: the choice of language, the crates, the plan IR, the parsers, the metrics engine, the rules and the advisor, connected mode and each analysis built on it, and the release pipeline. Everything described here is implemented. For how to use each feature, see the user guide; for how to work on the code, see contributing.

Technology choice: Rust, Ratatui and Crossterm

We seriously considered both Rust (Ratatui + Crossterm) and Go (Bubble Tea + Lip Gloss).

Decision: Rust. The product is a data-heavy analysis engine with a thin UI on top. Writing the engine once and reusing it in the CLI, the TUI, a browser playground, an MCP server and an editor extension is what makes the project sustainable. Go would be the better choice only if time to the first release outweighed everything else, and the architecture below would not change.

Workspace layout

explain-sql/
├─ Cargo.toml                    # workspace, shared lints and profiles
├─ crates/
│  ├─ explainsql-core/           # no I/O, no async, WASM-compatible
│  │  ├─ src/ir.rs               # the plan IR
│  │  ├─ src/pg/                 # PostgreSQL front end: normalize, json, text, raw, lower; log entries
│  │  ├─ src/metrics.rs          # inclusive and exclusive time and buffers, misestimates
│  │  ├─ src/expr.rs             # reads the conditions printed in plans
│  │  ├─ src/rules/              # one file per rule
│  │  ├─ src/advisor/            # index candidates, rewrites, and why no index
│  │  ├─ src/catalog.rs          # what the database says about the plan's tables
│  │  ├─ src/check.rs            # the CI gate: findings, locked plans, explainsql.lock
│  │  ├─ src/compare.rs          # before and after a change: pages first, then time
│  │  ├─ src/memory.rs           # the work_mem a spill needs, and what it may take
│  │  ├─ src/diff.rs             # two plans of a statement, node by node
│  │  ├─ src/scenario.rs         # the planner settings explainsql may plan under
│  │  ├─ src/fingerprint.rs      # the same scan or join in another plan; plan shapes
│  │  ├─ src/counterfactual.rs   # why the planner chose its plan: questions and answers
│  │  ├─ src/params.rs           # statements with parameters: values to try, generic and custom plans
│  │  ├─ src/locks.rs            # the locks a statement takes: fast path, partitions, conflicts, waits
│  │  ├─ src/writes.rs           # what a write costs: HOT updates, the indexes that block them, WAL
│  │  ├─ src/top.rs              # pg_stat_statements rows: what can be planned, and why not
│  │  ├─ src/anonymize.rs        # names and values replaced, consistently, in JSON and text plans
│  │  ├─ src/timeline.rs         # plans over time from server logs: statements, plan changes, tags
│  │  ├─ src/requests.rs         # requests from statement logs: grouping, loops (N+1), the batched statement
│  │  ├─ src/analysis.rs         # metrics + findings + the one-sentence verdict
│  │  ├─ src/report.rs           # static reports: text, Markdown, JSON
│  │  └─ tests/                  # corpus, inputs, metrics, rules, report snapshots, robustness
│  ├─ explainsql-db/             # tokio-postgres + rustls: safe executor, prepared statements, catalog reader, locks, writes, HypoPG/rollback prover
│  ├─ explainsql-tui/            # Ratatui app: state, views, keymap, theme, icicle, the top list
│  └─ explainsql/                # binary: clap CLI, mode dispatch (tui | print | pager | json), connected mode, check, logs, top, requests
├─ fixtures/
│  ├─ schema.sql                 # deterministic dataset
│  ├─ scenarios/<name>.sql       # one statement plus expectations (rules, advice) per scenario
│  ├─ pg/{12..18}/               # generated plans: <name>.json, <name>.txt, manifest.json
│  ├─ inputs/                    # one plan in each form it arrives in: psql output, server logs
│  ├─ logs/                      # a session's auto_explain entries in stderr, csvlog and jsonlog
│  └─ requests/                  # an application's requests, as statement logging writes them
├─ fuzz/                         # cargo-fuzz target for the parsers (its own workspace; needs nightly)
├─ tools/cross-check/            # compares exclusive times with pev2 and explain.depesz.com
├─ action/                       # the GitHub Action's scripts (action.yml is at the root)
├─ docs/                         # the documentation site (mdBook): getting started, guide chapters, reference, rule catalog, design documents
│  ├─ rules/ES001.md …           # one page per rule, with an example from the corpus
│  └─ demo.svg                   # the README's demo, drawn from xtask/demo/recording.json
├─ install/                      # install.sh, install.ps1, packaging and smoke tests for releases
├─ xtask/                        # fixtures, rule pages, link check, demo; demo/: its query and recording
└─ .github/workflows/            # ci, docs, release, fixtures

In a terminal the binary opens the viewer; elsewhere, or with --print, it prints a report (--format text|md|json). --debug-parse shows what the parsers made of an input.

We use four crates and no more. Keeping core free of I/O is required for WebAssembly and for fast, deterministic tests; finer splits would slow down early development.

Process model: core is synchronous and pure. The TUI talks to the database layer over channels. The database layer runs on a background Tokio runtime and can cancel a running query.

Plan IR

Plans are stored in an arena: nodes live in a Vec<Node> and refer to each other through NodeId(u32) indices. This avoids ownership problems with parent pointers, is cache-friendly and serializes trivially. Code that walks the tree does so iteratively, so a deep plan cannot overflow the stack.

#![allow(unused)]
fn main() {
pub struct Plan {
    pub nodes: Vec<Node>,                   // nodes[0] is the root; a node's id is its index
    pub summary: Summary,                   // planning and execution time, triggers, JIT, settings, ...
    pub source: Source,                     // JSON or text, and the wrappers removed (psql table, log entry, ...)
    pub warnings: Vec<Warning>,             // problems found while parsing; the plan is usable despite them
}

pub struct Node {
    pub id: NodeId,
    pub parent: Option<NodeId>,
    pub children: Vec<NodeId>,
    pub node_type: String,                  // PostgreSQL's name: "Seq Scan", "Hash Join", "Aggregate", ...
    pub relationship: Option<Relationship>, // Outer | Inner | Member | InitPlan | SubPlan | Subquery
    pub subplan_name: Option<String>,       // "InitPlan 1", "SubPlan 2", "CTE totals"
    pub join_type: Option<String>,          // likewise strategy, operation, relation, index, alias, ...
    pub estimates: Option<Estimates>,       // cost, rows, width; None with COSTS OFF
    pub actuals: Option<Actuals>,           // per-loop time and rows, plus loops; None without ANALYZE
    pub buffers: Option<Buffers>,           // TOTALS across loops, not per loop (likewise io_timings, wal)
    pub predicates: Vec<Predicate>,         // Index Cond, Hash Cond, Filter, ... as raw text
    pub workers: Vec<Worker>,               // per-worker figures of parallel nodes
    pub extra: BTreeMap<String, serde_json::Value>, // every other property, under its JSON name
    // ...plus output columns, sort and group keys, rows removed by filters, workers planned and launched
}
}

• Typed fields cover what later phases rely on; everything else is kept. Other properties stay in extra under their PostgreSQL JSON names (Heap Fetches, Sort Method, Hash Buckets, …), and the statement-level sections (planning, triggers, JIT, serialization) keep their unfamiliar keys the same way. Nothing in the input is lost, and properties added by future server versions show up without code changes.
• Node types are strings, not an enum. Extensions and forks add their own (Citus and TimescaleDB custom scans, Greenplum’s Motion), and code that cares matches on the names it knows.
• Absent and zero mean the same. The text format leaves out zero counters and false flags, so the IR does too, whichever format a plan came from: all-zero buffers become None, a zero Subplans Removed is dropped, and so on. This is what lets the JSON and text forms of a plan lower to identical IR.
• Derived metrics live beside the IR. The metrics engine computes inclusive and exclusive time, shares of the total and misestimate factors into a separate structure indexed by NodeId. A figure the plan cannot support is None: without ANALYZE or with TIMING OFF there are no times, and nothing is filled in from estimates, so the UI never presents a guess as a measurement.

The IR uses PostgreSQL’s vocabulary, since PostgreSQL is the only engine for now, but its structure (arena, estimates, actuals, predicates, extra) is engine-neutral. A future MySQL front end would lower into the same IR, as the pg module does.

Parsing pipeline

input ─▶ normalize() ─┬─▶ json::parse() ─┬─▶ raw tree ─▶ lower() ─▶ Plan
                      └─▶ text::parse() ─┘

• JSON is the primary format and the source of truth. When connected, ExplainSQL always requests EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON) (VERBOSE, SETTINGS, FORMAT JSON for the estimated plan), with SETTINGS from PostgreSQL 12 and WAL added for statements that write from 13.
• The text format is supported from v0.1. It is the default in psql and in auto_explain, and most plans shared in issues and chats are text. Accepting only JSON would turn away a large share of users on their first try.
• normalize() removes what surrounds a plan and tells JSON (input starting with [ or {) from text. It handles psql’s aligned output (ASCII and Unicode line styles, borders 0–2, + and ↵ continuation marks, the (N rows) footer) as well as its wrapped, expanded and CSV formats; auto_explain entries in stderr logs (whatever the log_line_prefix), jsonlog and csvlog, keeping the logged query text; result cells copied in double quotes, as GUI clients such as pgAdmin copy them; Markdown code fences; prompts and other text before a plan; shared indentation; and byte order marks, CRLF line endings and non-breaking spaces. Wrappers can nest (a fenced log excerpt), and each one removed is recorded in Plan::source.
• Both parsers build the same raw tree: nodes holding their properties under PostgreSQL’s JSON names. The JSON parser reads it off directly. The text parser translates each line into those names: Buffers: shared hit=5 read=2 becomes Shared Hit Blocks and Shared Read Blocks, and Sort Method: quicksort Memory: 25kB becomes Sort Method, Sort Space Type and Sort Space Used. One lowering step then serves both formats, and comparing them is direct.
• The text parser is hand-written, with no regular expressions and no parser generator. An indentation stack follows the layout rules of PostgreSQL’s explain.c: a node’s properties start two columns to the right of its name, a child’s -> arrow sits in its parent’s property column, and an InitPlan, SubPlan or CTE label sits in the property column with its node two columns further in. Lines after the tree that start in column 0 belong to the statement (Planning:, triggers, JIT:, Settings:, Execution Time, …). Text plans do not print relationships; they are inferred from the parent’s type and the child’s position.
• lower() builds the typed IR, absorbing version drift (below) and the differences that only reflect how a plan was printed.
• Never fail hard. Unfamiliar properties are kept in extra. In text plans they also produce a warning, and a line that cannot be read at all is kept verbatim in extra["Unparsed Lines"]. A truncated JSON plan is closed after its last complete value, and a truncated text plan keeps the nodes before the cut. Input holding several plans, such as a before-and-after pair, yields the first one and a warning. Only input with no plan in it is rejected, with one exception: serde_json parses recursively, so JSON nested deeper than 512 levels (255 plan levels) is refused rather than risking the stack. Text plans have no depth limit.
• Several plans. parse() reads the first plan of the input and warns of others; parse_all() reads them all, in order. normalize_all() keeps every Markdown fence, every auto_explain entry of a log (each with its query text) and every psql result; then the JSON parser reads each plan of an array and each JSON value that follows, and the text parser starts a new plan at each root line in column 0. Text between two plans that belongs to neither, such as After:, is left out with a warning on the plan it precedes. For an input with one plan, parse_all() returns what parse() does; the corpus and the input fixtures check it.
• Not supported: the YAML and XML formats.

Version and fork drift (examples)

• PostgreSQL 13: buffers used during planning; the WAL option.
• PostgreSQL 14: the Memoize node.
• PostgreSQL 16: the GENERIC_PLAN option.
• PostgreSQL 17: the SERIALIZE and MEMORY options; I/O timings split into shared and local; subplan outputs shown as (InitPlan 1).col1 instead of $0.
• PostgreSQL 18: BUFFERS is on by default with ANALYZE; actual row counts are always printed with two decimals (rows=10.00, and "Actual Rows": 10.00 in JSON, so parsers must read them as floats); Index Searches; Disabled: true on nodes the planner had to use despite an enable_* setting. Older versions add a huge disable_cost of 1e10 instead, which a naive tool mistakes for the most expensive node.
• Extensions and forks: Custom Scan nodes (Citus, TimescaleDB), Motion nodes (Greenplum). Unknown node types are rendered generically, never rejected.

The parsers handle all of the above for PostgreSQL 12–18, and the corpus covers every version. The corpus also surfaced drift that is easy to miss: the source of an INSERT, UPDATE or DELETE is a Member of ModifyTable up to PostgreSQL 13 and its Outer child from 14; JIT generation time becomes an object with a separate Deform part in 17; and PostgreSQL 12 labels the leader’s JIT figures as worker −1. lower() maps each of these, like the renamed I/O timing keys, to a single form.

Testing the parsers

• Fixture corpus (tests/corpus.rs): all 882 generated plans parse without a single warning, and for each of the 441 scenario–version pairs the JSON and text forms lower to the same IR: tree shape, node types and relationships, every typed property, estimates, actual rows and loops, and everything in extra. The two forms come from separate executions (see fixtures/README.md), so timings, buffer counts and per-worker figures are not compared, and two kinds of values are excluded on principle: memory figures of nodes below a Gather, which depend on how much of the work the leader did, and estimates of data-modifying statements, because rolled-back writes still grow the table and the planner scales its estimates by the table’s current size. A guard test checks that the comparison does notice changed values.
• Captured inputs (tests/inputs.rs, fixtures/inputs/): one query’s plan, captured from a real server in every form it arrives in (psql’s aligned, Unicode, bordered, wrapped, expanded and CSV output, in text and JSON; auto_explain entries in stderr, jsonlog and csvlog logs), must yield the same tree as the plain text plan. Generated variants add cells copied from GUI clients, Markdown fences, CRLF, prompts, indentation, non-breaking spaces, truncation, several plans in one input, and inputs that must be rejected.
• Constructs the corpus does not reach (tests/text_format.rs, tests/unknown_properties.rs): other join, aggregate and set-operation variants, quoted identifiers, foreign and custom scans, compound property lines, the statement summary, and unfamiliar properties at every level of both formats.
• Robustness (tests/robustness.rs): thousands of truncated and mutated corpus plans, and pathological input (1,500 levels of indentation, a 20,000-child Append, JSON nested 100,000 levels deep, malformed fragments of every construct), must never cause a panic in the parsers, the analysis or the reports.
• Fuzzing (fuzz/): cargo +nightly fuzz run parse, seeded with the corpus and the captured inputs; a second target, analyze, also runs the analysis and the reports. The Phase 1 exit run of parse lasted one hour on three workers: 2.9 million inputs and no crash, timeout or memory blow-up.
• Snapshots of the rendered reports cover the parsers’ output end to end; see Testing the metrics and the rules.

Metrics: inclusive and exclusive time

Getting per-node numbers right is harder than it looks, and everything else is built on it. metrics::compute works on the IR alone.

• Times and rows are per-loop averages; buffers are totals across loops. Time is multiplied by loops; buffers never are.
• Parallel query. Below a Gather or Gather Merge, loops counts the processes that ran a node side by side, so time × loops is CPU time, not wall-clock time, and subtracting it makes the Gather’s own time negative. The engine counts the processes as the loops of the Gather’s child per loop of the Gather (workers plus the leader, when it takes part). It divides by that number for wall-clock time and keeps the undivided figure as CPU time.
• CTEs. A CTE runs as the CTE Scans reading it pull rows, so its time is already inside those scans. It is not subtracted from the node it is listed under. The scans subtract it instead, in proportion to their own time: the scan that pulls rows first computes them, and later ones read them from the CTE’s store.
• InitPlans. An InitPlan runs when its result is first needed, inside the node that needs it. That node subtracts it, rather than the node the InitPlan is listed under. The engine finds it by the reference to the result: $0 before PostgreSQL 17, (InitPlan 1).col1 from 17. When several nodes refer to it, the first in execution order (post-order) takes it.
• SubPlans run from the expressions of the node they are listed under, which subtracts them like any child.
• Rounding. Times are printed per loop with 0.001 ms resolution, so over 20,000 loops a figure can be off by 10 ms, and a parent can show less time than its children together. Within that tolerance, the engine moves the gap to the least precise figures (those with the most loops), never below what their own children need. Exclusive times then add up to the tree’s time. A node whose children exceed it by more than rounding explains is flagged as inconsistent; none is in the corpus.
• Time outside the tree. Triggers (including foreign-key checks, which can dominate a slow DELETE), SERIALIZE and executor startup are not part of any node. They are reported for the statement, along with an unattributed remainder: execution time minus the tree, triggers and serialization. JIT compilation falls partly inside node times and partly outside, so it is reported on its own.
• Misestimates compare actual and estimated rows per loop, each counted as at least one row. A node that a Limit, a semi or anti join, a merge join or a subquery can stop early is marked, so that returning fewer rows than estimated is not mistaken for a bad estimate.
• Never-executed nodes count as zero; with TIMING OFF or without ANALYZE, times are None and hotspots are ranked by buffers.
• I/O time (track_io_timing) is subtracted like buffers: each node keeps what it read and wrote itself. For the statement, the root’s figures, which include every node and every process, are split into reads of pages outside shared buffers, writes, and temporary files, and compared with the time of every process: the nodes’ own CPU time, summed. Compared with the leader’s wall-clock time alone, a parallel scan that waits for reads in three processes would take more than all of it.

The results agree with pev2 and explain.depesz.com, compared node by node on 24 reference plans from PostgreSQL 13, 16 and 18 (see tools/cross-check). Every node is within 5%, except in the Memoize plan above. There, both tools clamp the negative rounding gap to zero, so their exclusive times add up to more than the statement took.

Rules and reports

• Rules (rules/) read the IR and the metrics and return findings: the rule, the node, a severity from the share of the runtime involved, the evidence, and an action. Each rule is one file with its thresholds as constants, and each documents when it stays silent. The catalog is rules.md.
• Conditions are read by the predicate reader in expr.rs (see Predicate parsing): enough to tell which columns a filter compares with what, whether it wraps them in a cast or a function, and whether it ORs conditions on different columns.
• The verdict is one sentence: the statement’s time, where most of it went, and the finding about that node, if any. For example: 11.9 ms. 100% of it in Seq Scan on orders, which reads 200,000 rows to keep 10.
• Reports (report.rs) come in three formats:
  • text for terminals: the verdict, statement figures, the plan tree with exclusive time, bars and misestimate marks, and the findings;
  • Markdown for issues and pull requests;
  • JSON with the plan, the metrics and the findings, for other programs.

Testing the metrics and the rules

• Metrics invariants over the corpus (tests/metrics.rs), across 882 plans:

  • no node is inconsistent;
  • exclusive times add up to the tree’s time;
  • the tree never exceeds the execution time;
  • shares add up to at most 100%.

  Unit tests cover each exception above on small plans.

• Rules against the scenarios (tests/rules.rs): each scenario’s header lists the rules its plan triggers, and no other rule may fire, in either format on any version. A rule marked ? may fire on some versions only, where the planner’s estimates differ. Scenarios without rules, such as most of the traps for naive advisors, expect silence. A second test checks that the actions name the right columns and remedies.

• Snapshots (tests/report.rs, with insta): the text report of 24 reference plans and a Markdown report. A change in the metrics, the rules or the layout shows up as a reviewable diff.

• The binary (crates/explainsql/tests/cli.rs): formats, standard input, exit codes, and a reader that closes the pipe early.

The viewer

explainsql-tui shows a plan and its analysis. It depends only on core and Ratatui (0.29, the last release that builds with Rust 1.85), with Crossterm as the backend.

• State apart from drawing. app.rs holds the state: the visible rows, the selection, folds, search, focus and view modes. It turns keys into changes, with no terminal involved, so navigation is unit-tested directly. ui.rs draws a frame from that state, and lib.rs runs the loop: draw, wait for a key or a resize, handle it. Nothing is redrawn while nothing happens.
• Layout. The verdict and statement figures sit on top. Below them are the plan tree (share, time, bar, node, rows, estimate with ▲▼ marks, buffers), the details of the selected node, the findings and a status line. From 110 columns the details sit beside the tree; below that they go under it, and the tree takes only the rows it needs. Columns are dropped (buffers, then the bar, then the estimate) before the node names get shorter than 30 characters, so 80×24 stays usable.
• Virtualized tree. Only the rows on screen are built and drawn. Node names and column widths are computed once, when the viewer opens. A frame of a 5,000-node plan takes about 0.3 ms in a release build.
• Folding. Any node can be folded. Runs of four or more similar leaves are folded into one row from the start, such as the scans of a thousand partitions. Leaves are similar when they have the same type, and the same relation and conditions once numbers are blanked out. The folded row adds up their time, rows and buffers.
• Views. x shows time including children, w shows CPU time summed over parallel processes, and b shares by buffers instead of time. Including children, CPU time is summed over the subtree, because a Gather’s own figures cover only the leader.
• Findings and advice. A marker in the tree shows which nodes have findings. The panel under the tree lists the findings (f) or the advice (i). Both lists are browsable, and Enter jumps to the node, opening whatever folds hide it. c copies the selected CREATE INDEX with the OSC 52 escape sequence, which works over SSH and inside tmux. Number keys jump to the hotspots.
• Colors. True color, then 256 colors, then 16, depending on COLORTERM and TERM. With NO_COLOR it falls back to bold, dim and reverse video. --theme light adapts the palette to light backgrounds. Severities and misestimates always carry a word or a symbol as well as a color.
• Input. Keys come from the terminal even when the plan arrived on standard input: Crossterm opens /dev/tty on Unix and the console input on Windows.
• Pager mode. --pager reads what psql sends to its pager. A plan opens in the viewer. Anything else goes to $EXPLAINSQL_PAGER, $PAGER or less -S, never back to explainsql, and is printed directly when none of them runs. When the output is not a terminal, everything passes through unchanged.
• Tests. tests/render.rs draws frames with Ratatui’s TestBackend and compares them with snapshots: five reference plans at 120×40 and 80×24, help, search, the findings and the view modes. A 5,000-node plan that cannot be folded must draw a frame in under 16 ms in release builds (200 ms in debug builds).

The icicle view

icicle.rs draws the plan as nested boxes: the root on top, each node under its parent, as wide as the time spent in it and below it. The weight is CPU time when the plan was timed, so that the workers of a parallel plan add up under the node that gathers them, and estimated cost otherwise, a node’s own cost being its cost less its children’s. InitPlans, SubPlans and CTEs sit under the node they are listed under; trigger time, outside the tree, goes in the title. Widths are whole columns: a subtree too narrow for one is folded into its parent and drawn as …, and zooming on a box redraws it at the full width, which opens those folds. The view shares the tree’s selection, details, search and hotspots, so F switches between the two on the same node, and tests/render.rs snapshots it.

Predicate parsing

Filter and join conditions arrive as deparsed text such as ((status)::text = 'open'::text). expr.rs reads them with a small hand-written reader, not regular expressions:

• It splits conditions at the top-level AND and OR, outside parentheses, brackets and quotes, and finds the comparison operator of each part.
• It tells a column from a value, a cast of a column ((customer_id)::text) and a function of one (date_trunc('day', created_at)).
• It treats plan-only syntax as values: $1, (InitPlan 1).col1, ANY ('{…}') and ARRAY[…].

The rules and the advisor share it. The reader only has to understand what PostgreSQL’s deparser prints, a narrow and regular dialect, and it has been run over every condition in the corpus.

We considered sqlparser-rs, which would mean wrapping each condition as SELECT 1 WHERE (…) and first replacing plan-only syntax. The hand-written reader won for three reasons: it adds no dependency, it stays WebAssembly-friendly, and it never rejects deparse-only forms such as ~~ for LIKE. A condition it cannot read produces no advice. libpg_query (through pg_query.rs) may be added later for native builds only, where its query fingerprinting is useful for grouping queries found in logs.

Index advisor (without requiring HypoPG)

advisor/ turns the analysis into suggestions. Precision comes first: a wrong CREATE INDEX costs the reader more than a missing one.

• Where candidates come from. The rules have already decided that a scan is worth an index, with their gates on selectivity (under 5%), table size (1,000 pages or more), share of the runtime (10% or more) and early stops. The advisor takes their findings:

  • ES001, a selective sequential scan: index the filtered columns, or for the inner side of a nested loop, the join key;
  • ES005, a nested loop: index the inner side’s join key;
  • ES006, an index scan that filters: a composite index that also covers the filtered columns;
  • ES009, a foreign-key trigger: index the constraint’s referencing columns.

  Three patterns no rule covers are added, with the same gates:

  • ORDER BY … LIMIT sorting a whole table with a top-N heapsort: an index in the sort order;
  • selective scans of every partition of a table, each too small for ES001 but large together: one index on the partitioned table;
  • a correlated subquery (SubPlan) that rescans a table for every outer row: index the column it compares with the outer row.

• Keys (keys.rs). Each condition is classified:

  • equality: =, IN/= ANY, IS NULL, ORs on one column;
  • range;
  • prefix LIKE: b-tree with text_pattern_ops;
  • substring LIKE/ILIKE: GIN with gin_trgm_ops;
  • containment (@>, &&, @@, …): GIN;
  • a cast or function of the column;
  • an OR across columns.

  Columns follow the ESR rule: equality first, then the sort order, then at most one range. A comparison with another table’s column is a join key, which counts as equality. Plans do not list an index’s columns. When an index scan under a Limit was chosen for its order, the sort column is read from PostgreSQL’s default index name (orders_created_at_idx), and the suggestion says so.

• Rewrites. A condition that wraps the column in a cast or a function gets a rewrite instead of an index, since no index on the column can serve it.

• Explanations. A sequential scan that takes 10% or more of the runtime and gets no suggestion is explained, in this order:

  • it has no filter, so the query needs every row;
  • a Limit or semi join stops it early;
  • its filter ORs different columns;
  • the table is small;
  • it keeps too many rows.

• Merging. The same index found twice, or an index whose columns start another candidate’s, is reported once, with the evidence of both.

• Output. Each suggestion has:

  • its DDL, always CREATE INDEX CONCURRENTLY except on partitioned tables, where PostgreSQL does not support it (the caveat says how to build it without blocking writes);
  • a confidence: high, lowered to medium for an inferred column, an operator class that depends on the collation, pg_trgm, or a comparison with a run-time value;
  • the evidence and the caveats;
  • a verification status: unverified, estimated with HypoPG, or measured with rollback.

  Without a connection every suggestion says that existing indexes, the write load and the statistics were not checked. A foreign-key suggestion names the constraint and the query that lists its columns, since the plan does not show them.

Quality gate (tests/advisor.rs): every scenario says what the advisor must conclude: advice: none (a trap for naive advisors), advice: rewrite, or advice: index with each expected index in an index: line. On every version and in both formats, a trap gets no suggestion, and an index scenario gets exactly its indexes. A scenario without an advice line must get no suggestion either, so every suggestion the corpus produces has been reviewed.

In connected mode the catalog refines the advice (see “Connected mode and safety” below). Partial indexes for rare constants in pg_stats.most_common_freqs and INCLUDE columns are left for later.

Related work: Microsoft’s AutoAdmin “what-if” indexes (Chaudhuri and Narasayya), Dexter, postgres-mcp (HypoPG with a greedy, “Anytime”-style search) and pganalyze’s writing on its indexing engine.

Connected mode and safety

explainsql -d "$DATABASE_URL" -f slow.sql (or -c "SELECT …") runs the query itself. explainsql-db holds the connection on a small Tokio runtime behind a blocking API. The binary runs it on a worker thread that talks to the viewer over channels.

• Connection settings follow libpq (conn.rs): if psql connects, explainsql does too. In order of precedence:

  1. what -d gives: a URL, key=value settings or a database name;
  2. the service file (PGSERVICE, ~/.pg_service.conf, then the system file);
  3. the PG* environment variables;
  4. the defaults: the Unix socket, or localhost, port 5432 and the user’s name.

  The password comes from ~/.pgpass (%APPDATA%\postgresql\pgpass.conf on Windows). Like libpq, explainsql ignores the file when others can read it. TLS uses rustls (tls.rs) with libpq’s meanings:

  • prefer and require encrypt without checking the certificate;
  • verify-ca checks the chain against sslrootcert or the system store;
  • verify-full also checks the host name.

• Every run is rolled back (exec.rs). Each EXPLAIN happens inside BEGIN … ROLLBACK, with SET LOCAL statement_timeout (--timeout, 30 s by default). ROLLBACK runs whatever happened before it, and the code has no path that commits.

• The estimated plan comes first. It shows at once, and it tells what the statement does. A ModifyTable node (INSERT, UPDATE, DELETE, MERGE, also inside a WITH) or a LockRows node (FOR UPDATE) means the statement writes. Such statements run under EXPLAIN ANALYZE only with --allow-dml, whose help warns that sequences, dblink calls and other effects outside the database are not undone. Every other statement runs in a READ ONLY transaction, where even a function that writes fails.

• One statement, of a kind EXPLAIN takes. Statements go through the extended query protocol, which refuses several statements in one string. Anything that does not start with SELECT, WITH, VALUES, TABLE, INSERT, UPDATE, DELETE or MERGE is refused before anything runs. That covers DDL, CREATE TABLE AS and a pasted EXPLAIN.

• In the viewer, EXPLAIN ANALYZE runs in the background while the estimated plan is shown. The status line counts the seconds, and Esc cancels the run through PostgreSQL’s cancel request. r runs the statement again. e opens it in $VISUAL or $EDITOR, then shows the estimated plan of the edited statement and runs it.

• Catalog reads (catalog.rs) cover only what the advisor needs. They run in a read-only transaction:

  • for the tables in the plan, in the advice and behind the foreign keys it names: the size, the indexes (with their key columns and validity), the column collations and n_distinct, and the last analyze;
  • the columns of those foreign keys;
  • the installed extensions.

  advisor::refine then:

  • turns a candidate that an existing valid index already covers (by the left-prefix rule) into an explanation of why the planner probably did not use it;
  • turns a foreign-key suggestion into a CREATE INDEX on its referencing columns;
  • drops text_pattern_ops for columns with the C collation, and the pg_trgm caveat when the extension is installed;
  • states how large the table to index is.

• Tests (crates/explainsql-db/tests/live.rs, and the connected cases of crates/explainsql/tests/cli.rs) run against a database with the fixture schema, named by EXPLAINSQL_TEST_DATABASE_URL; the CI’s db job provides PostgreSQL 16 with HypoPG. A second session counts the rows of the tables touched by DELETE, UPDATE, INSERT and a data-modifying WITH before and after each runs with --allow-dml: nothing ever changes. Other tests cover:

  • the refusal without --allow-dml;
  • a writing function failing in the read-only transaction;
  • several statements and DDL being refused;
  • the timeout and cancellation, after which the connection stays usable;
  • the catalog reads.

• The proof loop (prove.rs) tests a suggested index before anyone creates it. It uses t in the viewer, or --prove with --print:

  • With HypoPG installed, explainsql creates a hypothetical index inside a read-only transaction and gets the estimated plan with it. hypopg_reset() follows unconditionally, since hypothetical indexes outlive transactions. Nothing is built and nothing is locked.
  • Without HypoPG, --allow-ddl builds the index for real, without CONCURRENTLY, inside a transaction that is rolled back. SET LOCAL lock_timeout = '2s' keeps it from waiting behind other sessions, and EXPLAIN ANALYZE measures the statement with it. Building blocks writes to the table, so the viewer first shows the table’s size and asks. Only a single CREATE INDEX is accepted.
  • compare.rs sets the plans side by side: pages read, pages written to temporary files, execution time or estimated cost, and the indexes the second plan uses. Pages decide first: unlike times, they do not depend on what the cache holds. Temporary files come next, then time, and only changes over 10% (and 0.1 ms for times) count; fewer pages but a slower run is mixed. Measured sides run once first only to warm the cache: without that, the run before the index often met a colder cache than the run after it, which followed the build that had just read the whole table. --runs N measures each side N times and compares medians. advisor::verify records the result: estimated or measured, with the before/after line. A suggestion the planner would not use, or that is not better by more than the noise, drops to low confidence and says so.

  After a run, r or an edit with e compares the new measured plan with the previous one in the status line.

  A rolled-back INSERT or UPDATE still leaves dead rows until the next VACUUM, as any rolled-back transaction does; --allow-dml’s help says that effects outside the table data are not undone.

• Not yet: partial indexes from pg_stats.most_common_freqs, and INCLUDE columns.

Why not: asking the planner again

A plan shows what the planner chose, not what it turned down. counterfactual.rs asks: it plans the statement again with the choice taken away and compares. It is pure: it picks the questions and reads the plans the database returns; connected.rs in the binary runs them through explainsql-db.

• Questions. With --why-not and no name, the hot nodes, at most three: sequential scans with a condition that take 10% or more of the runtime, nested loops that ES005 flags or that follow an underestimated outer side, and, with --measure, sorts and hashes that spilled. --why-not TABLE asks about the sequential scans of a table, or of the table an index belongs to; y in the viewer about the selected node.
  • A sequential scan: enable_seqscan = off, and random_page_cost = 1.1 to see whether the planner would take an index by itself.
  • A nested loop: enable_nestloop = off.
  • A spill: work_mem large enough to stay in memory, a power of two megabytes up to 1 GB, from what the plan shows (three times the sort’s disk space, the hash’s peak memory times its batches).
• Settings (scenario.rs). Only planner settings on a fixed list (enable_*, the cost constants, work_mem, hash_mem_multiplier, effective_cache_size, the collapse limits, plan_cache_mode, jit), each with a value of its type, and memory with an explicit unit. explainsql-db checks them again and applies them with set_config(name, value, true), names and values bound as parameters, inside the transaction that is rolled back.
• Matching (fingerprint.rs). Another plan of the statement has other node ids and often another shape. A scan is found again by its relation and alias, a join by the set of relations below it. An index scan counts as using an index only with an index condition: with sequential scans off, the planner may read a whole index without one, in its order, just to avoid the disabled scan.
• Answers. Estimated first, which is enough when no alternative exists:
  • Unusable: even with sequential scans off, no index serves the condition. The condition and the catalog say why: a cast or a function of the column (with its type), ORs across columns, <>, a pattern starting with a wildcard, LIKE with a collation other than C and no text_pattern_ops, an operator that needs GIN, or indexes that start with another column, are invalid or partial.
  • Costlier: the planner can use the alternative and estimates it more expensive, by how much; within 10% is a close call that a small change can flip. Before PostgreSQL 18, the cost of a plan with a disabled node includes 10¹⁰ per node; it is taken out.
  • With --measure, the planner’s choice and the alternative run the same number of times, after a warm-up run, and compare as above. Not better: the planner is right. Better, and the scan’s rows were overestimated tenfold or more (or, for a nested loop, its input underestimated): a misestimate. Better, with close estimates, and with random_page_cost = 1.1 the planner picks an index by itself: that plan runs too, and only if it is better as well is the setting suggested (cost settings). Otherwise the planner is wrong for a reason not found. A spill asks about time: staying in memory but running slower does not help.
  • An alternative that runs past the statement timeout while the planner’s choice finished makes the planner right.
• Approximation. enable_* settings hold for the whole statement, so other scans and joins can change too. The answer lists them and is marked approximate.
• Advice. Analysis::record keeps the answers and puts them into the advice: an existing index that the planner did not use gets the reason found instead of the likely ones.
• Tests. counterfactual.rs covers every answer on small plans. crates/explainsql-db/tests/live.rs checks that settings hold only inside their transaction and that others are refused; crates/explainsql/tests/cli.rs asks about a function of a column, a broad range and a sort that spills, against the fixture database.

Statements with parameters

A statement with parameters has two kinds of plans. A custom plan is made for the values of one execution. The generic plan is made once for any value. After five custom plans, PostgreSQL switches to the generic plan when its estimated cost is below the custom plans’ average cost, each with a charge for planning (choose_custom_plan in plancache.c), and then keeps it. params.rs finds how the plan depends on the values. It is pure: it maps the parameters, picks the values and judges the plans. params.rs in the binary runs the plans through explainsql-db’s prepared.rs.

• Placeholders. $n, or JDBC’s ? turned into $n in order. Literals, quoted identifiers, dollar quotes and comments are skipped, and ?? becomes the ? operator. PostgreSQL infers each parameter’s type from a PREPARE (pg_prepared_statements.parameter_types).

• Running. Each plan comes from its own PREPARE and EXPLAIN EXECUTE. They run inside a transaction that is rolled back, under plan_cache_mode (force_generic_plan or force_custom_plan, from PostgreSQL 12). The values travel as literals in dollar quotes whose tag they do not contain. The statement is deallocated after the rollback, which does not undo a PREPARE, whatever happened. Each run prepares the statement again, so that no cached generic plan carries over from earlier settings. The usual safety holds: the estimated plan first, READ ONLY unless --allow-dml, and a timeout.

• Mapping. A parameter is compared with a column when a scan’s index condition, recheck condition or filter does so: col = $1, a cast of the column, $1 <= col flipped, or col = ANY (ARRAY[$1, $2]) for an IN list. A parameter after LIMIT, OFFSET or FETCH FIRST counts rows. Mapping uses the generic plan with NULL values and enable_partition_pruning = off. The generic plan prunes partitions when it starts, using the values it runs with, so with NULL values it would prune them all.

• Values.

  • For equality: two most common values, the least common of the most common values, and a histogram value outside them.
  • For a range: the bounds at the 0th, 25th, 50th, 75th and 100th percentiles of the histogram.
  • For a LIMIT or an OFFSET: fixed row counts.
  • Statistics come from pg_stats, of the partitioned table (pg_partition_root, inherited) when the scanned table is a partition and the partitioned table has them.

  Each parameter is tried with the others held at a typical value: the one that keeps the most rows, or a page of 10 rows for a LIMIT. --bind fixes a value. A parameter with no value stops the trials: holding it at NULL would make every custom plan a contradiction.

• Same plan. A custom plan is the generic plan when their shapes match. Before comparing, an Append or Merge Append that the generic plan’s run-time pruning left with one child is replaced by that child (fingerprint::pruned_shape). A custom plan that proves there is no row (a Result whose one-time filter is false, as a range that ends before it starts) is set aside.

• Judging.

  • Estimated: values that get another plan make the statement sensitive.
  • Measured (--measure): the custom plan and the generic plan run with the same values. The generic plan hurts when it reads at least twice the pages, or, for as many pages, takes twice the time, or runs past the timeout while the custom plan finished. Another plan that does not hurt is harmless.
  • The switch is predicted from the planner’s costs. The generic plan is kept whatever the first five values when its cost is below every custom plan’s, never when it is above them all, and otherwise depending on those values.
  • Advice to plan each execution (plan_cache_mode = force_custom_plan, pgJDBC’s prepareThreshold=0) comes only when PostgreSQL could switch.

• Report. Measured, the report shows the generic plan run with the values it does worst with, so that the findings and the advice (often an index that serves every value) are about that plan. The parameters section comes first, in text, Markdown and JSON.

• Tests. params.rs covers placeholders, clauses, mapping, values, holding, trials and every verdict on small plans. fingerprint.rs covers pruned shapes, and tests/report.rs snapshots the report. crates/explainsql-db/tests/live.rs checks generic and custom plans, values that try to escape their quotes, writes refused without --allow-dml, that nothing stays prepared, and partition pruning. crates/explainsql/tests/cli.rs runs a customer’s latest orders, whose generic plan walks the index of dates, against the fixture database.

Locks

EXPLAIN does not show locks, but the transaction that exec.rs rolls back still holds them after the EXPLAIN. locks.rs in explainsql-db reads them there, between the EXPLAIN and the ROLLBACK: first the backend’s own locks from pg_lock_status(), leaving out the one on its own virtual transaction ID (the only lock the transaction holds before the statement), then the catalog for the locked relations, and pg_locks with pg_stat_activity for other sessions’ locks on them. The queries that read them take their own locks only after the first query. locks.rs in the core is pure: it reads a capture and the plan.

• Fast path. A backend takes weak relation locks (AccessShareLock, RowShareLock, RowExclusiveLock) in slots of its own when no session holds a strong lock on the relation (lock.c): 16 before PostgreSQL 18, and from 18 max_locks_per_transaction rounded up to a power of two, in groups of 16 that each take a share of the relations (FastPathLockGroupsPerBackend). pg_locks.fastpath says which got one. Locks that did not fit go to the shared lock table, which statements that take many locks contend for (LWLock:LockManager). Locks outside the fast path with free slots left were moved there by a strong lock.
• By table. Each lock is grouped under its table: an index under its table, a TOAST table under its main table, a partition and its indexes under the partitioned table at the top (pg_partition_root). The plan says which indexes and partitions it reads (Index Name, Relation Name with its schema). Indexes the plan does not use, not scanned since pg_stat_database.stats_reset (idx_scan is 0), and that enforce no constraint are named.
• Conflicts. The conflict table of the documentation (LockMode::conflicts_with) names the commands that would wait for the statement’s strongest lock on each table, and, when the plan leaves indexes unused, the commands that lock an index. Other sessions’ granted or awaited locks that conflict are notes.
• Generic plans. prepared.rs prepares the statement and makes its plan in a first transaction, then runs EXPLAIN EXECUTE in a second and reads the locks there. A cached generic plan is not planned again: AcquireExecutorLocks (plancache.c) locks every relation in it, partitions that initial pruning then drops included. Deferring those locks to after pruning was committed for PostgreSQL 18 and reverted. A custom plan is planned for each execution, so its locks are the planner’s. compare_executions compares the two counts.
• Waits. A second connection, opened with the first run it watches, samples pg_stat_activity (wait_event_type, wait_event, and pg_blocking_pids for a lock) every 10 ms for the backend and, from 13, its parallel workers (leader_pid). The backend’s PID is read in each transaction, as a pooler may run it on another backend. exec.rs and prepared.rs run a measured run again, up to twice, when it waited for another session’s lock, and note it.
• Tests. locks.rs covers grouping, the fast path before and from 18, unused indexes, conflicts, waits and the generic plan’s note on small captures, and tests/render.rs snapshots the viewer’s overlay. crates/explainsql-db/tests/live.rs reads the locks of the partitioned fixture table planned with now(), of a write, and of its generic and custom plans, and watches a run that waits for another session’s row lock and one that a LOCK TABLE waits behind. crates/explainsql/tests/cli.rs runs --locks, with and without --bind.

Writes

The transaction that exec.rs rolls back also counts what the statement wrote. writes.rs in explainsql-db reads pg_stat_xact_user_tables, the transaction’s own counters of rows inserted, updated, HOT-updated and deleted by table (and, from 16, n_tup_newpage_upd), before and after the EXPLAIN ANALYZE. The view also holds counts of the backend’s earlier transactions that it has not reported to the statistics yet, so the statement’s rows are the difference. The read before runs in a savepoint that is rolled back, which releases the locks it takes on the catalog: they would otherwise count among the statement’s. The tables written are then read from the catalog: their fillfactor, and for each index its access method, whether it is partial, enforces a constraint or belongs to an index of a partitioned table, its scans, and every column it refers to: indkey for its keys and INCLUDE columns, and each :varattno in the node trees of its expressions and predicate. For a statement that writes, exec.rs and prepared.rs add WAL to EXPLAIN ANALYZE from 13. writes.rs in the core is pure: it reads a capture, the plan and the statement’s text.

• HOT. heap_update (heapam.c) makes an update HOT when the new version fits on the page of the old one and no column of RelationGetIndexAttrBitmap changed. From 16, columns that only summarizing indexes (BRIN) refer to do not count, though those indexes still get an entry, which the count of index entries leaves out. assigned_columns reads the columns a statement sets from its text: UPDATE … SET, ON CONFLICT … DO UPDATE SET and MERGE’s UPDATE SET, inside a WITH too, with (a, b) = … targets, and without being fooled by commas in CASE, function calls or literals. A column the statement sets may keep its value, which does not stop a HOT update, so a blocking index is named only when updates were in fact not HOT. When no index refers to a column it sets, the page had no room: the note gives the fillfactor and how many new versions went to another page.
• Index entries. Each row inserted and each update that is not HOT writes an entry in every index of its table; partial indexes make it at most that many.
• The proof. With --prove --allow-ddl, writes::prove drops the blocking indexes that can be dropped alone (not those enforcing a constraint, nor a partition’s index attached to its parent’s) in a transaction with lock_timeout = '2s', runs the statement there with its writes read, and rolls back.
• Tests. writes.rs covers the columns a statement sets, blocking indexes with BRIN before and from 16, the page-room note, wording and the proof. tests/render.rs snapshots the viewer’s overlay. crates/explainsql-db/tests/live.rs reads an update blocked by an index twice on one connection, the same update with the index dropped, an update of a column no index refers to, an insert, and checks that a query and an estimated plan write nothing. crates/explainsql/tests/cli.rs runs the report and the proof.

Plan diff

diff.rs compares two plans of the same statement: from explainsql diff, and for the viewer’s status line after a run in connected mode. It is pure, and takes plans from any source and in any format.

• Matching. Nodes are matched by the work they do, in three passes, each node at most once:

  1. Within the same scope (the main query, an InitPlan, a SubPlan, a CTE): a scan by what it reads and its alias, a join by the relations below it, any other node by its family (Gather and Gather Merge are one family, as are the two sorts, the two appends, and Aggregate with Group) and the relations below it. Relation names below a node have their numbers blanked out, so that an Append over pruned partitions still matches.
  2. Scans by their relation and scope alone: partitions get their aliases in plan order, which pruning and versions change (events_2025_06 in one plan, events_6 in the other).
  3. Anything left, wherever it is in the statement.

  When several nodes share a key, they match in plan order.

• Shapes (fingerprint::shape, fingerprint::id). One line per node: its type, join type, strategy, partial mode, parallelism, direction and relationship, the relation, index, CTE or function it reads with numbers blanked out, and the kinds of its conditions. Costs, rows, times, buffers, literal values and aliases are left out: the same plan has the same shape whatever the parameters, the data and the cache, in JSON or text (the corpus checks it on every scenario and version), and when PostgreSQL renames partitions. The id is the shape’s 64-bit FNV-1a hash.

• Changes. A matched pair of scans changed its access path when its type, index, direction or parallelism differ, or, for bitmap heap scans, the indexes of the bitmaps below; a pair of joins its method, join type or outer side; any other pair its operation (type, strategy, partial mode). Unmatched joins on both sides mean another join order. Unmatched nodes are added or removed, except those their parent’s change explains: bitmap index scans, and the Hash of a matched hash join. The same access change on several partitions, and partitions read or no longer read, are told once. Measured plans also compare temporary files (spills), misestimates of 10× or more where they start (not where they carry up the tree), and the work of matched nodes: a change over 10% in pages or time that moves at least 5% of the statement. Time alone, for the same pages, is reported with the pages read from disk: the cache or the load may explain it.

• Order and verdict. Structural changes come first, then the others, each by weight: the larger share of the statement’s time, pages or estimated cost its nodes take in either plan. The verdict is compare.rs’s comparison of the totals followed by the first structural change, or “the plan is the same” when the shapes are.

• Reports. Text, Markdown and JSON (report::diff_*): the verdict, the shapes, the changes with their evidence, and the plan after with changed nodes marked ~ and new ones +. The JSON report is the diff with the label of every node of both plans.

• Tests. diff.rs covers each kind of change on small plans. tests/diff.rs checks that every corpus plan matches itself and its other format node for node, that scans find their relation in another version, that any two plans compare, and what changed from PostgreSQL 12 to 18 in three scenarios; tests/report.rs snapshots the text and Markdown reports, and tests/cli.rs runs explainsql diff.

Plans over time: server logs

explainsql logs reads the plans auto_explain logged and tells, for each statement, which plans it got and when its plan changed. Reading is in pg/log.rs, the analysis in timeline.rs, both pure; the binary filters and prints.

• Entries (pg::parse_log). One pass over a jsonlog, a csvlog or a stderr log finds every auto_explain message (duration: … ms plan:) and what the log says about it:

  • jsonlog and csvlog: the record’s fields (time, process, user, database, application, query id);
  • stderr: the line prefix, read for a time, a [pid], user@db or user=,db=,app=;
  • the duration, kept in thousandths of a millisecond so that entries compare exactly;
  • auto_explain’s Query Text: and, from PostgreSQL 16, its Query Parameters: line (a key of JSON plans).

  The plan itself goes through the parsers like any other. An entry whose plan cannot be read is a warning on its line. The robustness tests feed the captured logs cut and mangled.

• Statements. Entries are ordered by time, as the log prints it; the time zone is left out, as a log’s entries share one. Entries are grouped by the query identifier of the plan, or else of the log record: for an EXECUTE, the record’s identifier is that of the EXECUTE, the plan’s that of the prepared query. Without one, entries are grouped by the text: comments, a leading PREPARE name (types) AS, literal values and parameters left out, an IN list one ?.

• Plan changes. A statement’s entries form runs of the same shape. Where one run ends and another begins, diff.rs compares the last plan of the one with the first of the other. The change also records the runs’ median durations, whether another process ran the plan after, and whether the plan after is a generic plan: one that keeps the parameters ($1) of a parameterized statement, the plan before not. A statement with four runs or more, and more than twice as many runs as plans, alternates.

• Order. Statements whose plan changed come first, by what their costliest change added: the median duration after minus before, times the runs after. The others follow by their total time.

• sqlcommenter (timeline::tags). The tags of the last comment that holds only key='value' pairs are decoded (percent-encoding, \'). The statement keeps them, the trace context left out. --trace matches the trace id of traceparent.

• Reports (report::logs_*). Text and Markdown: each statement with its plans and changes, and for a switch to a generic plan, the --params and --bind command that tests it. JSON: the timeline, and every entry with what the log says, its shape and its trace, without the plans.

• Tests. fixtures/logs/ holds a real session, logged by PostgreSQL 16 in the three formats at once (see its README): an index dropped by a migration, a stable report, and a prepared statement that switches to its generic plan. tests/logs.rs checks that the three formats give the same entries and timeline. timeline.rs covers texts, tags and times on small inputs, tests/report.rs snapshots the reports, and tests/cli.rs runs the filters.

Requests and loops

explainsql requests groups the statements of server logs into requests and finds the loops in them. Reading is in pg/log.rs, the analysis in requests.rs, both pure; the binary measures in connected mode and prints.

• Statements (pg::parse_statements). One pass over a jsonlog, a csvlog or a stderr log reads the messages of statement logging: duration: … ms statement: …, execute <name>: …, and log_statement’s lines without a duration, whose duration: line comes after. The parse and bind steps of the extended query protocol add their durations to the execution they precede, in the same session. Values come from the parameters: detail: the record’s field, or in stderr the DETAIL line of the same process. Each message keeps the session (%c, session_id) and the virtual transaction (%v, vxid), unless that says no transaction was open (3/0, as a statement that committed on its own logs it). The binary falls back on auto_explain entries, which carry the statement and its values too.
• Requests (requests::profile). Statements are ordered by time. Those with a traceparent tag go together by its trace id, across sessions. The others, per session: a transaction (a virtual transaction id seen more than once) is a request, which takes the COMMIT that logs no transaction after it; statements outside one go together while the session was idle no longer than the gap, from the end of one (its log time) to the start of the next (its log time less its duration).
• Loops. In each request, statements are grouped by their text without comments, literals and parameters, as timeline::normalize writes it. A group of min_runs or more that is not transaction control or a setting is a loop; across requests, loops of the same text are one. It is a LOOP when the values changed in some request, else a REPEAT. The example is the request with the most runs. Runs whose log has no values for their parameters cannot be called a repeat: they are a LOOP that is not batched. For statements with their values written in, the literals that changed become $1, $2, … in the statement that will be prepared, the others stay; runs whose literal lists differ in length (IN (…)) cannot be lined up, and a value that changed written as E'…' or B'…' is not read back.
• The batched statement (requests::batch). A small tokenizer (words, $n, literals, comments, symbols, with the depth of parentheses) reads the statement, and its comments are left out. = ANY($n::type[]) replaces = $n (or IN ($n)) when one parameter changed, once, as a term of the statement’s own WHERE: right after the WHERE, an AND or an OR, followed by the next one, ORDER BY, FOR, RETURNING or the end, with nothing applied to either side (no NOT, cast or operator). That holds in a SELECT with no LIMIT, OFFSET, FETCH, GROUP BY, HAVING, SELECT DISTINCT, window, set operation or aggregate, and in an UPDATE or DELETE, after a WITH too; then the statement finds the rows of all the runs. Otherwise a SELECT goes in CROSS JOIN LATERAL (…) over unnest($n::type[], …), each parameter that changed replaced by its column of batch, so that each value keeps its own rows; with several, the arrays line up run by run, and a run that repeats another’s values is left out. A name right before a value is its type (date '…'), so that value cannot become a parameter. An INSERT gets advice instead, and an UPDATE or DELETE that does not fit = ANY is left to be written by hand.
• Proof (binary, requests.rs). It asks the parameters’ types (PREPARE, pg_prepared_statements), runs the batched statement with Cache::Custom and the values as array literals (measure_prepared), then up to 20 runs one by one (explain_prepared, Mode::Analyze), all rolled back. requests::proof sums the runs’ planning and execution times and pages, scaled to every run, against the median of the batched runs, and adds the round trips, timed with SELECT 1. The foreign key comes from the column the generic plan compares the parameter with (params::parameters) and pg_constraint (Database::references); requests::reference prefers the key whose other table the statement before the loop reads.
• Reports (report::requests_*). Text and Markdown: each loop with its example request, the batched statement, the proof, the foreign key and the advice. JSON: the requests, the loops and every statement with what the log says, without the values of parameters.
• Tests. fixtures/requests/ holds a real session logged by PostgreSQL 16 in the three formats at once (see its README). pg/log.rs checks that the three give the same statements, values, sessions and durations; requests.rs covers grouping, loops, the tokenizer and the batched forms on small inputs; tests/cli.rs checks that the three formats give the same report and, against the fixture database, the measured proof and the foreign key; tests/live.rs reads foreign keys and round trips.

The costliest statements: pg_stat_statements

explainsql top reads pg_stat_statements for the current database in a READ ONLY transaction that is rolled back, ordered by total execution time, with calls, mean time, shared pages hit and read, and temporary pages written. It first checks that the library is loaded and the extension created, and says which is missing. top.rs in the core is pure: it decides which rows can be planned and why not (a utility command, a text pg_stat_statements hides from roles without pg_read_all_stats, or one cut at track_activity_query_size), and formats the list in text, Markdown and JSON.

In a terminal, the list is part of explainsql-tui (top.rs). Enter plans the selected statement without running it: with EXPLAIN (GENERIC_PLAN) from PostgreSQL 16 when it has $n parameters, as pg_stat_statements writes its constants; before 16, or with p, the statement goes through the parameter analysis of Statements with parameters. The viewer opens on the result and q returns to the list. Tests: top.rs covers the rows on small inputs, and crates/explainsql-db/tests/live.rs reads the view, including as a role that cannot see other roles’ texts (EXPLAINSQL_TEST_READER_URL).

Anonymized plans

anonymize.rs replaces what a plan tells about the schema and the data, so that it can be shared: the names of tables, indexes, CTEs, aliases, schemas, columns, constraints and triggers, and literal values. Each name gets a replacement by its kind (table_a, index_a, column_a, …), and each literal one of its form ('value_a', a LIKE pattern keeping its % at either end, other numbers), the same way everywhere in the input. Names that differ only in their numbers keep differing only in their numbers (table_b_1, table_b_2), because the viewer’s folding, diff and plan shapes group partitions by their names with the numbers blanked out, and the anonymized plan must group and match as the original does.

It works on the plans themselves, JSON or text, after normalize() has removed their wrappers, and rewrites names in node properties, in conditions and in the query text. Function and type names, keywords, $n parameters and system names are kept. A property or line it does not know has every name and value in it replaced. Finally the result is parsed again and must have the same nodes as the input, or nothing is printed. tests/anonymize.rs runs it on every corpus plan and every captured input form: each must read back with the same nodes, and nothing it named may be left.

Checks in CI

explainsql check is a gate: it exits with 0 when every plan passed, 1 when one failed and 2 when it could not run, and says why in text, Markdown, JSON or SARIF.

• Core (check.rs, pure). check() takes a plan, its analysis and its locked plan, if any, under a Policy: fail_on, a severity, and strict. A plan fails on a finding at least as severe as fail_on; on being worse than its locked plan as compare.rs judges it, when pages, temporary files or, for two estimated plans, the cost decided; and, under strict, on any change of shape. Time alone never fails a plan: for the same pages it changes with the cache and the runner’s load, which in CI is noise; it is a note, like a plan that changed and is not worse. Without a locked plan, a plan is new.
• The lock (check::Lock). Pretty JSON, sorted by name, versioned: for each name, the plan’s shape id, pages and estimated cost, and the plan as captured, JSON as JSON and anything else as text, so that diff.rs can compare with it and a change reads well in a review. --update writes the plans checked and keeps the others. Names are paths from the lock file’s directory, with /.
• Binary (check.rs). It collects the files (directories are searched in order: *.sql with -d, *.json and *.txt without), reads or runs each one as connected mode does, with the advice checked against the catalog, checks it, and with --prove tests the suggested indexes of the plans that failed. A file it cannot read or run is reported on standard error and makes the exit code 2, after the others are checked.
• Reports (report::check_*). Text: one line per plan with its shape, then why it failed, notes and fixes. Markdown: a table, and for each plan that failed or changed, its diff folded under <details>. JSON: every check with its findings and advice. SARIF 2.1.0: the rules and two more, plan-worse and plan-changed; each finding is a result on its file, an error when it fails the plan and otherwise a warning or a note by severity.
• Tests. check.rs covers the policy and the lock; tests/report.rs snapshots the text and Markdown reports; tests/cli.rs runs a plan from new to locked to worse, every format, the exit codes, and the same against the fixture database with --prove.
• The GitHub Action (action.yml, a composite action, with its scripts in action/). install.sh puts a binary on the runner: the binary input as it is, or the release named by version, or the one the action was referenced by (github.action_ref, such as v0.3.0), or the latest. check.sh runs explainsql check with --format md and --sarif, keeps both reports in a directory of the run’s own, and lets the step pass so that the comment can still be written; the exit code goes to the outputs, and the last step fails with it. comment.sh finds the pull request’s comment by the hidden marker (with comment-key in it) and updates it in place: a failed check posts or updates it, a passing one only updates a comment already there. Pull requests from forks get no comment, since their token cannot write one. The scripts are checked by shellcheck and actionlint in CI, and the action workflow runs the action on the plans in action/test/: one that matches its lock and passes, and one whose index scan became a sequential scan and fails.

Releases and documentation

• Release pipeline (.github/workflows/release.yml). We chose a hand-written workflow over cargo-dist, for three reasons: the smoke tests and dry runs stay fully under our control, development needs no extra tool, and without a Homebrew tap cargo-dist’s main extra is not needed. The workflow builds:

  • x86_64 and aarch64 Linux, static with musl (aarch64 through cargo-zigbuild);
  • x86_64 and aarch64 macOS;
  • x86_64 Windows.

  install/package.sh puts each binary in an archive with the README, the changelog, the licenses and a SHA-256 checksum. Archive names carry no version, so releases/latest/download/… links always work.

• Smoke tests. Each archive is installed on a clean runner with install.sh or install.ps1, from the downloaded artifacts only. Then install/smoke.sh or smoke.ps1 runs:

  • --version;
  • --demo;
  • a JSON report of a plan file;
  • a psql table on standard input;
  • --pager pass-through;
  • the exit code for input that is not a plan.

  Further checks:

  • a tampered checksum is refused;
  • Linux binaries are static, and the aarch64 one runs under QEMU;
  • on fresh Alpine and Ubuntu containers, installing and running --demo takes under a minute (download time aside).

• When it runs. A v* tag that matches the version in Cargo.toml publishes a GitHub release: the archives, SHA256SUMS, both install scripts and the changelog’s section as notes. A manual run, or a branch push that changes the pipeline, is a dry run that publishes nothing.

• crates.io. The four crates are published together: explainsql-core, explainsql-db, explainsql-tui and the explainsql binary. Each package holds only its sources, a README and the licenses; tests stay out, as they read the corpus outside the crate. The binary’s README is the repository’s, so its links are absolute. CI packages and builds every crate as crates.io would (cargo publish --workspace --dry-run) on every push. On a release tag, the release workflow publishes them in dependency order through Trusted Publishing: crates.io trusts the workflow’s OIDC token, and no token is stored. Versions already published are skipped. RELEASING.md has the steps.

• Documentation site (docs/, mdBook): getting started, a guide chapter per feature, the command-line reference, troubleshooting, the rule catalog with one page per rule, the design documents and the contributing guide. Findings link to their rule’s page (Rule::doc_url):

  • in the viewer’s details;
  • in Markdown reports;
  • as rule.docs in JSON.

  cargo xtask rule-docs writes each rule page’s example: the scenario that shows the rule best, its plan and explainsql’s finding. cargo xtask check-links checks every relative link and anchor, and keeps site pages from linking outside docs/. The docs workflow runs both checks and builds the site on every push. It deploys to GitHub Pages only when run by hand or on a release tag, once Pages is enabled in the repository settings.

• Demo. The README’s demo is a recording of a real session. cargo xtask demo --record builds the release binary and runs it in a tmux pane against the database named by EXPLAINSQL_TEST_DATABASE_URL (the fixture schema, without HypoPG, so that t builds the index in a rolled-back transaction and measures it). It types the query and a scripted sequence of keys (the verdict, the slowest node, why not, the advice, the measured proof, the locks and the help), waits for each result, and saves every screen as tmux shows it, colors included, to xtask/demo/recording.json. cargo xtask demo draws the animated SVG (docs/demo.svg) from that recording: drawing needs no database and gives the same SVG every time, so CI checks it is current.

Contributing

Thank you for wanting to help. This page explains how the project is put together, how to build and test it, and how to make the most common kinds of change. The architecture goes deeper into the design.

The shape of the code

ExplainSQL is a Rust workspace with four crates:

Besides the crates, fixtures/ holds the test corpus, xtask/ the project’s own tasks, fuzz/ the fuzz targets, tools/cross-check/ a comparison with other tools, install/ the install scripts and release packaging, action/ the GitHub Action’s scripts, and docs/ this site.

Build and test

You need Rust 1.85 or later.

cargo build                           # the debug binary, in target/debug/explainsql
cargo run -p explainsql -- --demo     # the viewer on the sample plan
cargo test --workspace                # every test
cargo fmt --all                       # formatting
cargo clippy --workspace --all-targets -- -D warnings

CI runs formatting, clippy with warnings as errors, actionlint and shellcheck on the workflows and scripts, the tests on Linux, macOS and Windows, the connected-mode tests against PostgreSQL 16, and a cargo publish --dry-run of every crate. Run the first three locally before you push, and you will rarely be surprised.

The test database

Most tests work on captured plans and need nothing else. The tests of connected mode run against a real database named by EXPLAINSQL_TEST_DATABASE_URL, and are skipped when it is not set. The database needs the fixture schema, and for the full set, HypoPG and pg_stat_statements:

createdb explainsql
psql -d explainsql -v ON_ERROR_STOP=1 -f fixtures/schema.sql
psql -d explainsql -c "CREATE EXTENSION hypopg"               # postgresql-16-hypopg on Debian and Ubuntu
psql -d explainsql -c "CREATE EXTENSION pg_stat_statements"   # with shared_preload_libraries = 'pg_stat_statements'
psql -d explainsql -c "CREATE ROLE explainsql_reader LOGIN PASSWORD 'reader'"

export EXPLAINSQL_TEST_DATABASE_URL=postgresql://postgres@localhost/explainsql
export EXPLAINSQL_TEST_READER_URL=postgresql://explainsql_reader:reader@localhost/explainsql
cargo test -p explainsql-db -p explainsql -- --test-threads=1

The reader role has no pg_read_all_stats, which lets the tests check how top handles statements it is not allowed to see. Run these tests one at a time: building an index in one test waits for the locks of another test’s rolled-back update.

The fixture corpus

fixtures/ holds real EXPLAIN output from PostgreSQL 12 to 18: 65 scenarios, each captured in JSON and text on every version. The parsers, metrics, rules and advisor are all tested against it. Each scenario is one SQL file whose header says what the plan demonstrates, which rules must fire (and no others may), and what the advisor must conclude. The fixture README describes the format.

Regenerating the corpus needs Docker:

cargo xtask gen-fixtures                               # every version, every scenario
cargo xtask gen-fixtures --versions 18 --only seq_scan_selective
cargo xtask check-fixtures                             # also part of cargo test

Plans in fixtures/pg/ are never edited by hand.

Adding or changing a rule

Rules are the easiest way to contribute, by design: one rule is one file, a scenario and a page.

1. Write the rule in crates/explainsql-core/src/rules/esNNN_name.rs, with its thresholds as named constants, and register it in rules/mod.rs. Say in the code when it deliberately stays silent: precision matters more than recall.
2. Add a scenario in fixtures/scenarios/ whose plan triggers it, and regenerate that scenario on every version. Check that no other scenario starts firing it by mistake: cargo test will tell you.
3. Write its page in docs/rules/ESNNN.md, following the others (signal, evidence, action, when it stays silent), add it to the catalog and to docs/SUMMARY.md, then run cargo xtask rule-docs to fill in the example from the corpus.

The documentation

This site is built with mdBook from docs/:

mdbook build docs            # into target/book
mdbook serve docs --open     # with live reload
cargo xtask rule-docs        # refresh the examples on the rule pages
cargo xtask check-links      # every relative link and anchor resolves

Pages inside docs/ must not link outside it, because the site does not contain the rest of the repository; link to the file on GitHub instead. The Docs workflow checks the rule pages, the demo and the links, and builds the site on every push. It deploys to GitHub Pages when run by hand or on a release tag.

When you change behavior, update the guide chapter that describes it, the command-line reference if an option changed, and the changelog.

The demo

The animated demo at the top of the README is a recording of a real session, not a mock-up. cargo xtask demo --record builds the release binary and runs it in a tmux pane against the database named by EXPLAINSQL_TEST_DATABASE_URL. It types the query and a scripted sequence of keys, waits for each screen, and saves every screen with its colors to xtask/demo/recording.json. The database needs the fixture schema without HypoPG, because the demo shows the index being built and measured in a rolled-back transaction.

cargo xtask demo then draws docs/demo.svg from that recording. Drawing needs no database and always gives the same SVG, so CI checks that the SVG is up to date with cargo xtask demo --check. The steps live in xtask/src/demo.rs; when the viewer’s screens change, record again.

Fuzzing

The parsers are fuzzed with cargo-fuzz, which needs a nightly toolchain:

mkdir -p fuzz/corpus/parse
cargo +nightly fuzz run parse fuzz/corpus/parse fixtures/pg/* fixtures/inputs
cargo +nightly fuzz run analyze fuzz/corpus/analyze fixtures/pg/* fixtures/inputs

parse runs the parsers; analyze also runs the analysis and every report. Neither may ever panic.

Comparing with other tools

tools/cross-check/ compares ExplainSQL’s exclusive times with those of pev2 and explain.depesz.com on 24 reference plans. See its README.

Releases

Releases are made by pushing a version tag. The release workflow builds every target, smoke-tests each archive on a clean runner, and publishes the GitHub release, the four crates on crates.io and the documentation site. Feature pull requests do not bump the version. RELEASING.md has the steps.

Licensing

Contributions are dual licensed under MIT and Apache-2.0, like the project, unless you explicitly state otherwise.