Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
>
>>This leads to my next problem (which was one of the original reasons I
>>went with node replacement): don't we get different performance between
>>a parameterized query and an equivalent unparameterized query in cases
>>such as this? :
>
>
>> SELECT * FROM sometable WHERE field = $1 * 10
>
>
> I don't think the run-time performance would be materially different in
> most cases. What might happen is that if the selectivity functions are
> stupid, they would fail to reduce the comparison value to a constant and
> thus not be able to arrive at a good selectivity estimate, thus leading
> to a bad plan choice.
This appears to be true for all the selectivity functions in
utils/adt/selfuncs.c -- they rely on being given a constant-folded
expression tree.
> However we need not put up with the selectivity
> functions being that stupid. I think it would be reasonable to
> constant-fold expressions involving known parameter values *within the
> context of selfuncs.c only*. This would let us get good estimates
> without giving up the usability of the plan for fresh parameter values.
Ok, so something like this?
1) eval_const_expressions takes a list of parameter values and does
Param to Const replacement if given parameters.
2) The main planner does not pass parameters to eval_const_expressions.
3) When the selectivity functions care about a Const vs non-Const value
and they are dealing with a non-leaf expression node, they call
eval_const_expressions passing the expression tree & the planner-global
parameter values, and then look at the result's Const-ness again.
The alternative is to duplicate much of the logic of
eval_const_expressions into a helper function for anything that cares
about constants.
...
There appear to be a few other places where Const vs. Param affects the
resulting plan (clause_selectivity, LIMIT/OFFSET support, etc). Do the
same thing there?
-O
