EXPLAIN ANALYZE

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:

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.

Output contract for this stage

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

No result rows, no [EXEC] line.

What you should see when done

$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.