Skip to content
The Data EngineeringThe Data EngineeringBeta
About
Main content

Prepared statements and plan caching

Why does reusing a plan cause parameter sniffing, and which four escapes exist?

Planning has a fixed cost per query, so reuse spreads it over k executions. This is the fixed-cost-gives-a-batch-size idea once more, with the reuse count as the batch. The inequality is simple. The mechanism is not the interesting part.

The interesting part is that plan caching creates the most common pathology here, and it is

Example. A plan is compiled for WHERE country = 'LU', which returns 200 rows, so it uses an index lookup. The plan is then reused for country = 'US', which returns 200 million rows. The plan is optimal for the value it was compiled with, and catastrophic for the value it is used with. This is : the "sniffs" (looks at) the first parameter value and builds the plan for it. It is not a bug in any one product. It is the necessary consequence of a decision earlier than the value that the decision depends on.

In Ananya's case the plan was built the first time for one of the small stores, and cached.

There are exactly four positions, and they cover the space:

  1. Do not peek. Plan against the average from the and ignore the real parameter. Uniformly mediocre, never catastrophic. PostgreSQL's works this way. It is adopted after several (by default five) custom plans, if its estimated cost is competitive.
  2. Peek and specialize. Plan per parameter value or per class of values, and cache several plans. This costs planning time and cache space. Oracle's adaptive cursor sharing detects that the selectivity of a bind value differs materially and creates a second child cursor.
  3. Recompile every time. Correct, and pays the fixed planning cost on every execution.
  4. Plan for the worst plausible parameter. Optimize for survival, not for the expected case.

Options 1 and 4 are the same underlying move: choosing robustness over optimality. It is the practical resolution of this whole mechanism and here it has its clearest instance. It is also the only one of the four that is a stance and not a technique, and it separates that are pleasant to operate from those that are merely fast in benchmarks.

Cached plans are invalidated by DDL (schema changes) and by refresh. So the moment staleness ends is a plan-change event. Nothing about the query or the data changed at that instant, but ran and a plan flipped. This is the mechanical explanation for the most common operational mystery: "it was fine, then suddenly it wasn't, and we changed nothing." Plan instability does not arrive evenly. It clusters at analyze time. So staleness is not only a source of wrong estimates. It is also a source of discontinuity, and these are two separate harms.

In one sentence: plan reuse spreads a fixed planning cost and in return binds a decision before the value it depends on; the four escapes are do not peek, peek, always recompile, or plan for the worst; and the good ones choose robustness.