Detect Query Plan Regressions in CI

Capture EXPLAIN output for critical queries as golden files and fail CI when a plan introduces sequential scans or a much higher cost.

~40 minpostgresqltestingperformance

Task

Catch planner regressions caused by statistics, index, or version changes before they raise production latency.

When to use this

An index is dropped during a cleanup and a critical dashboard query silently switches to a sequential scan. Nothing fails in tests, and the regression is noticed only when p99 latency doubles a week later.

Prerequisites

PostgreSQL test database seeded with realistic row counts
SQL files for the critical queries under version control
A CI job that can run psql against the test database

Steps

1. Seed deterministic data and run ANALYZE so plans reflect realistic distributions.

psql "$TEST_DATABASE_URL" -v ON_ERROR_STOP=1 -f testdata/seed.sql && psql "$TEST_DATABASE_URL" -c 'ANALYZE;'

Expected output: The seed script loads representative tenants and orders, and ANALYZE completes without warnings.

2. Write a script that captures the plan without cost noise and compares it to the committed golden file.

#!/usr/bin/env bash
set -euo pipefail

psql "$TEST_DATABASE_URL" -X -q -A -t \
  -c "EXPLAIN (COSTS OFF) SELECT o.id FROM orders o WHERE o.tenant_id = 42 AND o.status = 'open' ORDER BY o.created_at DESC LIMIT 20" \
  > plans/orders_open.plan

diff -u plans/orders_open.expected plans/orders_open.plan

Expected output: The diff is empty when the plan is unchanged and shows the exact plan change when it is not.

3. Fail the build on an unexpected change and require the author to update the golden file deliberately.

bash scripts/check-query-plans.sh || (echo 'Query plan changed; review before merging'; exit 1)

Expected output: A dropped index or a new sequential scan fails the pull request with the plan diff attached.

4. Regenerate the golden file only in the same pull request that explains the change.

cp plans/orders_open.plan plans/orders_open.expected && git diff --stat plans/

Expected output: The updated golden file is reviewed alongside the index or query change that caused it.

Troubleshooting

Problem

Plans differ between the CI image and the developer laptop

Solution

Pin the PostgreSQL image tag and seed script version; planner behavior and default cost settings vary across major versions.

Problem

Plans flap because of stale or skewed statistics

Solution

Seed deterministic data, run ANALYZE every time, and avoid comparing plans on tables with random row counts.

Limitations

COSTS OFF hides cost estimates, so a plan with the same shape but much higher cost is not detected.
Golden plans are version-specific; a PostgreSQL upgrade requires regenerating all of them.

Plans are behavior, not implementation

The query planner is free to choose any plan that returns the same rows. When an index disappears, statistics change, or a version upgrade alters cost constants, the plan can change without any code change. A golden plan test treats the plan for a small set of critical queries as a contract: a change is allowed, but it must be explicit and reviewed.

Pick a small set

Start with five queries that dominate latency or cost, not every query in the codebase. Capture EXPLAIN (COSTS OFF) because cost values vary with statistics and settings but node structure is stable enough to review. Run the capture against a seeded database with representative cardinalities; plans on an empty table are meaningless.

psql "$TEST_DATABASE_URL" -X -q -A -t \
  -c "EXPLAIN (COSTS OFF) SELECT ..." > plans/orders_open.plan
diff -u plans/orders_open.expected plans/orders_open.plan

Make failures actionable

When the diff is non-empty, print it in the CI log and tell the author what to do: either fix the regression or update the golden file with an explanation. Do not auto-update the file. The review of the updated plan is the entire value of the test; silently accepting changes turns it into noise.

Extend to cost

If plan shape alone is too coarse, capture EXPLAIN (FORMAT JSON) and assert on total cost within a tolerance, or record actual execution time with EXPLAIN ANALYZE on a fixed dataset. Both are noisier than plan text, so use them for the two or three queries where cost matters most. Re-baseline after every PostgreSQL major upgrade and document the version in the plans directory.