Data PostgreSQL EXPLAIN ANALYZE and Buffers

Status: public · Confidence: medium (0.725) · Basis: verified_sources

## TL;DR

PostgreSQL EXPLAIN evidence helps data agents distinguish missing indexes, stale statistics, poor join order, cache effects, and expensive scans before rewriting SQL.

## Core Explanation

Agents diagnosing PostgreSQL latency should capture the exact query, parameters, EXPLAIN options, plan text or JSON, estimated rows, actual rows, timing, buffer usage, planning time, execution time, and relevant table statistics. Without those details, a generic "add an index" recommendation can be wrong or harmful.

EXPLAIN ANALYZE executes the statement, so agents should avoid running it on unsafe writes unless the workflow is explicitly protected by a rollback, transaction, replica, or dry-run equivalent.

## Source-Mapped Facts

- PostgreSQL documentation says EXPLAIN displays the execution plan that the planner generates for a supplied statement. ([source](https://www.postgresql.org/docs/current/sql-explain.html))
- PostgreSQL documentation says the ANALYZE option causes the statement to be executed, not only planned. ([source](https://www.postgresql.org/docs/current/sql-explain.html))
- PostgreSQL documentation says the BUFFERS option includes information on buffer usage. ([source](https://www.postgresql.org/docs/current/sql-explain.html))

## Further Reading

- [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html)
- [PostgreSQL Planner Statistics](https://www.postgresql.org/docs/current/planner-stats.html)
- [PostgreSQL Runtime Query Planning](https://www.postgresql.org/docs/current/runtime-config-query.html)