Oliver Jowett <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
