On Wed, Nov 28, 2007 at 02:20:11PM -0500, Josh Harrison wrote:
> >Some indexes are inexact, i.e. they may sometimes return tuples that
> >don't actually match the index condition.
>
> What causes an index to be inexact. When you create an index and vacuum it
> regularly, it is suppose to be correct....right??
The nature of the beast. For example, if you create an index on large
integer arrays it doesn't store the actual array in the index, but a
hashed version thereof. When we scan the index because of this hashing
it might match other arrays that shouldn't be. Hence the recheck.
Similarly for geometry indexes. The index only stores bounding boxes
and an intersection test might hit the bounding box but not match the
actual query.
> So does recheck condition affect the performance of the queries since it
> basically rechecks the condition?
> Also does it goes to the heap to retest ?
At the time of the recheck the data is already in memory. So no, it
doesn't go back to the heap.
> For example for this query
> explain analyze select count(*) from foo where foo_id=1 I get the following
> plan
It isn't the recheck that's costing it, it's probably just that you're
matching a lot of rows. A bitmap scan classically needs a recheck
because if a lot of rows need to be stored it might remember only
blocks 2044-2060. It then needs to recheck each row as it comes through
to make sure it really matches the conditions.
This query is 8ms, I imagine when it takes a long time it's matching
lots of rows?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy