Re: Functional indexes with slow functions are misplanned

Jeff Janes <jeff.janes@gmail.com> writes:
> When the results of an expression can be obtained from a functional index,
> the expression never needs to be evaluated.  But the planner doesn't seem
> to know that.  It thinks the expression is evaluated not only once per row,
> but multiple times, presumably at each step as it descends the btree.

Hmm ... there are a lot of things that are not done very well for
functional indexes, but at least part of this has nothing to do with that.

cost_index() thinks it can use list_difference_ptr() against the
indexquals list to separate out which restriction conditions will be
applied as filter conditions; but that hasn't worked reliably since the
equivalence class machinery was invented.  So there's about a 50-50 chance
that equality index conditions will be charged as though they had to be
evaluated at each row returned by the indexscan, though of course they are
not.  Usually this means no worse than one extra cpu_operator_cost per
row, but with an expensive qual condition it could mean a lot more.

I think that fully duplicating the logic to identify redundant quals
that's in create_indexscan_plan() would likely be a mistake: the effort to
prove quals redundant shouldn't be spent on what are only hypothetical
index paths.  But we could introduce the is_redundant_derived_clause check
relatively cheaply, and that's what would matter far more of the time than
the other things.

Arguably this is a bug fix, but I'm nervous about possibly destabilizing
plan choices in the back branches, so I'm inclined to change it in HEAD
only.

            regards, tom lane