Re: encourging bitmap AND

Marti Raudsepp <marti(at)juffo(dot)org> writes:
> On Thu, Dec 23, 2010 at 22:52, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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