Query Planner

The problem

When a database query is slow, the usual advice is to add an index. Often that isn't the problem. Before running a query, a database estimates how many rows each step will produce, using a small statistical summary of the table. When those estimates are wrong, it confidently picks a slow plan. Nothing is broken and no index is missing. The plan was chosen correctly from a false belief.

A common cause is the independence assumption. Asked for rows where the city is Balikpapan and the province is Kalimantan Timur, a database multiplies the two odds as if the city said nothing about the province. It predicts one row in ten thousand. The truth is one in a hundred.

The standard tool, EXPLAIN, shows only the winning plan. It hides the candidates that lost and the gap between belief and reality. I built Query Planner for backend and data engineers, database administrators, and anyone teaching or learning query optimisation.

What I built

I wrote a whole database engine that runs in the browser

Wrapping an existing engine would have been far less work. But no existing engine shows the plans it threw away, or puts its estimate next to the true count at every step. Only an engine I control can do both. So the app has its own query parser, table storage, index, statistics, cost model, plan search and executor with nine operators. Operator names match Postgres, so what people learn here carries over to real EXPLAIN output.

The planner is not allowed to see the data

A planner that can peek at the real rows isn't planning. I made the planner code unable to import the storage or executor code, and the linter fails the build if anyone tries. The planner reads statistics and nothing else. Every estimate also carries its own explanation: which method it used, its inputs and its assumptions. The screen draws that explanation directly, so the display can't drift from the maths.

I keep every losing plan

The classic plan search throws away a candidate as soon as a cheaper one appears. I keep every candidate, plus the order in which the search filled them in. For eight tables that's 255 cells and very little memory. It is what lets the animation replay the real search. I also keep plans that are only best because of their sort order. Without them, merge join can never win, and the app would teach something false.

I checked it against real Postgres

A copy of Postgres compiled to run in the browser serves as the test reference. The same data goes into both engines, and tests check that my planner picks the same plan shape. Where the two disagree, I documented the cause in the app instead of tuning it away. A separate test checks that every possible plan for a query returns the same result, which is what catches executor bugs.

A data detail almost hid the effect

My realistic Indonesian dataset listed cities grouped by province. The generator picks cities by list position, so that grouping shrank the independence error from about 30× to 3×. I re-ordered the list by population, which is also more realistic, and the effect came back.

Result

Query Planner is live and public. Drag the storage cost setting from 4.0 toward 1.1, the usual adjustment for SSDs, and the cost bars cross as the winning plan changes. A four-table join re-plans and re-runs in under 16 milliseconds, one frame at 60 frames per second. The plan tree shows the estimate beside the real count and the error growing: 1.2× at a scan, 8× after one join, 300× at the top.

A correlation chart draws the wrong assumption as a rectangle away from the real data. One click adds a multi-column statistic, and the estimate snaps to the truth. Nothing leaves the browser, and any plan can be shared as a link. I built it in one session of about 16.5 hours on 3 September 2026.