From: PFC <lists@peufeu.com>
To: pgsql-performance@postgresql.org
Subject: Re: Forcing more agressive index scans for BITMAP AND
Date: Fri, 04 Apr 2008 13:02:59 +0200
Message-ID: <op.t822a9l2cigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0804041137500.20402@aragorn.flymine.org>
References: <1207280383.28172.26.camel@neuromancer.home.net>
<Pine.LNX.4.64.0804041137500.20402@aragorn.flymine.org>
> On Fri, 4 Apr 2008, Ow Mun Heng wrote:
>> select * from table
>> where A=X
>> and B = Y
>> and C = Z
>> and D = AA
>> and E = BB
With that kind of WHERE condition, Postgres will use a Bitmap Index Scan
to combine your indices. If, however, postgres notices while looking at
the statistics gathered during ANALYZE, that for one of your columns, you
request a value that happens in a large percentage of the rows (like 20%),
and this value has a rather random distribution, Postgres will not bother
scanning the index, because it is very likely that all the pages would
contain a row satisfying your condition anyway, so the time taken to scan
this huge index and mark the bitmap would be lost because it would not
allow a better selectivity, since all the pages would get selected for
scan anyway.
I would guess that Postgres uses Bitmap Index Scan only on your columns
that have good selectivity (ie. lots of different values).
So :
If you use conditions on (a,b) or (a,b,c) or (a,b,c,d) etc, you will
benefit GREATLY from a multicolumn index on (a,b,c,d...).
However, even if postgres can use some clever tricks, a multicolumn index
on (a,b,c,d) will not be optimal for a condition on (b,c,d) for instance.
So, if you mostly use conditions on a left-anchored subset of
(a,b,c,d,e), the multicolumn index will be a great tool.
A multicolumn index on (a,b,c,d,e) is always slightly slower than an
index on (a) if you only use a condition on (a), but it is immensely
faster when you use a multicolumn condition.
Can you tell us more about what those columns mean and what you store in
them, how many distinct values, etc ?
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: lists@peufeu.com
Subject: Re: Forcing more agressive index scans for BITMAP AND
In-Reply-To: <op.t822a9l2cigqcu@apollo13.peufeu.com>
* 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