Re: Functional indexes with slow functions are misplanned

On Tue, Mar 3, 2015 at 4:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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.
>

The changes you made in HEAD solved the problem.  Now I'll have a reason to
upgrade once 9.5 comes out.

Thanks,

Jeff