AI Coding Agents
Code Review
Database query plan review for agent changes
September 26, 2026 - 2 minute read
Database query plan review turns a performance concern into evidence a coding agent can inspect. 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 distinguishes planner estimates from the measurements produced by EXPLAIN ANALYZE. That distinction should shape the task and the review.
Define the database query plan review 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. Avoid a vague instruction to improve every query. It 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.
Factory's Droid Exec can run this bounded analysis in a controlled working directory and write a plan comparison artifact. Keep database credentials outside the prompt and restrict the task to a sanitized or synthetic dataset.
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. Compare node types, estimated rows, total cost, and the point where the old and new plans diverge.
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. The agent should report that uncertainty instead of claiming a source change fixes it.
Apply database query plan review 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.
Factory's automated code review can check the resulting diff for correctness problems. Add repository guidance for database-specific hazards, then keep the plan comparison and integration test as separate required checks.
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.
