Re: Optimizing prepared statements

On Sun, September 3, 2006 23:52, Tom Lane wrote:

> What exactly do you mean by "optimize away a parameter"? The way you
> described the mechanism, there are no parameters that are "optimized
> away", you've merely adjusted selectivity predictions using some assumed
> values.

I'm using "optimized away" as shorthand for "replaced with a literal
constant in the statement definition used to generate the plan." So if a
parameter $n is found to be, say, always equal to the string 'foo', then
we might want to generate a specialized plan as if the statement's
definition contained the literal string 'foo' wherever it really says $n.
I've been calling that "optimized for $n=='foo'" or "$n is optimized away
in this plan."

> Actually converting a parameter to a constant is a whole
> 'nother matter --- it allows constant-folding for example. But then you
> *cannot* use the plan unless there's an exact match to the assumed
> value. These two approaches provide very different tradeoffs of plan
> quality vs plan specificity, so it makes me uncomfortable that you're
> failing to clarify what you mean.

Right. When I said "optimized for" a certain parameter value, I meant
actual substitution the whole time. I'm sorry if I didn't make that
clear; it seemed so basic that I must have forgotten to mention it. I
guess the principle would also work otherwise, but it's intended to allow
constant folding.

So for any given statement, there would be a cache of frequently-needed
plans for different sets of constant substitutions. As you point out, a
call could only use a plan if the plan's substitutions were consistent
with the call's parameter values (but within that constraint, the more
substitutions the merrier). That's why I talked so much about comparing
and matching: that part is for correctness, not optimization.

As I've said before, all this falls down if there is a significant cost to
keeping one or two extra plans per prepared statement. You mentioned
something about "tracking" plans. I don't know what that means, but it
sounded like it might impose a runtime cost on keeping plans around.
Could you elaborate?

Jeroen