75% of our query-plan storage was duplicate

Earlier this year we ran a backfill over every execution plan we had stored for one tenant. Thousands of rows. After fingerprinting, a few hundred distinct shapes. Three quarters of the table was the same plan written down again with slightly different numbers.

That was not a surprise in the way it should have been. We had a conflict clause on the insert path specifically to stop this. It named a unique index that did not exist. Postgres does not complain when an ON CONFLICT DO NOTHING references a constraint that is not there; the clause simply never fires. Every insert went through. We found it in the backfill numbers, not in a log.

What a plan looks like when you strip the noise

An EXPLAIN (FORMAT JSON) output is mostly noise, if what you care about is whether the planner made a different decision. Costs move with statistics. Row estimates move with the table. Timing and buffers move with the cache. JIT flags move with the config. None of those mean the plan changed.

So the fingerprint keeps a deliberately short list: what each node is, what it touches, what it filters on, how it orders, and whether it chose to parallelize. The child plans come along recursively, in order, because outer versus inner matters.

Everything else is dropped. Then the literals inside the predicates are normalised, and the order of that normalisation matters more than it looks. Structured literals such as identifiers and timestamps are replaced first, then bind parameters and strings, and plain numbers last, because a numeric pass run first would eat the digits out of the identifiers and timestamps before their own patterns could match. The canonical form is sorted and hashed, and that hash is the plan’s identity.

kept · structure

• what the node is
• what it touches
• what it filters on
• how it orders
• whether it parallelizes
• children, in order

stripped · runtime

• costs
• row estimates
• timing
• buffers
• JIT flags
• extension-specific counters

normalized · in this order

• identifiers, timestampsfirst
• bind parameters, stringsthen
• plain numberslast

The concrete version-compatibility bug the normaliser exists to absorb is a cast that one major version prints and another omits. Same plan, different major version, different text. They must produce the same fingerprint or every upgrade looks like a regression.

The parts that were not Postgres

Every flavour we support puts something in the plan tree that is structural to it and noise to us. Time-series extensions add chunk-exclusion counters that change every time a chunk is added, without the plan changing at all, so they are stripped. Distributed engines add per-segment and per-node execution details, same treatment. A plan on those engines has to hash identically to the same plan without those fields.

Some engines do not produce JSON plans at all. Our first decision was to return no fingerprint rather than a wrong one. We later changed that for the distributed engines, because a plan with no fingerprint is a plan whose regressions are invisible. They now get a coarse fingerprint, marked so it can never collide with a precise one, that keeps shape and nesting and collapses numeric leaves. It is not a full parser for distributed EXPLAIN output. It is enough to notice when the shape moves.

Three layers

Dedup runs at three layers. The agent keeps a short-lived cache keyed on the raw plan hash so it does not resend the same plan every tick. The server checks the structural fingerprint against what exists before inserting. And a retention worker garbage-collects duplicates as a safety net, because a dedup path that depends on one check being right is a dedup path that will eventually store everything twice. Ours did, which is how we got to 75 percent, and why there are now three.

Why this changes regression detection

Plan storage is a cost problem. Plan identity is a correctness problem, and it was the reason we cared.

A plan regression, in our rule, requires two things at once: the plan changed, and the query got materially slower. Materially means a multiple of the best prior plan, sustained over several executions, inside a bounded window, with a higher multiple for critical. The planner re-chooses constantly and most of those choices are neutral. Slowdowns without a plan change are load or contention, and pretending otherwise generates alerts nobody trusts. We compare against the best prior duration, not the mean, so one slow historical sample cannot hide a real regression behind an inflated average. And it is a ratio, not milliseconds: two going to eight is as bad as two hundred going to eight hundred.

None of that works if two identical plans hash differently, because then every plan looks like a change and the rule fires on noise. And none of it works if two different plans hash the same, because then the change is invisible. The fingerprint is the thing the rule stands on. A regression is a sequence of plans over time, and the test fixtures say so explicitly, because a fixture where both plans share a timestamp tests nothing.

What you can take from this

If you are working through a regression right now rather than building the storage for one, query plan regression is the reference page for it.

If you store plans, hash them structurally and check whether your dedup is actually deduplicating. Count distinct fingerprints against row count; the ratio will tell you in one query. If your regression alerts fire too often, look at whether you are alerting on plan changes or on slowdowns, because the useful signal is the intersection. And if your ON CONFLICT names an index, go and check that the index exists. Ours did not.