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

From: "Matt Magoffin" <postgresql(dot)org(at)msqr(dot)us>
To: "Gregory Stark" <stark(at)enterprisedb(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org
Subject: Re: Way to avoid expensive Recheck Cond in index lookup?
Date: 2007-12-19 19:16:52
Message-ID: 49240.192.168.1.108.1198091812.squirrel@msqr.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Louis-David Mitterrand 2007-12-19 19:31:32 postgres UTC different from perl?
Previous Message Scott Marlowe 2007-12-19 19:16:44 Re: Problem with collector statistic