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

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Matt Magoffin" <postgresql(dot)org(at)msqr(dot)us> writes:
>> The problem for me is, the Recheck Cond is then on the xpath() function
>> used by the function-based index. My understanding is that then the
>> database must actually call the xpath() function again on all matches from
>> the index lookup.
>
> This is mistaken. It only happens if there are so many hits that the
> bitmap becomes lossy (which you can control to some extent anyway by
> adjusting work_mem).

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.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!