Quick Start

LQO finds the PostgreSQL queries a planner setting makes clearly faster. This page takes you from nothing to a first report in a few minutes, on a demo database, then on your own.

1. Get the lqo Command

Download the archive for your system from the download page, unpack it and check it runs:

tar -xzf lqo_linux_amd64.tar.gz
./lqo version

2. Try It on the Demo

You need a PostgreSQL server you can create a schema on. A throwaway container is ideal:

docker run -d --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:18
export LQO_DSN="postgres://postgres:secret@localhost:5432/postgres?sslmode=disable"

lqo demo builds a small shop in a schema called lqo_demo: 200,000 customers, a million orders and three million order lines, made so that PostgreSQL’s row estimates go wrong in two well-known ways. It also writes the workload to try:

lqo demo
lqo advise --allow-primary lqo-demo.sql

--allow-primary is needed because a fresh container is a primary server. LQO normally runs only on a standby, since it runs every query several times.

After a minute or two you get the report. In the recorded run, the trap query went from 1,061 ms to 684 ms with nested loops switched off, a second query went from 35.1 ms to 24.7 ms without index scans, three queries stayed on the default plan, and an UPDATE was skipped because LQO only runs reads.

3. Run It on Your Database

Pick the server. Use a streaming replica, a restored backup or a staging copy with production-like data. LQO checks and refuses a primary unless you say --allow-primary.

Collect a workload. A server log is best, because it says which queries are slow and how often they run. On the primary, log statements that take more than 100 ms for a day:

ALTER SYSTEM SET log_min_duration_statement = '100ms';
SELECT pg_reload_conf();

LQO reads the plain log, csvlog, jsonlog and auto_explain output, and fills in the values of prepared statements from the parameters: lines. A .sql file of statements works too.

Run it.

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

The connection string works as it does for psql, and LQO_DSN, PGHOST, PGPASSWORD and ~/.pgpass all work. If your application sets a search_path, pass it with --set search_path=app,public.

By default LQO tests the 25 slowest kinds of query, three texts of each, three timed runs per plan. --top, --samples and --repeat change that.

4. Use What It Finds

Each suggestion is for one kind of query only. Apply it inside that query’s transaction, never server-wide:

BEGIN;
SET LOCAL enable_nestloop = off;
SELECT sum(l.qty) FROM ... ;
COMMIT;

SET LOCAL ends with the transaction, so nothing else on the connection changes. Run LQO again after big data changes or an upgrade: a plan that was right in March can be wrong in June.

lqo show lists everything LQO has found so far, and --report markdown writes a report you can paste into a ticket. The reference has every command and flag.