Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesUse planner estimates as a cheap first screen, not as a latency guarantee; use a timed canary when query characteristics or local evidence make execution risk harder to judge from a plan alone. A canary executes the SQL, so it belongs on a controlled, representative rehearsal database—not as an automatic test against production. The right promotion gate is a locally calibrated combination of both signals, not a universal cost ceiling.
What each signal tells you
| Signal | What it measures | Does it execute the candidate? | Main limitation |
|---|---|---|---|
Plain EXPLAIN |
The planner’s estimated cost and row counts for a proposed plan. PostgreSQL cost values are arbitrary units, not elapsed milliseconds. | No. It plans the statement without running it. | Estimates depend on planner statistics and configuration; a cost threshold is a local heuristic, not a direct latency SLO. |
EXPLAIN ANALYZE in a timed canary |
Observed execution information, including actual runtime and row counts, alongside the plan estimates. | Yes. PostgreSQL states: “The ANALYZE option causes the statement to be actually executed, not only planned.” | It incurs the work of running the query, and its findings are useful only insofar as the rehearsal environment represents the intended workload. |
PostgreSQL 18 documents these distinctions in its EXPLAIN documentation. Comparing estimated and actual rows can reveal a mismatch that a plan-only screen cannot establish. That additional evidence is useful, but it is not free: execution consumes resources and may have side effects.
Which signal should be allowed to veto promotion?
Neither signal should be treated as a universal veto in isolation. A plan estimate is attractive for frequent screening because it does not execute the candidate; a timed canary gives execution-time evidence that estimates alone cannot provide. Those are practical trade-offs, not claims that one gate has outperformed the other in a comparative benchmark.
A workable policy is staged: reject or review candidates that violate locally calibrated plan rules, then require a bounded canary for cases with elevated risk or a history of estimate-to-execution disagreement. Allow an exception only when a reviewer can explain why the risk is low, and revisit that exception when data, statistics, configuration, or workload conditions change.
Recommended Free Tools
#1 Best Overall
Use plan evidence as an early screen
Capture a JSON-format plan and inspect the fields your team has chosen to monitor, such as estimated rows and scan types. A locally selected cost ceiling can help flag outliers, but planner cost is not predicted latency. Its meaning is tied to the local PostgreSQL setup and workload; do not transplant a threshold from another cluster.
Escalate to a canary when risk warrants execution
Potential escalation signals include unusually large estimated row counts, large sequential scans, correlated subqueries, OFFSET-based paging, volatile functions, or substantial disagreement between estimates and prior canary observations. These are candidate triggers to test against your own workload, not established universal rules. A team may also decide that a query class always needs a canary because its failure impact is high.
Rank #2
How to design a staged promotion gate
The following is a workflow to adapt and measure locally, not a validated deployment recipe.
- Record the candidate context. Keep the exact SQL, intended database role, candidate identity, and the service objective the team is trying to protect together.
- Capture a plan without execution. Run plain
EXPLAINin the intended planning context and store the JSON plan and selected estimate fields. Apply only thresholds calibrated for that environment. - Choose whether execution evidence is needed. Use agreed risk triggers and prior local observations to decide whether to promote on plan evidence, request review, or require a canary.
- Run a bounded canary on a rehearsal target. Use an isolated database with a controlled role and execution policy. Set limits appropriate to your environment; the example thresholds sometimes used to illustrate such a harness are not validated defaults.
- Store the verdict beside the candidate. Retain both the plan and canary result so reviewers can inspect estimate-versus-observed behavior and refine the local policy over time.
Do not interpret illustrative sample output as a measured cluster result. No comparative benchmark establishes that a planner-cost gate or timed canary performs better in general. Results can vary with hardware, cache warmth, PostgreSQL configuration, workload, and the representativeness of the rehearsal data.
Where a canary should run—and what it can prove
An existing staging replica or other suitable rehearsal database is preferable when it is isolated and representative enough for the question being tested. A small or skewed data subset can produce a different plan or runtime from the intended workload, and cache warmth can change observed timing. A canary result is evidence about the conditions under which it ran, not a guarantee about every production execution.
Keep the rehearsal role and target deliberately constrained. A connection-string naming check—for example, searching for a word such as “prod”—is not a security boundary. Use real access controls and an environment-selection mechanism that makes it difficult for a test to reach a production host.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Execution safety: a canary runs the statement
EXPLAIN ANALYZE executes the candidate; it is not a simulation. PostgreSQL warns that side effects can occur. Its SQL EXPLAIN documentation describes running analysis of data-modifying statements inside a transaction and rolling it back as one way to avoid retaining changes. A rollback does not make arbitrary SQL harmless, so use a controlled environment and permissions, and do not assume a read-only harness policy covers writes or DDL.
For a read-only candidate, verify that the rehearsal role and harness enforce the intended restrictions before execution. For statements that modify data or schema, define a separate rehearsal policy with explicit safeguards; the staged read-only proposal above does not establish a general write or DDL promotion policy.
Quick Recap
Calibrate the policy instead of copying thresholds
- Collect plans and canary observations from the workloads and environments the policy is meant to cover.
- Track where estimated rows or plan costs fail to flag candidates that later show concerning behavior.
- Review both false alarms and misses before changing a threshold or escalation rule.
- Reassess the calibration after changes to data distribution, statistics, configuration, hardware, or workload.
- Keep the timeout, cost ceiling, row trigger, and escalation criteria explicitly local; none is established here as a universal best practice.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
