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

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 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 queries the real numbers itself.

Why cannot my agent do this already?

Claude Code, Cursor, and every other coding agent are built around a repository. They index files, follow imports, and reason about source. That is the whole surface area they are given.

A query plan is not in the source. It is produced at runtime by the planner, from your table statistics, your data distribution, and your 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 two cases.

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

So when you ask an agent "why is this query slow?". It does the only thing it can: pattern-match the SQL text against things that are usually slow. Sometimes that is right. 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?

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

Pasting plan output into the chat

You run EXPLAIN (ANALYZE, BUFFERS) in psql, copy the output, paste it into the conversation. This genuinely works. The agent is good at reading plans once it has one.

Where it degrades:

• It is a snapshot. The agent proposes a change, you apply it, and now you have to re-run and re-paste to see whether it helped. Every iteration is manual.
• You choose the query. The agent only ever sees what you already suspected. The query actually eating 40% of your database time is 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, after a bulk load, after the table crossed a size threshold. The agent has no way to know its input expired.

Saving diagnostics to a file the agent reads

A step up: dump plans and pg_stat_statements output to docs/db-diagnostics.md or similar, and let the agent read it. Better, because the agent can pull it without you re-pasting, and it survives across sessions.

Same fatal flaw, 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.

The .cursorrules / CLAUDE.md schema dump

Same category, same decay curve. It is the right instinct, get real facts in front of the model, implemented with a format that has no refresh mechanism.

What data does the agent actually need?

Four things cover the large majority of "why is this slow" work:

Data	Where it comes from	Why the agent needs it
Top queries by cumulative cost	pg_stat_statements, ranked by total_exec_time	Tells it which query matters, the one you did not suspect
The plan, with actuals	EXPLAIN (ANALYZE, BUFFERS)	Estimated vs actual rows is the root-cause signal for most bad plans
Existing indexes	pg_indexes / pg_stat_user_indexes	Stops it proposing an index you already have, or one that duplicates a prefix
Approximate table sizes	pg_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

Ranking 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, good enough for order-of-magnitude reasoning, which is all the agent needs here, and vastly cheaper than count(*).

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

This is the practical blocker people hit second. EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on a real query with a dozen joins is enormous, deeply nested, 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 that help:

Prefer text format for pasting. The default text output is dramatically more compact than FORMAT JSON for the same plan. JSON is the right choice when something is going to parse it programmatically; it is the wrong choice when a human is copying it into a chat box.

Summarize to the misestimated nodes. The signal in a plan is concentrated: nodes where the planner's estimated rows diverge sharply from actual rows, and nodes where actual time (times loops) accounts for a large share of total runtime. Everything else is 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 whole diagnosis in fifteen lines: the planner thought the filter was selective. 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 actually changing.

Use FORMAT JSON when a tool is doing the reduction. If something is programmatically walking the plan tree to find the worst nodes, JSON is exactly right. That is the case where verbosity is free because a machine, not a context window, absorbs it.

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

How does the MCP approach change this?

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 the diagnostic queries itself, on demand, at the moment it needs them.

The structural difference is not convenience. It is freshness and initiative. The agent stops reasoning about a snapshot you chose and starts reasoning about the database as it currently 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 rather than reckless:

• Read-only, enforced by the database. Not by prompt instruction, by role privileges. A dedicated role with SELECT on the relevant schemas and pg_read_all_stats for the statistics views. No INSERT, UPDATE, DELETE, or DDL. A prompt saying "do not write" is not a security control.
• Careful with EXPLAIN ANALYZE itself. It executes the statement, and a read-only role does not make that safe, it stops writes, not cost. 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. So: default the agent to plain EXPLAIN (no ANALYZE), which does not execute and is genuinely always safe. When you do want real timings, enforce a server-side ceiling rather than trusting the prompt.ALTER ROLE agent_ro SET statement_timeout = '30s' plus SET LOCAL temp_file_limit, and run it against a replica if you have one. And never EXPLAIN ANALYZE a write statement against production: read-only privileges will reject it, but the reason to avoid it is that 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, reviewed, with CREATE INDEX CONCURRENTLY where appropriate. Nothing about connecting an agent to the database should mean the agent changes the database.

What does the loop actually look like?

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?", no query named, 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. EXPLAIN (ANALYZE, BUFFERS) on the worst offender, then reduces the tree to the nodes that matter rather than 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. Reads pg_indexes on orders: indexes on id and created_at, nothing on status. So 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). And 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 flipped 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 your data volume looks like, and none of it required the agent to write anything.

How DBGorilla helps

DBGorilla connects to your database read-only and works through the AI coding agent you already use (Claude Code, Cursor) over MCP. When your agent asks what is slow, it gets the real pg_stat_statements rows, the real EXPLAIN (ANALYZE, BUFFERS) plan with estimated-vs-actual rows, the indexes that actually exist, and real table sizes, reduced to what fits usefully in a context window instead of a raw dump. It surfaces and explains the data so your agent can reason about your production database rather than infer it from source. It does not write to your database, run migrations, or change configuration, applying a fix stays your call. Get started free →

FAQ

Why cannot Claude Code or Cursor read my query plans by default? They are wired to your filesystem and repository, not your database. A plan is a runtime artifact of your data volume and statistics. It does not exist in the code. Without a pasted plan or a database connection, the agent has nothing to read and falls back to pattern-matching the SQL text.

Should I paste EXPLAIN ANALYZE output into the chat? Yes, as a first step, it works well. It degrades because you re-paste after every change, the plan is stale the moment your data moves, and the agent only sees the query you already suspected. Use text format, not FORMAT JSON, which is far more verbose for the same plan.

What data does an agent actually need to diagnose a slow query? The top pg_stat_statements rows 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. With those four it can usually name the cause; without them it is guessing.

Is it safe to give an AI agent access to my production database? With a read-only role, yes. Grant SELECT on what it needs plus pg_read_all_stats, and no write or DDL privileges, enforced by the database, not by a prompt. Keep applying changes a human action through your normal migration path.

How do I keep a huge JSON 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.

Related

• How to read a PostgreSQL EXPLAIN ANALYZE plan
• What pg_stat_statements tells you (and what it does not)
• Why AI-generated SQL is slow
• Why does my AI coding agent hallucinate columns that do not exist?