From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marti Raudsepp <marti@juffo.org>
Cc: Ben <midfield@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: encourging bitmap AND
Date: Sun, 26 Dec 2010 12:24:47 -0500
Message-ID: <17713.1293384287@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTimvWF7BZo8Ko+wExE+pCfeRqvnz-Qp--2w=7h3C@mail.gmail.com>
References: <5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com>
<27812.1293137532@sss.pgh.pa.us>
<AANLkTimvWF7BZo8Ko+wExE+pCfeRqvnz-Qp--2w=7h3C@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, marti@juffo.org, midfield@gmail.com
Subject: Re: encourging bitmap AND
In-Reply-To: <17713.1293384287@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox