AI-Assisted Query Optimization: Real Results

Optimisation Is an Evidence Problem

Query tuning fails in a specific way: someone changes something plausible, the query gets faster on their laptop, and nobody knows why. Adding a model to that loop makes it faster to produce plausible changes, which is not obviously progress.

What actually helps is narrower and less exciting — and it starts before any model is involved.

Do These First

• ANALYZE. A bulk COPY loads rows and leaves the planner with no statistics, so it guesses — badly. On day one after a migration this single command resolves more “PostgreSQL is slow” reports than everything else combined.
• pg_stat_statements. Order by total_exec_time. The query costing you the most is frequently not the one anyone complained about.
• EXPLAIN (ANALYZE, BUFFERS). Estimated versus actual rows is the diagnostic that matters. A row estimate off by three orders of magnitude explains almost every bad plan.

Where a Model Earns Its Place

Reading plans. A 200-line nested plan is dense, and summarising “the estimate on this join is off by 40,000x, and everything above it follows from that” is real assistance to someone who doesn’t read plans daily.

Explaining the Oracle habit. An Oracle DBA reaching for a hint needs to know that PostgreSQL has none by design — you fix the planner’s inputs, not its output. Translating that instinct into the PostgreSQL equivalent is a genuinely useful explanation.

Generating candidates. Proposing three rewrites to benchmark is fine. Proposing one and calling it the answer is not.

What the Numbers Look Like

Post-migration wins cluster in a boring, repeatable order, and almost none of them need a model:

ANALYZE after load          largest single win; seq scan -> index scan
CREATE STATISTICS           correlated columns; fixes bad row estimates
raise work_mem (session)    kills the disk sort in a reporting query
parallel workers per gather default 2 is conservative for large scans
rewrite correlated subquery to a join or a lateral

We deliberately don’t publish a headline speedup multiple. Any number we quoted would come from our workload, not yours, and “40% faster” without the schema and the query behind it is marketing. Measure on your own system; that is the only figure that means anything.

The Discipline

One change at a time, measured with EXPLAIN (ANALYZE, BUFFERS) before and after, on representative data volumes. A model can help you understand the plan and generate candidates. It cannot tell you whether your production data looks like your test data — and that is usually where tuning goes wrong.