The tests passed. The plan didn't. | boringSQL
Table of Contents
Test the plan, not just the rows
An empty database is a liar
Gate on what happened, not what was guessed
Knowing when you can't tell
Testing the planner itself
Leave evidence
What "verified" means
TL;DR - RegreSQL 1.0 tested that your queries return the right rows. 2.0 tests that they return them the right way, and it does the checking against production's real statistics instead of your empty dev database, which lies.
A migration cleanup dropped an index nobody thought was load-bearing. Every test passed: same rows, same order, green. Three days later the API started timing out on a query that hadn't changed a character, because the planner had quietly switched it from an index scan to a sequential scan over a table that had kept growing.
The first version of RegreSQL would have passed that change too. It tests what your queries return: run them, diff the rows against a committed expected file, go red when the output changes. That catches the query that now returns the wrong rows. It says nothing about the query that returns the right rows the wrong way, which is most of what takes a database down.
Version 2.0 tests that.
Test the plan, not just the rows
Here is that failure on a laptop. An orders table, an index on customer_id, and a query that reads one customer's orders:
-- orders-by-customer.sql<br>select id, total<br>from orders<br>where customer_id = :cid<br>order by id;<br>Baseline it and run the tests. Green:
$ regresql test<br>✓ 2 passing<br>Now drop the index the way that migration did, and run the same tests again:
$ regresql test<br>✓ 1 passing<br>✗ 1 failing
FAILING:<br>orders-by-customer.1.buffers (3898 > 109 * 102%, +3476.1%)<br>Expected buffers: 109<br>Actual buffers: 3898 (+3476.1%)
Cost (info): 7962.41 (baseline: 421.03)
⚠️ Table 'orders': Bitmap Heap Scan → Seq Scan<br>Here is the whole loop, start to failure, as it actually runs:
The output check still passes; the rows didn't change. The plan check catches what the output check can't: the same query, returning the same result, now runs a sequential scan instead of the bitmap index scan it used before, reading thirty-five times the buffers. That failure blocks the merge. And the diff names the table and the exact change, in the same shape as the row diffs you already read in code review. Plan drift stops being something you find in production and becomes something you review in a pull request.
An empty database is a liar
That demo caught the regression because the table was big enough for the index to matter. On your dev database, it usually isn't. Run EXPLAIN there and it says every query is fine, and it isn't lying on purpose: at a few hundred rows the cheapest plan really is a sequential scan, and it picks it. Production's planner reads different statistics and picks a different plan. A green suite on small data proves your queries are correct on small data, and nothing about the plan production will run. That gap, and why code review can't see it, is a story of its own.
You can't copy production data to your laptop. You don't need to. You need what the planner reads: the row counts, the histograms, the most-common-values. PostgreSQL 18 dumps and loads those on their own (pg_dump --statistics-only), and RegreSQL 2.0 injects them so EXPLAIN, against ten rows on your laptop, sees the real table's distribution:
regresql test --stats production-stats.sql<br>Now the plan you baseline is the plan production would pick, and a plan regression fails on your laptop before it reaches a server. That is the difference between testing your query and testing it against reality.
Gate on what happened, not what was guessed
Cost estimates are the planner's opinion. Opinions drift, and they drift most exactly when a plan is about to go wrong, because a bad plan is usually a bad estimate first. Gating a test on estimated cost means trusting the number that breaks first to tell you when it broke.
So 2.0 gates on measured behavior instead. regresql baseline --analyze runs the query for real and records what happened: buffers actually read, whether a sort or hash spilled to disk, how many rows actually flowed through each node, and the cardinality error, the ratio between what the planner predicted and what it got (the "q-error"). A query whose estimates were off by 2x and are now off by 200x has a plan that is one data change away from collapse, and the row counts say so long before the cost does. The baseline stores actuals; the test compares actuals; the estimate is never on the witness stand.
Wall-clock time is the actual everyone wants and the one that lies most, because it moves with cache state, whatever else is on the box, and luck. 2.0 measures it and treats it as evidence, not a verdict: interleaved runs, a per-query median, and a noise threshold derived by permuting the samples, so a query counts as slower only when it beats its own measurement noise. Anything that can't clear the threshold...