Some PostgreSQL queries are slow without being big. The planner picks plans from estimated row counts, and those estimates can be off by a factor of a thousand, often because two related columns are invisible to the statistics. A query that should take 40 milliseconds takes four seconds. A good DBA knows the fix routine: run EXPLAIN ANALYZE, find the bad join, try enable_nestloop = off, time it, and check nothing else got worse. One query at a time.
LQO, a learned query optimizer for PostgreSQL, does that routine across a whole workload. It finds queries the planner handles badly, tests the planner’s own settings on a replica, and keeps only the ones that clearly win. You read the report, or let LQO apply the proven settings to live queries. Every trial is read-only and rolled back. It’s one binary, with no extension to install.
The Pitch in Four Numbers
- 2,414 ms to 1,038 ms, a 57% cut, on the demo’s trap query with nested loops switched off.
- 35 of 94 Join Order Benchmark query shapes got a faster plan, from 113 queries over the IMDB dataset.
- 0 writes got past LQO’s checks in the tests.
- PostgreSQL 14 to 18, managed services included, with no dependencies and no extension.
How It Works
LQO steers the planner. It doesn’t replace it. It only uses PostgreSQL’s own switches, like enable_nestloop and enable_hashjoin, so every plan it suggests is one PostgreSQL would produce under some setting anyway. Nothing exotic reaches your database.
It learns by measuring. Each query shape runs under the default plan and under each setting, several times, on your data. A setting is adopted only if it’s never much slower on any text it was tested on and clearly faster overall. Trials run as EXPLAIN ANALYZE inside a read-only transaction, with the setting applied by SET LOCAL, then rolled back. Writing statements aren’t run.
lqo advise reads a workload, server logs in several formats, a SQL file or pg_stat_statements alone, runs the trials and prints a report. lqo serve sits in front as a proxy. By default it passes queries through untouched and learns on a replica, choosing what to test with Thompson sampling. In active mode it applies proven settings to live queries, one statement at a time, keeps checking them against calls run without the setting, and drops any that start running slower. lqo switch off turns everything off at once. It also works inside application transactions, which matters for ORMs like Django, Rails and Hibernate, and it can correct row estimates where one switch isn’t enough.
In the demo run, two queries improved with a hint, three were better left on the default plan, and one UPDATE was skipped. Four of the benchmark queries each needed a different setting, which is exactly why doing this by hand is slow.
Who It’s For
- PostgreSQL operators and DBAs chasing slow queries the planner keeps getting wrong.
- Developers on ORMs who can’t easily rewrite the SQL their framework produces.
- Teams on managed databases, where logs are hard to get at and extensions are restricted.
- Anyone with a replica or staging copy who wants proven fixes before touching production.
Limits
LQO refuses to run on a primary unless you say --allow-primary, because some plans it tests are deliberately slow and that costs load. A replica, restored backup or staging copy is the right target. Its suggestions are limited to what PostgreSQL’s own settings can produce, and by default it changes no data and no configuration. It’s independent of Bao, the 2021 MIT research system that showed steering the planner beats replacing it, and contains none of its code.
The Four-Question Screening
Every project featured here gets the same four questions, the way you’d screen a startup idea.
1. Where is the money in it?
LQO is free under Apache 2.0, with no paid tier. Slow queries cost money in database bills and in DBA time, and PostgreSQL is everywhere. The paid paths are a hosted service that watches a workload continuously and reports wins, an enterprise edition with fleet management across many databases, and support for teams running active mode in production. Managed database providers are a natural partner, since better plans mean fewer upgrade tickets.
2. What real problem does it solve?
Plans that go wrong because the statistics can’t see what the data really looks like. Fixing them by hand works but doesn’t scale, and the fixes go stale as data changes. LQO keeps measuring and only keeps what still helps.
3. The technology in plain words
Think of a satnav that sometimes picks a terrible route because its traffic data is wrong. LQO is the driver who quietly tries the other routes on a quiet day, times each one several times, and only switches when a route is clearly faster every time. If it stops being faster, the driver switches back.
4. Moat and differentiation
Research systems use neural networks and cost models. Monitoring tools show slow queries and leave the fix to you. LQO is simple and explainable: time each plan on your data and apply a strict rule, using only switches PostgreSQL already has. That makes it safe to trust. The moat that can grow is the learning across similar query shapes, which proves new queries faster, and the safety record of active mode, which is what a cautious DBA needs to see.
Why It Matters
Most performance problems in PostgreSQL aren’t missing indexes. They’re plans built on wrong guesses. A tool that finds those queries and proves the fix, without changing the database itself, saves both hardware and expert time.
Follow the Project
Read what a learned query optimizer is and why PostgreSQL picks slow plans, start with the quick start, get it from the download page, or see the code in the LQO repository on GitHub. LQO is listed with the other open-source projects on SoftDevStudio.com, and more featured projects are in the Projects section.