EXPLAIN ANALYZE

Add explain analyze for SELECT: run the plan to the end without printing its rows, count for every node the rows it handed up and the different pages it read, and print them after the estimates, with timings on one line that tests ignore.

Not run yet · 6 tests

Why this stage exists

explain shows what the planner intends. When a query is slow, the question is what happened: did the index scan find one row, as estimated, or a thousand? Did the filter throw away most of what the scan read? EXPLAIN ANALYZE answers it by running the plan and counting, node by node, and printing the counts beside the estimates.

It is also the best way to watch the previous stage at work. The stale-statistics trap is invisible in explain, which still shows a confident rows=1. Under explain analyze it is one line:

[PLAN]   └── IndexScan (table=t, index=by_v, key='x') (rows=1 cost=6) (actual rows=1000 pages=137)

What each node counts

Rows are what the node handed up to its parent. They tell you where rows are made and where they are thrown away:

explain analyze select id from t where id > 0 and v < 'c2'

Project (cols=[id])

↑ 112 rows

Filter (v < 'c2')

drops 888

↑ 1000 rows

ran

IndexScan (table=t, range: id > 0)

The scan reads the whole table, because id > 0 admits every key; the Filter keeps 112. The index on v is not used: a key bound wins by rule, so v < 'c2' stays in the Filter.

Pages are the different pages the node itself read from the buffer pool while it ran, counted once each, whether they were already in the pool or not. Only scans read pages; a Filter, a Project, a Sort or a Limit reads 0. An IndexOnlyScan reads only index pages, and an IndexScan the index pages plus the table pages of every row it looked up, which is why the two can differ by orders of magnitude for the same WHERE.

Early stops show. Under limit 5, the scan hands up 5 rows, not the table: the Limit stopped asking. That is the same pull model you built in the Core Engine, now visible.

Timings go on their own line

Real EXPLAIN ANALYZE prints times per node, and that is often what you want. Times are never the same twice, so no test can read them, and droid's output rule says nothing a test reads may depend on timing. So the times go on one line, [TIME] ..., after the plan. Print anything you find useful there; the tests skip it.

Only SELECT

explain analyze runs the statement. For an INSERT, UPDATE or DELETE that would mean changing the data in order to explain it, which PostgreSQL does (and many people have learned the hard way). droid refuses: 01608, and nothing runs.

The algorithm in words

Instrument the plan

Every node gets two counters, filled while it runs.

1.Give each node a count of rows handed up and a set of the page numbers it read itself.
2.Each time a node hands a row to its parent, add one to its count.
3.Each time a scan asks the pool for a page, add the page number to that scan's set; operators that read no page keep an empty set.
4.Run the plan to the end, exactly as a SELECT would, but drop the rows instead of printing them.

Print it

The explain lines, with what happened appended.

1.Print the first line and each node line as explain does, estimates included when the table has statistics.
2.After each node, append (actual rows=A pages=P): A its count, P the size of its set.
3.Print one line starting with [TIME] and whatever timing you like.
4.Refuse anything but a SELECT with 01608, before running anything.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, section 61.

Input	Output
`explain analyze select * from t where v = 'c7';` (analysed)	`[PLAN] select * from t where v = 'c7'`, then `[PLAN] └── IndexScan (table=t, index=by_v, key='c7') (rows=1 cost=<C>) (actual rows=1 pages=<P>)`, then `[TIME] <anything>`
a node on a table without statistics	the node, then `(actual rows=<A> pages=<P>)`
a `Filter`, `Project`, `Sort`, `Limit` or aggregate	`pages=0`
`explain analyze insert ...`, `update ...`, `delete ...`	`[ERROR:01608] <message>`, and nothing runs

No result rows, no [EXEC] line.

What you should see when done

shell

$droid --db plans.db
Welcome to droid!
>create table t (id int, v varchar(10));
Create table statement executed successfully
>insert into t (id, v) values (1, 'a');
Insert statement executed successfully (1 rows)
>insert into t (id, v) values (2, 'b');
Insert statement executed successfully (1 rows)
>insert into t (id, v) values (3, 'a');
Insert statement executed successfully (1 rows)
>explain analyze select id from t where v = 'a';
[PLAN] select id from t where v = 'a'
[PLAN]   └── Project (cols=[id]) (actual rows=2 pages=0)
[PLAN]       └── Filter (v = 'a') (actual rows=2 pages=0)
[PLAN]           └── SeqScan (table=t) (actual rows=3 pages=1)
[TIME] 0.041 ms
>explain analyze delete from t;
[ERROR:01608] explain analyze takes a SELECT
>.exit
exiting.. good bye!

Common bugs in this stage

Printing the result rows. EXPLAIN ANALYZE runs the query but prints only the plan.
Counting a child's pages in its parent. Each node counts the pages it read itself; a Filter reads none.
Counting page requests instead of pages. A leaf read twice is one page.
Running the plan only partway. Run it as a SELECT would, to the end or until the Limit stops it.
Running DML. Refuse before executing anything.
Putting a timing in a node line. Times belong on the [TIME] line only.

Language notes

The page above is the same for every language. These are the only language-specific hints.

C: a small open-addressing set of uint32_t page numbers per node, or a bitmap of 1000 bits, since a file has at most 1000 pages.
C++: a std::unordered_set<uint32_t> per node, or std::bitset<1000>.
Rust: a HashSet<u32> per node, or a [u64; 16] bitmap.
Zig: std.StaticBitSet(1000) per node.

Loading starter code…

No run yet

6 tests wait for your first run

Run tests builds your code, drives the binary through every case for this stage, and shows expected and received output side by side here. Failing cases come first.