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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match