Time enters the planner

How do mechanisms move a decision earlier, later, or away entirely, and what does each position cost?

CaseConstructed case · Ananya at Kiranamart

One store takes 9 minutes, the others 0.2 seconds

Kiranamart's "Store sales this week" dashboard runs a parameterized query: WHERE store_id = $1. For 390 of the 400 stores it returns in 0.2 seconds. For the biggest store, the one in the city centre, it takes 9 minutes. The SQL is the same. The statistics are fresh. Ananya runs the large store's query by hand with the literal value in the text, and it takes 6 seconds.

The same week, an analyst runs a one-off ad hoc query. EXPLAIN says the planner expected a join input of 5,000 rows. Actually 40 million arrived. The plan used a sort-merge join with a heavy shuffle, but a broadcast join would have been far better. It is too late to change the plan now. Or is it?

Both problems have one root: a decision was made at a time when the facts it needed were not yet available. This article is about the relationship between when a decision is made and when the information to make it well becomes available.

How it ends →

The first articles shared an assumption so basic that it was never stated: the optimizer decides, then execution obeys. Every mechanism in this article relaxes that assumption in one direction or the other. Time becomes an explicit variable, and the governing question is the gap between when a decision is made and when the information to make it well exists.

This is the "early versus late binding" dial from Data reduction, now the literal subject. There are three positions on the timeline, and each mechanism in this article sits on one:

Position	Mechanism	What it buys	What it costs
Decide earlier, reuse	Plan caching, prepared statements, compiled code	The decision cost is spread over many runs	The decision is made without the information that mattered
Decide later, revise	Adaptive execution, dynamic sizing	Measurements instead of estimates	A barrier, and determinism
Do not decide, reuse the answer	Result caching, subplan memoization	The whole computation	Storage, invalidation, and a bet that the query will recur

We take them in one deliberate order. Materialization versus pipelining comes before subplan caching, because it is not a peer of the others. It is the precondition for two of them: adaptivity and cross-query sharing are bought with the same thing.

(Operator speed itself, such as how fast a vectorized scan runs, is a matter for the execution engine. Here we cover only the planner's decisions about execution.)