Skip to content

How do I get my AI agent to read EXPLAIN ANALYZE and pg_stat_statements?

Last updated

View as Markdown

Quick answer: Your AI coding agent reads files, not databases. A query plan lives in your running Postgres, not in your repo, so the agent never sees it. Close the gap in one of three ways: paste EXPLAIN (ANALYZE, BUFFERS) output into the conversation, keep a refreshed diagnostics file the agent reads, or give it a read-only database connection (typically over MCP) so it can query the real numbers itself.

An AI coding agent and its harness are built around a repository. They index files, follow imports, and reason about source. That is the full surface they can access.

A query plan is not in the source. It is produced at runtime by the planner from your table statistics, data distribution, and actual row counts. The same SELECT is an index scan on your laptop’s 500-row fixture table and a sequential scan over 40 million rows in production. Nothing in the repository distinguishes those cases.

pg_stat_statements has the same limitation from the other direction. It aggregates normalized query shapes across real traffic: which shape used the most cumulative time, how many times it ran, and how often it missed shared buffers. That data exists only after your code has run against production volume. It is absent from the code the agent reads.

So when you ask an agent “why is this query slow?”, it can only pattern-match the SQL text against things that are usually slow. Sometimes that works. Often it invents a plausible cause and confidently proposes an index you already have.

What do the manual patterns look like, and where do they break?

Section titled “What do the manual patterns look like, and where do they break?”

Most people start here, and you should too. It is the fastest way to test whether the loop is worth automating.

You run EXPLAIN (ANALYZE, BUFFERS) in psql, copy the output, and paste it into the conversation. This works. The agent can read plans once it has one.

Where it degrades:

  • It is a snapshot. The agent proposes a change, you apply it, and then you have to re-run and re-paste to see whether it helped. Every iteration is manual.
  • You choose the query. The agent sees only what you already suspected. The query actually using 40% of your database time may be one you never thought to check.
  • It goes stale silently. The plan you pasted twenty minutes ago may not be the plan Postgres picks now, after an ANALYZE, a bulk load, or the table crosses a size threshold. The agent has no way to know its input has expired.

Saving diagnostics to a file the agent reads

Section titled “Saving diagnostics to a file the agent reads”

A step up is to dump plans and pg_stat_statements output to docs/db-diagnostics.md or a similar file and let the agent read it. This is better because the agent can access it without you re-pasting, and it persists across sessions.

It has the same flaw, only worse: the file has no expiry. A pasted plan is at least visibly a point-in-time artifact. A committed file looks authoritative and gets trusted six weeks later, after three migrations and a tenfold traffic increase. A stale diagnostics file is often worse than no file because it makes the agent confidently wrong instead of appropriately uncertain.

This belongs to the same category and decays in the same way. The instinct is right: put real facts in front of the model. The format has no refresh mechanism.

Four things cover most “why is this slow” work:

DataWhere it comes fromWhy the agent needs it
Top queries by cumulative costpg_stat_statements, ranked by total_exec_timeTells it which query matters, including one you did not suspect
The plan, with actualsEXPLAIN (ANALYZE, BUFFERS)Estimated vs actual rows is the root-cause signal for most bad plans
Existing indexespg_indexes / pg_stat_user_indexesStops it proposing an index you already have, or one that duplicates a prefix
Approximate table sizespg_class.reltuples, pg_total_relation_size()A Seq Scan on 800 rows is fine; on 80 million it is not. Without this the agent cannot tell

Rank by cumulative cost, not average:

SELECT
queryid,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
shared_blks_hit,
shared_blks_read,
left(query, 200) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

A query that takes 3 ms and runs 40 million times is a bigger problem than one that takes 4 seconds and runs twice a day. mean_exec_time hides that; total_exec_time does not. (See what pg_stat_statements tells you for what these columns do and do not mean.)

Existing indexes and their usage:

SELECT
s.relname AS table_name,
s.indexrelname AS index_name,
s.idx_scan,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
i.indexdef
FROM pg_stat_user_indexes s
JOIN pg_indexes i
ON i.schemaname = s.schemaname
AND i.indexname = s.indexrelname
WHERE s.relname = 'orders'
ORDER BY s.idx_scan DESC;

Approximate row counts without a full table scan:

SELECT
relname,
NULLIF(reltuples, -1)::bigint AS approx_rows,
pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = 'public'::regnamespace
ORDER BY reltuples DESC
LIMIT 25;

reltuples is an estimate updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX. It is good enough for order-of-magnitude reasoning, which is all the agent needs here, and far cheaper than count(*).

How do I make plan output small enough to be useful?

Section titled “How do I make plan output small enough to be useful?”

This is the second practical obstacle. EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on a real query with a dozen joins is enormous and deeply nested, with every node carrying cost, timing, buffer, and width fields. Dump the whole thing into a context window and you have spent thousands of tokens to communicate about four numbers that mattered.

Some things help:

Prefer text format for pasting. The default text output is much more compact than FORMAT JSON for the same plan. JSON is the right choice when a program will parse it; it is the wrong choice when a human is copying it into a chat box.

Summarize the misestimated nodes. The useful information in a plan is concentrated in nodes where the planner’s estimated rows diverge sharply from actual rows, and in nodes where actual time (times loops) accounts for a large share of total runtime. Everything else provides context. A useful summary is more like:

Total: 4,210 ms
Nested Loop (est 12 rows, actual 84,300 rows, loops=1) <-- 7,000x misestimate
-> Seq Scan on orders (est 12, actual 84,300) filter: status = 'pending'
-> Index Scan on customers (actual 0.046 ms, loops=84,300) <-- 3,900 ms here
Buffers: shared hit=412 read=61,200
Indexes on orders: (id), (created_at) -- nothing on status
orders: ~41,000,000 rows

That is the diagnosis in nine lines: the planner thought the filter was selective, but it was not, and the inner index scan ran 84,300 times. An agent can work with that. It is also small enough to keep in context alongside the code you are changing.

Use FORMAT JSON when a tool is doing the reduction. If a program is walking the plan tree to find the worst nodes, JSON is the right format. The verbosity does not consume the context window because a machine, not the model, processes it.

If you want the mechanics of reading the tree yourself, see how to read a PostgreSQL EXPLAIN ANALYZE plan.

MCP (Model Context Protocol) lets an agent call tools that reach outside the repository. Point one at a Postgres connection and the agent can run diagnostic queries itself, when it needs them.

The main changes are freshness and initiative. The agent stops reasoning about a snapshot you chose and starts reasoning about the database as it is. It can ask a follow-up question you did not anticipate. It can check whether the index it is about to propose already exists instead of assuming.

The rules that make this safe:

  • Read-only, enforced by the database. Role privileges enforce this, not a prompt instruction. Use a dedicated role with SELECT on the relevant schemas and pg_read_all_stats for the statistics views, and no INSERT, UPDATE, DELETE, or DDL. A prompt saying “do not write” is not a security control.
  • Be careful with EXPLAIN ANALYZE itself. It executes the statement, and a read-only role does not make that safe. It stops writes, not resource use. The SELECT the agent runs can still scan for forty minutes, spill gigabytes of temp files, and evict the buffer cache your live traffic depends on. Set plain EXPLAIN as the default, without ANALYZE, so it does not execute and is genuinely always safe. When you want real timings, enforce a server-side ceiling rather than trusting the prompt: ALTER ROLE agent_ro SET statement_timeout = '30s' plus a temp_file_limit set the same way with ALTER ROLE, run as a superuser (the role cannot SET it itself). Run it against a replica if you have one. Never EXPLAIN ANALYZE a write statement against production: read-only privileges will reject it, but on a role that can write, it commits.
  • The human applies changes. The agent reads, explains, and proposes. The index goes in through your normal migration process, with review and CREATE INDEX CONCURRENTLY where appropriate. Connecting an agent to the database should not allow it to change the database.

Concretely, with a read-only connection in place:

1. You ask an open question. “What is the most expensive thing my database is doing right now?” You name no query because you do not know yet.

2. The agent pulls real rankings. It queries pg_stat_statements ordered by total_exec_time and gets back actual rows: a normalized SELECT ... FROM orders JOIN customers ... with 3.1 million calls and 41 minutes of cumulative execution time, roughly a third of all database time in the window.

3. It gets a plan for that one. It runs EXPLAIN (ANALYZE, BUFFERS) on the worst offender, then reduces the tree to the nodes that matter instead of reporting every line.

4. It explains the mechanism. “The planner estimated 12 rows from orders WHERE status = 'pending' and got 84,300. There is no index on status, so it is a Seq Scan, and because the estimate was tiny the planner chose a nested loop, which then executed the inner index scan on customers 84,300 times. That inner loop is 3.9 of the 4.2 seconds.”

5. It checks what already exists. It reads pg_indexes on orders: indexes on id and created_at, nothing on status. The proposal is not a duplicate.

6. It proposes; you apply. A partial index on status where the value is selective, created concurrently in a migration you review:

CREATE INDEX CONCURRENTLY idx_orders_status_pending
ON orders (status)
WHERE status = 'pending';

Two caveats the agent should surface and you should check: CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and most migration tools wrap each migration in one by default (Rails disable_ddl_transaction!, Django atomic = False). If the build fails, it leaves an INVALID index behind that still costs write overhead; drop it and retry.

7. You verify with the same loop. Re-run the plan after the index is live and statistics have caught up. The estimate should now be close to actual, and the join should have moved away from the nested loop.

Every step in that loop consumed real data from your database. None of it required the agent to guess what your schema or data volume looks like, and none of it required the agent to write anything.

  • Why can't my AI coding agent read my query plans by default?

    Because a coding agent and its harness are wired to your filesystem and your repository, not to your database. They can read the SQL string in your source, but a query plan is a runtime artifact of your data volume, distribution, and statistics, it does not exist anywhere in the code. Unless you paste the plan in or connect the agent to the database, it has nothing to read.

  • Should I paste EXPLAIN ANALYZE output into the chat?

    It works, and it is the right first step. It degrades over time because you have to re-paste after every change, the plan is stale the moment your data moves, and the agent only ever sees the one query you thought to check. Text-format plans paste better than FORMAT JSON, which is far more verbose.

  • What data does an agent actually need to diagnose a slow query?

    Four things: the top pg_stat_statements rows ranked by total_exec_time, an EXPLAIN (ANALYZE, BUFFERS) plan for the query in question, the indexes that already exist on the tables involved, and approximate row counts for those tables. With those, an agent can usually name the cause; without them it is guessing from the SQL text.

  • Is it safe to give an AI agent access to my production database?

    Only with a read-only role and a scoped connection. Grant pg_read_all_stats and SELECT on what it needs, never DDL or write privileges. The agent should be able to observe and explain; applying any change should stay a human action through your normal migration process.

  • How do I keep a huge JSON query plan from eating my whole context window?

    Do not dump the full tree. Reduce it to the nodes where estimated and actual rows diverge sharply and the nodes carrying most of the runtime, plus the buffer counts and the existing indexes. That is usually a dozen lines and contains the entire diagnosis.