Re: Recheck condition

On Fri, Nov 30, 2007 at 11:27:24AM -0500, Josh Harrison wrote:
> Thanks for your reply
> Is there a way to get them not to use the
> heap for intermediate result and go to heap only for final data? This will
> drastically improve the performance but Im not sure if postgres can do that?
> Will creating the index in a different way and/or rewriting the query in a
> different way achieve this result?

I'm trying to imagine what it would take to avoid the heap access after
the index scan I don't think it's possible. It would require that the
bitmaps generated by the bitmap scan have the person_id attached and
then have the bitmap AND operation only happen if the person IDs match.
No such machinary currently exists.

You do have somewhat of a worst case scenario, intersecting 300k
records with 3million records. I'm not sure if there is a good way to
handle that. The only things I can think of is to add the person_id to
the index also so that you can avoid the sort (not sure if first or
last is more helpful). Or perhaps clustering on that index to reduce
disk access...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy