# How to review database query plans in agent PRs > Database query plan review compares the old and new plan for the same query, parameters, and data shape before merge. Learn when to use EXPLAIN or EXPLAIN ANALYZE, how to spot estimate errors, and how Factory runs the comparison and reviews the diff. URL: https://factory.com/articles/database-query-plan-review-coding-agents Published: 2026-09-26 Updated: 2026-10-23 To review a query plan in an agent pull request, capture the old and new plan for the same query, parameter shapes, schema, and representative data, then compare node types, row estimates, and cost at the point where the plans diverge. Use measured execution only in an isolated environment, and repeat runs before calling anything a regression. A small SQL change can alter join order, row estimates, index use, or the amount of data sorted. Unit tests may confirm the result while missing a plan that becomes expensive with production-shaped data. PostgreSQL's guide to [using EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html) distinguishes planner estimates from the measurements produced by `EXPLAIN ANALYZE`. That distinction should shape the task and the review. ## Define the baseline Start with the exact query, bound parameter shapes, schema version, PostgreSQL version, and representative table statistics. Include the existing plan when one is available. A plan captured from an empty developer database says little about the same query over uneven production data. Tell the agent which outcome matters. The goal may be reducing rows scanned, removing an unexpected sort, preserving index use, or keeping latency within an established benchmark. A vague instruction to improve every query encourages unrelated index creation and rewrites that are difficult to compare. Record whether the baseline comes from production telemetry, a staging replay, or a synthetic fixture. The source determines how confidently a reviewer can generalize the result. ## Compare estimates with measured execution Use plain `EXPLAIN` first when executing the statement would be unsafe. Add buffer and format options that make the result consistent enough for review. The table lists what to compare between the old and new plan, and what a difference usually points to. | Compare | What a difference suggests | | --- | --- | | Node types | A new sequential scan, sort, or join strategy | | Estimated rows against actual rows | Statistics that no longer match the data | | Total cost | A more expensive plan shape | | Buffers read | More pages touched for the same result | | Divergence point | The node where the change took effect | Use `EXPLAIN ANALYZE` only in an isolated environment because it executes the statement. For data-changing statements, PostgreSQL documents wrapping the command in a transaction and rolling it back. Record execution time across several controlled runs rather than treating one warm-cache result as a stable benchmark. Look for estimation errors as well as slower nodes. A plan may choose a poor join because statistics do not represent current data distribution. In that case, changing the query can mask the underlying statistics problem, and the agent should report that uncertainty instead of claiming a source change fixes it. ## Keep the fix in context An index has write, storage, and maintenance costs. A query rewrite can change locking or result ordering. Ask the agent to inspect migration files, callers, transaction boundaries, and existing indexes before proposing either. Keep the pull request focused on one measured regression. The review summary should name the dataset shape, commands, old and new plans, measured runs, and any remaining variance. This gives a database reviewer enough context to approve the change or request a safer experiment. ## How Factory runs query plan review Factory is a platform for running a software factory, where agents called Droids take work from a signal to a reviewed pull request. For query plans, Factory runs the comparison as a bounded task and reviews the resulting diff. The flow below shows the order. The plan comparison and the code review stay separate checks so one cannot hide a gap in the other. SQL change
query, index, or migration"] B["Plain EXPLAIN
old and new plan, same data shape"] C["EXPLAIN ANALYZE
isolated database, repeated runs"] D["Plan comparison artifact
divergence point and variance"] E["Code review
correctness and database rules"] F["Database reviewer
approves or asks for more"] A --> B --> C --> D --> F A --> E --> F `} /> **Bounded, headless analysis.** [Droid Exec](https://docs.factory.com/droid-exec/overview) runs a task as one non-interactive pass. It is read-only by default and needs `--auto` before it can edit files or run riskier commands, and `-o json` keeps the plan comparison machine-readable. Keep database credentials outside the prompt and point the task at a sanitized or synthetic dataset. **Review with database rules.** [Automated code review](https://docs.factory.com/software-factory/code-review-ci) posts inline comments on each pull request. A `review-guidelines` skill can name database-specific hazards, such as new indexes without a migration review. The local [`/review` command](https://docs.factory.com/software-factory/code-review) accepts custom instructions for a pre-push check of the same rules. To try both on your own repository, run these commands in the Droid CLI.
Local review. Reviews uncommitted changes, a commit, or a branch against its base. One-time setup. Adds automated code and security review to your pull requests.
## Frequently asked questions ### What is database query plan review? Database query plan review compares the execution plan of a query before and after a change, using the same query, parameter shapes, schema version, database version, and representative statistics. It catches changes to join order, row estimates, index use, and sorting that unit tests miss. ### What is the difference between EXPLAIN and EXPLAIN ANALYZE? EXPLAIN shows the planner's estimates without running the statement. EXPLAIN ANALYZE executes the statement and reports measured results, so use it only in an isolated environment. For data-changing statements, PostgreSQL documents wrapping the command in a transaction and rolling it back. ### Why does the dataset matter for a query plan? A plan captured from an empty developer database says little about the same query over uneven production data. Record whether the baseline comes from production telemetry, a staging replay, or a synthetic fixture, because the source decides how far a reviewer can generalize the result. ### Should a coding agent add an index to fix a slow query? Only after checking the costs. An index has write, storage, and maintenance costs, and a query rewrite can change locking or result ordering. The agent should inspect migrations, callers, transaction boundaries, and existing indexes, and keep the pull request to one measured regression. ### How does Factory help with query plan review? Droid Exec runs the bounded analysis headless, read-only by default, and can write structured output for CI. Automated code review checks the diff on every pull request and can apply database rules from a review-guidelines skill. ## Further reading - [How to catch performance regressions before merge](https://factory.com/articles/coding-agent-performance-regression-review) - [Safer database migrations with coding agents](https://factory.com/articles/coding-agent-database-migrations) - [Reviewing agent-generated pull requests at scale](https://factory.com/articles/reviewing-agent-generated-pull-requests) - [PostgreSQL: Using EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html)