How LQO Was Tested, From Writes It Must Never Run to Traps It Must Find

A tool that runs your queries on a database has two jobs, and the first one matters more. It must never do harm: no writes, no settings left behind, no runaway query. Then it must actually find something. LQO’s tests go after both, against a real PostgreSQL server rather than a mock.

All 62 tests pass in the recorded run, on a two-core cloud machine with PostgreSQL 16. CI runs them again on every change against PostgreSQL 14 and 18, and runs the parts that need no server on macOS and Windows. The logs are on GitHub.

It Must Never Write

The first line of defence is a check on every statement before it runs: only reads get through. The tests feed it an INSERT, an UPDATE, a DELETE, a DELETE hidden inside a WITH, a SELECT INTO, a SELECT ... FOR UPDATE, a call to set_config and a TRUNCATE. Every one is reported as skipped, and the test checks the table’s row count and sums haven’t moved.

The second line is the server itself. Every trial runs inside BEGIN TRANSACTION READ ONLY and is rolled back. To prove that holds on its own, a test skips the check and sends writes straight to the trial runner: an UPDATE, a DELETE, and a call to a function that inserts a row. PostgreSQL refuses all three, and the data is the same afterwards.

It Must Leave Nothing Behind

Hints are applied with SET LOCAL, which ends with the transaction. A test runs a mix of trials, including one that fails on a missing table and one stopped by the time limit, then reads every setting LQO touches. All match what they were before, and statement_timeout, lock_timeout and the read-only flag are back to normal. LQO also checks this itself after every query shape, and stops if anything differs.

Two more tests cover stopping. A trial cancelled from the client stops within the test’s limit, and the connection still works afterwards. An interrupted run, the Ctrl-C case, stops in about half a second in the recorded run, marks its report as interrupted, and leaves the session clean.

The state file, where LQO keeps its findings, is replaced whole on every save. A test starts a process that saves a 500-shape file in a loop and kills it with SIGKILL, 40 times, at different moments. After every kill the file reads back whole: either the old one or the new one, never half of each.

It Must Never Suggest a Slower Plan

The rule is strict on purpose, and the tests check its edges. For a lookup by key, where the default plan is right, a test offers only harmful hints. LQO keeps the default, and the far slower plan is stopped at the time limit instead of running to the end. For a query with random() in it, whose rows change from run to run, no hint is suggested at all, because LQO can’t tell a faster plan from a different answer.

It Must Find the Trap

The demo database, the one lqo demo builds, is the benchmark. It has 200,000 customers, a million orders and three million order lines, built the same way every time, with traps PostgreSQL’s estimates fall into. The test builds it, runs the whole workload, and checks three things: that the JSON trap is found and fixed by switching off nested loops, with at least 20% saved; that the shape where a hint hurts one value isn’t touched; and that the key lookup and the UPDATE are left alone.

In the recorded run, the trap went from 1,061 ms to 684 ms per text, 36% less. A second trap, a LIMIT query that walks the wrong index, went from 35.1 ms to 24.7 ms without index scans. That one is close to the line: on some runs its timings overlap and LQO keeps the default plan instead, which is the rule doing its job. The shape with two values stayed on the default plan, and rightly: switching off index scans would have taken one of its values from 0.08 ms to 28.7 ms.

The Join Order Benchmark

The standard test for query optimizers is the Join Order Benchmark: 113 queries over the IMDB movie database, from the 2015 paper that showed how badly optimizers misjudge joins. Its data comes from CWI’s server, so LQO runs it in its own workflow on GitHub, loads the data into PostgreSQL 16 and puts every query through lqo advise. The reports land in test/results/job on GitHub.

In the recorded run, 35 of the 94 query shapes got a suggestion and 59 stayed on the default plan. The suggestions saved between 21% and 98%, and one pass over every tested query went from 172.6 seconds to 77.6. The benchmark post goes through what changed and what it doesn’t show.

The quick start runs the same demo on your own machine.