PostgreSQL EXPLAIN ANALYZE With AI: A Guide

If you have ever pasted a 60-line query plan into a chat window and asked "why is this slow?", you have already tried to read PostgreSQL EXPLAIN ANALYZE output with AI. Sometimes the answer is brilliant. Other times the model confidently recommends an index that already exists, or one that Postgres will never use. This guide shows how to do it properly: capture a plan the model can actually reason about, prompt it so the answer is specific, automate the loop in Node.js, and verify every suggestion before it touches production.

Why EXPLAIN ANALYZE output is hard to read

A plan is a tree of nodes, and each node reports estimated rows, actual rows, loops, timing, and buffer usage. The real problem is rarely visible in the top line. It hides in a mismatch deep in the tree: a node that estimated 10 rows and returned 400,000, a Seq Scan that discards 99% of rows with Rows Removed by Filter, or a nested loop repeated 50,000 times.

AI is good at exactly this kind of pattern matching across a long, noisy tree. It is bad at knowing your data distribution, your existing indexes, or your write load. That split tells you how to use it: let the model read the plan, but give it the schema context and check its answer against the database.

Step 1: Capture a plan the model can use

Plain EXPLAIN only shows estimates. You need EXPLAIN ANALYZE, which actually runs the query, plus buffer statistics to see how much data was read from cache versus disk:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > now() - interval '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

Two rules matter here. First, EXPLAIN ANALYZE executes the statement, so for INSERT, UPDATE, or DELETE wrap it in a transaction and roll back:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) UPDATE orders SET status = 'archived' WHERE created_at < now() - interval '1 year';
ROLLBACK;

Second, prefer FORMAT JSON when the plan goes to a script. It is unambiguous to parse, and models handle structured trees more reliably than wrapped text output.

Step 2: Give the model the context it cannot guess

A bare plan produces generic advice like "consider adding an index." Add three things: the original query, the table definitions with existing indexes, and the row counts. A prompt template that consistently works:

You are a PostgreSQL performance reviewer.

Query:
{query}

Schema and existing indexes:
{schema}

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) output:
{plan}

1. Identify the single node that costs the most actual time.
2. Point out any node where estimated rows differ from actual rows by 10x or more.
3. Suggest at most two changes (an index, a query rewrite, or a statistics fix).
4. For each change, give the exact SQL and state one risk, such as write overhead.
Do not suggest an index that already exists in the schema above.

The final line is not decoration. Without it, models regularly re-propose indexes that are already in place.

Step 3: Automate PostgreSQL EXPLAIN ANALYZE with AI in Node.js

Once the prompt works by hand, wrap it in a small script so any slow query can be reviewed in one command:

import pg from "pg";
import Anthropic from "@anthropic-ai/sdk";

const db = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const ai = new Anthropic({ apiKey: process.env.ANTHROPIC_API_KEY });

export async function reviewQuery(sql) {
  const { rows } = await db.query(
    `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${sql}`
  );
  const plan = JSON.stringify(rows[0]["QUERY PLAN"]);

  const { rows: idx } = await db.query(
    `SELECT tablename, indexdef FROM pg_indexes WHERE schemaname = 'public'`
  );
  const schema = idx.map((r) => r.indexdef).join("\n");

  const res = await ai.messages.create({
    model: process.env.LLM_MODEL,
    max_tokens: 1200,
    messages: [{ role: "user", content: buildPrompt(sql, schema, plan) }],
  });
  return res.content[0].text;
}

Point this at a staging database or a read replica, never at production with untrusted SQL, because the query really executes. Keep the model name in an environment variable so upgrading it does not require a code change.

Step 4: Verify every suggestion before you ship it

Treat the model's output as a hypothesis. A reliable verification loop looks like this:

1. Create the suggested index on a staging copy with realistic data volume.
2. Run ANALYZE on the table so the planner has fresh statistics.
3. Re-run the same EXPLAIN (ANALYZE, BUFFERS) and compare execution time and buffer reads.
4. Confirm the new plan actually uses the index, since Postgres may still choose a sequential scan on small or low-selectivity tables.
5. Create it in production with CREATE INDEX CONCURRENTLY to avoid blocking writes.

If the plan does not change, the suggestion was wrong for your data, no matter how convincing the explanation sounded.

Common mistakes when using AI for query plans

• Pasting production data into the prompt. Plans can contain literal values in filters. Use placeholders or sanitized queries, and check your data-handling policy before sending anything to a third-party API.
• Trusting cost numbers across queries. Costs are planner estimates in arbitrary units. Compare actual time and buffers, not cost.
• Ignoring stale statistics. A huge estimate-versus-actual gap often means the table needs ANALYZE, not a new index. Ask the model to consider this explicitly, as the prompt above does.
• Adding every suggested index. Each index slows writes and consumes storage. Accept the one that fixes the slowest node and re-measure.

Frequently Asked Questions

Can AI replace learning to read EXPLAIN ANALYZE?

No. It speeds up first-pass triage, but you still need to recognise node types like Seq Scan, Index Scan, and Nested Loop to judge whether a suggestion makes sense.

Is it safe to run EXPLAIN ANALYZE on production?

It is safe for read-only SELECT statements that you would be comfortable running anyway, but it consumes real resources. For data-modifying statements, use a transaction with ROLLBACK, or better, use a staging copy.

Why does the model suggest an index that Postgres ignores?

The planner may judge a sequential scan cheaper for small tables, or the index columns may not match the query's filter and sort order. Verify with a fresh EXPLAIN ANALYZE after creating the index on realistic data.

Conclusion

Reading PostgreSQL EXPLAIN ANALYZE output with AI works best when you supply the query, the schema and existing indexes, and a structured JSON plan, then ask for at most two concrete changes. Always reproduce the improvement on staging with a fresh ANALYZE before you run CREATE INDEX CONCURRENTLY in production. Start with your single slowest query this week, save the prompt template in your repository, and reuse it for every plan review after that.