Date: Fri, 04 Apr 2008 13:02:59 +0200
To: pgsql-performance@postgresql.org
Subject: Re: Forcing more agressive index scans for BITMAP AND
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <1207280383.28172.26.camel@neuromancer.home.net>
	<Pine.LNX.4.64.0804041137500.20402@aragorn.flymine.org>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t822a9l2cigqcu@apollo13.peufeu.com>
In-Reply-To: <Pine.LNX.4.64.0804041137500.20402@aragorn.flymine.org>
User-Agent: Opera Mail/9.24 (Linux)


> On Fri, 4 Apr 2008, Ow Mun Heng wrote:
>> select * from table
>> where A=3DX
>> and B =3D Y
>> and C =3D Z
>> and D =3D AA
>> and E =3D BB

	With that kind of WHERE condition, Postgres will use a Bitmap Index Sca=
n  =

to combine your indices. If, however, postgres notices while looking at =
 =

the statistics gathered during ANALYZE, that for one of your columns, yo=
u  =

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 bothe=
r  =

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 sca=
n  =

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 ind=
ex  =

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 i=
n  =

them, how many distinct values, etc ?