I've applied the patch you sent in for this, with some editorializations --- you were being too aggressive about substituting constants, with the net effect that the plan was not still parameterized as it was supposed to be.
Thanks. This should make my JDBC driver changes easier to sell.
I realized along the way that what we're really doing here is inventing
a notion of constant-folding expressions "for estimation purposes only".
As such, we don't have to be as rigid about making only provably safe
transformations as eval_const_expressions normally has to be. I didn't
do anything with the idea yet, but I'd like to look into having this
mode do more than just substitute Param values. An example that's been
causing us trouble for a long while is that the planner can't make any
nondefault selectivity estimate for
SELECT ... WHERE timestampcol > now() - '1 day';
because eval_const_expressions dare not reduce now() to current time.
But I think it would be entirely reasonable to do so "for estimation
purposes".
Something related I was pondering was adding a "constant expression at execution" flag to various expression nodes. eval_const_expressions would use this to mark expressions that are constant for a particular execution, but can't be constant-folded safely at planning time (essentially a STABLE modifier for expression trees).
The evaluate-for-estimation logic could use this to determine when it's safe to evaluate the whole expression as constant. I think this handles the now() case too, as STABLE functions are "constant at execution" if their arguments are.
At execution time the executor can cache the results of expressions flagged as constant at execution, assuming there's somewhere safe to cache the result for just that execution (ExprState?). This should make queries that use parameters in complex expressions go faster.
I took a quick look through the executor code, but couldn't see where STABLE function results are cached (for the same arguments). Does this currently happen? If not, we'd get that as well.
-O
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
