Debug a Slow Database Query With AI: Read the Plan

To debug a slow database query with AI, give the assistant the query plan, not just the SQL. An AI model cannot see your tables, row counts or indexes, so from the query alone it can only guess. The plan from EXPLAIN ANALYZE shows what Postgres actually did and how long each step took, and that is the evidence a good diagnosis needs.

This guide covers running the command safely, the three things in the output worth reading, and a prompt that gets useful answers. The details come from the PostgreSQL documentation on using EXPLAIN, and the examples are Postgres-specific.

Run it safely first

EXPLAIN alone shows the plan Postgres intends to use, with estimates. EXPLAIN ANALYZE runs the query and reports what really happened. The docs spell out the consequence: because it actually executes the query, any side effects happen as usual. An UPDATE or DELETE under EXPLAIN ANALYZE changes your data.

The docs' own safe pattern is to wrap it in a transaction and roll it back:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE customer_id = 42;
ROLLBACK;

If an AI coding agent is running these for you, make sure it knows this rule, and think twice about letting it run them against production at all. Keeping an AI coding agent away from your production database covers the guardrails.

The plan in one minute

The output is a tree with one line per step. Scan nodes sit at the bottom, and joins, sorts and aggregates sit above them. Each line shows cost, rows and width, and with ANALYZE it adds actual time, rows and loops. Costs are in arbitrary units, conventionally disk page fetches, while actual time is in milliseconds. Do not compare them to each other.

Here is the sample from the Postgres docs for a filter that cannot use an index:

Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244)
  (actual time=0.030..1.995 rows=7000.00 loops=1)
  Filter: (ten < 7)
  Rows Removed by Filter: 3000
  Buffers: shared hit=345
Planning Time: 0.102 ms
Execution Time: 2.145 ms

The three signals worth reading

1. Estimated rows against actual rows. The docs call this the thing that is usually most important to look for: whether the estimated row counts are reasonably close to reality. A large gap usually means the planner chose a poor plan, often because table statistics are stale. Running ANALYZE on the table and checking again is the first fix to try.

2. Rows removed by filter. If a step scans a million rows and the filter throws away all but a few, the query is doing the work of reading a lot to return a little. That is the shape an index is meant to fix, and a Seq Scan with a big Rows Removed by Filter line is the classic sign.

3. Loops times time. On ANALYZE output the docs say the actual time and rows are averages per execution, so you multiply by loops to get the total time spent in the node. A step that looks like 0.05 ms but runs 20,000 times is a full second.

Seq scan versus index scan

A sequential scan is not automatically bad. The docs say an index scan is most often seen for queries that fetch a single row, because fetching rows in index order is expensive per row. If your filter keeps most of the table, a sequential scan is the cheaper plan and an index will not help. Reading the plan tells you which situation you are in, and that is exactly the context an AI suggestion needs.

Be careful with test data too. The docs warn that results on a toy-sized table cannot be assumed to apply to large tables, so a plan from your empty development database proves little.

The prompt

This Postgres query is slow. Here is the query, the schema for the tables
involved with existing indexes, and the EXPLAIN (ANALYZE, BUFFERS) output.
Row counts: <table: count>.
1. Say which node accounts for most of the time, using actual time and loops.
2. Compare estimated rows with actual rows and say if statistics look stale.
3. Suggest at most two changes, each with the exact SQL.
4. For each change, say what I should see in the new plan if it worked.
Do not suggest rewriting the whole query.

Step four is the one that matters. It turns the suggestion into a test: add the index, run the same command, and compare. If the plan does not change the way the AI predicted, the diagnosis was wrong, and you have learned that in a minute instead of after a deploy.

What to expect from AI suggestions

Assistants are good at spotting a missing index on a filtered or joined column and at flagging a function wrapped around a column that blocks an index. They are weaker at stale statistics and at queries where the real problem is called thousands of times from application code. That last case is a loop in your app, not a slow query, and the plan will show a fast query. If that sounds familiar, read why AI coding agents write slow code and why your AI-built app is slow. For writing the query in the first place, see how to prompt AI to write a SQL query, and for choosing tools, the AI coding tools overview.

Slow query FAQ

What does EXPLAIN ANALYZE do in Postgres?

It runs the query and shows the plan with the real row counts and times for each step. Because it executes the query, data-changing statements take effect unless you roll them back.

Why is my Postgres query using a sequential scan?

Either no usable index exists for the filter, or the planner decided reading the whole table is cheaper because the filter keeps most rows. The plan's row estimates help you tell which.

Can AI suggest the right index for a slow query?

Often, when it has the plan and the existing indexes. Ask it to say what the new plan should look like, then run the command again to confirm.

Why is the estimated row count different from the actual?

Usually stale statistics. Running ANALYZE on the table refreshes them, and the plan may change afterwards.