Are they doing the same work?

01 / Define success first

Write the answer before timing it

A comparison contract is a short agreement about the work each option must complete. It names the input, output, resources, permitted tuning and guarantees. “Run a revenue query” is too loose: different month boundaries, missing amounts and duplicate values can all change the answer.

For this exercise, January means month_id = 1. Every row is a separate order. An unknown amount, represented by SQL NULL, still counts as an order and contributes zero cents to the total. Repeated amounts remain separate purchases. Return the region, order count and revenue in integer cents; sort the result by region. Integer cents keep this example free of currency rounding differences.

An index is an extra structure that can help find selected rows. Building it costs time and memory. Equal index statements are a documented tuning policy; they do not force the engines to use identical physical plans. A different customer question may permit each option's best supported configuration under equal cost or resource limits. Choose that policy before seeing the winner.

02 / A worked example

Three small changes, one wrong report

Our eight orders include two separate purchases for 100 cents, an unknown amount, a zero amount, a refund of −100 cents, and rows outside January. An edge case is an input that tests a boundary or an unusual but permitted value. Small fixtures make mistakes easy to inspect before scaling up.

Work through January by hand. Region 1 has orders 1, 2 and 8: 3 orders, 450 cents. Region 2 has orders 3 and 4: 2 orders, 0 cents. Those two rows are our expected answer, computed independently of either database.

-- The colleague's candidate
SELECT region_id, COUNT(amount_cents),
       COALESCE(SUM(DISTINCT amount_cents), 0)
FROM orders
WHERE month_id <= 2
GROUP BY region_id ORDER BY region_id;

COUNT(amount_cents) counts known amounts, so it omits the unknown one. DISTINCT removes repeated monetary values before adding, even when they belong to separate orders. The month condition includes February. COALESCE(value, 0) substitutes zero when an aggregate is unknown; it does not repair the other errors.

The candidate returns region 1 as 5 orders and 950 cents, and region 2 as 1 order and 0 cents. A report can therefore look plausible and still answer another question. The correct query uses COUNT(*), ordinary SUM(amount_cents), and month_id = 1.

03 / Execute the contract

Catch the wrong work, then repair it

Run the candidate first. The result should fail in both engines regardless of which takes less time. Then select the repaired query and run again. Optionally add the month index to both options and inspect its preparation time separately. Predicting is optional; you can also read the complete SQL before running.

CREATE TABLE orders (order_id INTEGER, month_id INTEGER,
 region_id INTEGER, amount_cents INTEGER);
-- All eight rows are shown above.
-- Expected rows: [1, 3, 450], [2, 2, 0].
-- The repaired query counts every row and filters month_id = 1.

04 / Read the evidence

Correctness is an admission rule

A failed answer is excluded from a speed claim about the agreed work. It is not a slower answer with a penalty attached: it is different work. The repaired run supports only the stated fixture and contract. Eight rows are enough to expose these defects; they are too few to evaluate an index's performance value or realistic query scaling.

For a server purchase, ask when a write is acknowledged, how replicas participate, what consistency a reader sees, and which failure the design must survive. Two systems can return the same rows today while offering different recovery behaviour. Test those guarantees in the intended deployment before using performance to choose between them.

05 / Independent practice

Design an adversarial fixture

Your next customer asks for “orders paid in January.” Before adding a timing chart to a proposal, write a five-line contract and invent three rows that would reveal an incorrect implementation. Choose at least one case absent from this fixture: an empty month, a missing region, two currencies, a timestamp at midnight, or repeated order identities. State the required handling before calling either query correct.

1. Download the failing record and repaired record; explain the exact answer difference.
2. Write expected rows for your three new inputs by hand. Explain why each row could change the recommendation.
3. Choose a tuning policy and a required service guarantee. Separate a directly measured fact from a guarantee this browser cannot establish.

To take the next programming step, open the source and change EDGE_ROWS in src/methods-model.ts, then update fairReference() if your contract changes. A reference is a simple independent implementation of the expected result. Keep it readable enough to review by hand. Follow the repository's reproduction instructions and run your edited fixture locally. For timestamps or currency, extend the schema and both SQL and reference; changing just the displayed labels does not test that contract.

Draft and selected controls save on this device when local storage is available. Notes join the completed evidence at download time.

06 / Sources and reproducibility

Make the contract inspectable

The exercise uses the same pinned local engine resources as Chapter 2. Request timing includes dispatch, worker scheduling, engine execution, conversion to plain string rows and response delivery. Answer comparison happens after the clock. Startup, loading, optional index creation and ANALYZE (gathering query-planner statistics) are recorded separately. Stop, a hidden tab, worker errors or request time limits leave an incomplete record with its error visible.

• Postgres aggregate functions explains null handling and counting.
• DuckDB aggregate functions documents aggregation semantics.
• Postgres reliability and write-ahead logging introduces failure guarantees beyond this read lab.
• Method, source locations and reproduction commands; engine source and licence notices.

Next, keep the answer contract and ask whether these inputs resemble the customer's real work.