Re: Way to avoid expensive Recheck Cond in index lookup?

> But it's true that it's possible for a slow expression to make the recheck
> very expensive. The planner doesn't have a very good understanding of how
> to
> tell whether the expression is likely to be slow.
>
> The case I ran into is thing like "WHERE x = ANY $1::integer[]" which
> become
> very slow for very large arrays. So I'm sure xpath() could possibly
> trigger
> the same case.
>
> But the number of matching pages would have to be quite large. And in that
> case the alternative (regular index scans) is going to suck too.

So the actual index function expression is _only_ evaluated in the
re-check for some (or all?) matches, if there are more matching pages than
can fit into the memory allocated by work_mem?

I also seemed to notice that after running a query that did return a large
number of results where the query plan did use the text[] index, running
the same query, or a similar one, would stop using the index lookup and
just do a full table scan. Would that be the optimizer changing plans
because of the statistics it gathered when it ran the query initially with
the index lookup but found the re-check condition took such a long time to
execute?

What I was trying to accomplish was to define a text[] index created from
the results of an xpath() expression, for the purposes of being able to do
fast index lookups using the && operator. But I'm finding that even when
the index is used, the query is very slow and I was assuming it was coming
from the re-check condition, which is defined as that xpath() call. So I'm
finding that this approach isn't working out as I had hoped.

-- m@