First LQO Release Finds the PostgreSQL Queries a Planner Setting Makes Faster

Every PostgreSQL database has a few queries the planner gets badly wrong. Not slow because they’re big. Slow because the planner guessed the wrong row count, picked a plan for ten rows, and got ten thousand. Finding them is usually an afternoon with EXPLAIN ANALYZE, one query at a time.

LQO 0.1 does that afternoon for a whole workload. Give it a server log or a file of queries, point it at a replica, and it runs each kind of query under PostgreSQL’s default plan and under a set of planner settings. Then it tells you which settings make which queries clearly faster, with the exact lines to use.

It’s one binary called lqo, free and open source under the Apache License 2.0, for Linux, macOS and Windows. It works with PostgreSQL 14 to 18, managed services included, and needs no extension.

What It Does

lqo advise --dsn "postgres://lqo@replica:5432/shop" postgresql.log

LQO reads the log, groups statements that differ only in their values into one shape, and tests the slowest 25 shapes. For each it plans the query under the default settings and under 13 hints, such as switching off nested loops or index scans. Hints that give the same plan are run once, so a query usually costs only a few distinct plans. Each plan gets a warm-up run and three timed runs.

A hint makes the report only if it was never more than 10% slower on any text tried, clearly faster on at least one, and at least 20% faster overall. The report shows the time before and after, what changed in the plan, and the SET LOCAL lines.

What It Doesn’t Do

It doesn’t touch your data. Every trial is an EXPLAIN ANALYZE inside a READ ONLY transaction, rolled back, with the hint applied by SET LOCAL. Writes aren’t run at all, and if one ever got past that check, the server would refuse it. By default LQO won’t even start on a primary server.

It doesn’t apply anything either. Version 0.1 is an advisor. You read the report and put the setting on the queries that need it, inside their own transaction. That’s on purpose: the same switch that rescues one query can slow down a hundred others, so it never belongs in postgresql.conf.

Try It in Two Minutes

The demo builds a small shop with two classic traps in it. One is a join on JSON attributes that the planner guesses match about five customers, when tens of thousands do:

lqo demo --dsn "$LQO_DSN"
lqo advise --allow-primary --dsn "$LQO_DSN" lqo-demo.sql

In the recorded run, that query went from 1,061 ms to 684 ms with nested loops switched off, averaged over its two texts. LQO also left alone a query where switching off index scans would have made one of its values more than 300 times slower, 0.08 ms to 28.7 ms. That’s the point: a suggestion you can’t trust is worse than none.

The quick start has the steps, and what a learned query optimizer is explains the idea behind it.

What’s Next

Next comes a proxy that sits between your app and PostgreSQL, learns from live traffic in shadow mode, and later applies proven settings itself and backs them out when they stop winning. The advisor stays as the safe way in.

Download LQO, or read the code on GitHub.