Query Plan Stability
Hint vs let optimiser decide.
Overview
Query plan stability ensures database query plans stay predictable across statistics updates and version upgrades. The optimizer’s job is to pick the best plan given current statistics; when statistics drift or the optimizer picks a different plan after an upgrade, queries that ran in milliseconds suddenly take seconds. The discipline is in plan-cache awareness, judicious use of query hints, freshness of statistics, and quarterly plan review against actual production traffic.
- Hint vs let optimiser decide. Per-query the hint tradeoff; hints lock plans but prevent the optimizer from adapting to changed statistics.
- Plan-cache awareness. Per-database plan cache behavior; cached plans can persist after the workload that justified them.
- Query hints carefully. Per-query hint use; hints are last-resort tools because they prevent optimizer adaptation.
- Per-quarter plan review plus statistics freshness. Quarterly plan review catches drift; per-table analyze cadence keeps statistics accurate so the optimizer picks well.
The approach
The practical approach is to keep statistics fresh (per-table analyze cadence matched to write volume), monitor plan-cache behavior, use query hints sparingly and only when the optimizer is provably wrong, run quarterly plan reviews against pg_stat_statements or equivalent, and document the per-database plan-management policy so the rules are reviewable. Stable plans are downstream of fresh statistics; hints are the last resort.
- Plan-cache awareness. Per-database plan cache monitored; surface cached plans that have outlived their workload.
- Query hints carefully. Per-query hint use only where the optimizer is provably wrong; hints prevent adaptation to future workload changes.
- Per-quarter plan review. Quarterly plan review against pg_stat_statements or equivalent; catches drift before it surfaces as a slow-query incident.
- Statistics freshness plus documented policy. Per-table analyze cadence matched to write volume; per-database plan-management rationale committed for operational review.
Why this compounds
Plan stability discipline compounds across queries and versions. Each stable plan preserves predictable performance; each fresh-statistics table prevents the optimizer from picking wrong plans; the team builds intuition for what makes plans stable rather than what makes them fast today. The opposite, where plans drift unmonitored, surfaces as surprise slow queries every quarter.
- Query performance. Right plan supports throughput; the optimizer chooses index scans where they belong and joins where they belong.
- Stability. Stable plans avoid surprises; the query that ran in 10ms last week still runs in 10ms this week.
- Operational fit. Right plan matches workload; the optimizer adapts as workload evolves rather than locking in stale assumptions.
- Institutional knowledge. Each plan teaches optimizer patterns; the team learns what makes plans stable rather than just what makes them fast.
Plan stability discipline is a database discipline that pays off across years. Nova AI Ops integrates with database telemetry, surfaces plan patterns, and supports the team’s database engineering discipline.