About

lqo.net is the home of LQO, a learned query optimizer for PostgreSQL. It finds the queries PostgreSQL’s planner gets wrong, and the planner setting that makes each one clearly faster, by trying them on a replica. It’s one small binary called lqo, with nothing to install on the database.

Why It Exists

PostgreSQL picks every plan from row counts it estimates. Most of the time the estimates are close enough. Sometimes they’re off by a factor of a thousand, usually because two columns are related in a way the statistics can’t see, and a plan built for ten rows runs for ten thousand. A query that should take 40 ms takes four seconds, and nobody notices until a page times out.

Fixing one by hand is a known routine: run EXPLAIN ANALYZE, spot the bad join, try enable_nestloop = off, time it, check it didn’t hurt other values. LQO does that routine for a whole workload, the same way every time, and only tells you about the fixes that held up.

How It’s Built

• It steers the planner, it doesn’t replace it. LQO only uses PostgreSQL’s own planner switches, such as enable_nestloop and enable_hashjoin. Every plan it suggests is one PostgreSQL already chose under some setting, so there’s nothing new to trust.
• It learns by running. Each kind of query runs under the default plan and under each setting, several times, on your data. A setting wins only when it’s never much slower on any text tried and clearly faster overall.
• It can’t write. Every trial is an EXPLAIN ANALYZE inside a READ ONLY transaction, with the setting applied by SET LOCAL, and is rolled back. Statements that write aren’t run at all.
• One binary. It’s written in Go with no dependencies, its own small PostgreSQL client included, and runs on Linux, macOS and Windows. It works with PostgreSQL 14 to 18, managed services included.
• Tested by trying to break it. The tests send writes past its own checks, interrupt it mid-trial, kill it while it saves, and build a database full of traps it has to find. The results are published with the code.

License

All the code is open source under the Apache License 2.0, on GitHub. You can use it, change it and build it into your own products.

Questions People Ask

Is it really free?

Yes. LQO is free to use, and the code is free to take under the Apache License 2.0.

Does it change my database?

No. Version 0.1 is an advisor. It reads your workload, runs trials that are read-only and rolled back, and prints a report. You decide what to apply.

Can I run it on production?

Point it at a replica, a restored backup or a staging copy. It runs each query several times, and some of the plans it tries are slow on purpose, so it refuses to run on a primary unless you add --allow-primary. Your data is safe either way; the cost on a primary is load.

Where does the workload come from?

A server log is best, because it says which queries are slow and how often they run. LQO reads PostgreSQL’s plain log, csvlog, jsonlog and auto_explain output. A file of SQL statements works too.

What does “learned” mean here?

That LQO decides from measurements on your data rather than from a cost model. Research systems use neural networks for this. Version 0.1 keeps it simple and fully explainable: it times each plan and applies a strict rule. The post about the name says more.

Is it connected with Bao?

No. Bao is a research system from MIT that showed, in 2021, that steering PostgreSQL’s planner with its own settings works better than replacing it. LQO follows that idea. It’s independent and contains no code from Bao.

Can I suggest something?

Please do. The contact page has the address.