LQO Finds Faster PostgreSQL Plans for 35 of 94 Join Order Benchmark Query Shapes

The Join Order Benchmark is where query optimizers go to be embarrassed. It’s 113 queries over the IMDB movie database, real data with all its skew and correlated columns, and it was built to show how far a planner’s row estimates can drift from the truth. If a tool claims to find better plans, this is the first place anyone should look.

So LQO 0.1.0 ran it, start to finish, on a GitHub runner: 4 cores, 15 GB of memory, PostgreSQL 16 with 5 GB of shared buffers. The 113 queries fold into 94 query shapes, because some JOB variants differ only in their constants. Each shape ran under the default plan and under LQO’s 13 standard hints, with a warm-up run and three timed runs per plan. The whole thing took 75 minutes.

The Result

35 of the 94 shapes got a suggestion. The other 59 stayed on the default plan, because no hint passed the rule: never more than 10% slower on any text, clearly faster on at least one, and at least 20% faster overall. That’s most of the benchmark left alone, and that’s fine. PostgreSQL gets most of these queries right.

Where LQO did suggest something, the savings ran from 21% to 98%, with a median of 48%. One pass over every tested query took 172.6 seconds with the default plans and 77.6 seconds with the suggestions, 55% less.

That total leans on one query, so here it is without it. JOB 26c went from 59.2 seconds to 1.2 seconds. Take it out and the pass goes from 113.4 seconds to 76.4 seconds, 33% less. Still a third of the time, from switches PostgreSQL already has.

What the Winners Changed

The hints spread out more than you might guess:

The nested loop cases are the classic JOB story, and the same one as LQO’s demo. The planner thinks a filter keeps a handful of rows, builds a plan of index lookups that’s perfect for a handful, and then gets thousands. JOB 16b went from 11.3 seconds to 4.4 with nested loops off, and the 17 family, six queries in four shapes, all went the same way.

The parallel worker cases are the surprise. JOB 26c’s plan kept the same joins and scans; the only change was running it without parallel workers, and it went from 59 seconds to 1.2. JOB 30a went from 3.3 seconds to 0.55 the same way. On a 4-core machine, for these queries, splitting the work cost far more than it saved. On a server with more cores the answer could differ, which is exactly why LQO measures on your machine instead of guessing.

A few go the other direction. Four shapes, 27a, 27c, 33a and 33b, ran best with nested loops only, and five with hash joins off. No single switch fixes JOB. Each query wants its own, which is the whole argument for trying them per query.

What It Doesn’t Show

This is one machine, one run, one PostgreSQL version. Most JOB shapes have a single text, so the rule’s check across several values had little to work with here; on a real workload, with many values per shape, it does more. And 9 of the wins were about parallelism on 4 cores, which won’t carry over to every server.

What matters more than the 55%: every one of the 35 suggestions comes with the runs that earned it, in a report you can read. The full results, with every plan and every timing, are in test/results/job on GitHub, and the testing post covers the rest of what LQO was put through.