Re: encourging bitmap AND - Mailing list pgsql-performance

From Tom Lane
Subject Re: encourging bitmap AND
Date
Msg-id 17713.1293384287@sss.pgh.pa.us
Whole thread Raw
In response to Re: encourging bitmap AND  (Marti Raudsepp <marti@juffo.org>)
Responses Re: encourging bitmap AND  (Jim Nasby <jim@nasby.net>)
List pgsql-performance
Marti Raudsepp <marti@juffo.org> writes:
> On Thu, Dec 23, 2010 at 22:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> That index structure is really entirely unsuited to what you want to do,
>> so it's not surprising that the planner isn't impressed with the idea of
>> a bitmap AND.

> Why is it unsuited for this query? It expands to (150 < s AND 150 > e)
>  which should work nicely with bitmap AND as far as I can tell.

Well, maybe for small values of "nicely".  If you do it like that, then
on average each indexscan will scan about half of its index and return a
bitmap representing about half the rows in the table.  That's an
expensive indexscan, and an expensive bitmap-AND operation, even if the
final number of rows out of the AND is small.  Plus you're at serious
risk that the bitmaps will become lossy, which degrades the performance
of the final bitmap heapscan.

If you're doing interval queries enough to worry about having an index
for them, you really want an indexing structure that is designed to do
interval queries efficiently.

            regards, tom lane

pgsql-performance by date:

Previous
From: Jeff Janes
Date:
Subject: Re: concurrent IO in postgres?
Next
From: "marc.hsiao"
Date:
Subject: How to turn autovacuum prevent wrap around run faster?