Re: Multi-column index not used, new flipped column index is

ogjunk-pgjedan(at)yahoo(dot)com <ogjunk-pgjedan(at)yahoo(dot)com> schrieb:

> Hi Andreas and Markus,
>
> Bitmap indices sound like a good reason to go from 8.0 to 8.1. Is 8.2
> around the corner, by any chance?

IIRC in autumn/winter. If it is finish ;-)

>
> I searched PG docs to see if I need to do something special to create
> Bitmap indices, but couldn't find any information. Are the indices
> created the same old CREATE INDEX .... ON Table(column(s)) way and it
> is just PG that can choose to use them in this "Bitmap index-way"?

Yes, you create indexes on the old way.

You have a table with many columns, say, a,b,c,d. On a and b you have a
index.

Now you have select a,b,c,d from table where a=N and b=M;

Prior, PG use one index first and then the other. Now, since 8.1, it
combines both indexes in memory first and need only one bitmap index
scan on the table. If you have many indexes, and suitable selects, you
get a high performance boost.

Read http://www.postgresql.org/docs/8.1/static/release-8-1.html, 2nd
point.

HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°