Re: Bitmap indexes?

On Tue, 19 Mar 2002, Oleg Bartunov wrote:

Sorry to reply over you, Oleg.

> On 13 Mar 2002, Greg Copeland wrote:
>
> > One of the reasons why I originally stated following the hackers list is
> > because I wanted to implement bitmap indexes. I found in the archives,
> > the follow link, http://www.it.iitb.ernet.in/~rvijay/dbms/proj/, which
> > was extracted from this,
> > http://groups.google.com/groups?hl=en&threadm=01C0EF67.5105D2E0.mascarm%40mascari.com&rnum=1&prev=/groups%3Fq%3Dbitmap%2Bindex%2Bgroup:comp.databases.postgresql.hackers%26hl%3Den%26selm%3D01C0EF67.5105D2E0.mascarm%2540mascari.com%26rnum%3D1, archive thread.

For every case I have used a bitmap index on Oracle, a
partial index[0] made more sense (especialy since it
could usefully be compound).

Our troublesome case (on Oracle) is a table of "events"
where maybe fifty to a couple of hundred are "published"
(ie. web-visible) at any time. The events are categorised
by sport (about a dozen) and by "event type" (about five).
We never really query events except by PK or by sport/type/
published.

We make a bitmap index on "published", and trust Oracle to
use it correctly, and hope that our other indexes are also
useful.

On Postgres[1] we would make a partial compound index:

create index ... on events(sport_id,event_type_id)
where published='Y';

Matthew.

[0] Is this a postgres-only feature; my tame Oracle and
Sybase DBAs had never heard of such a thing, but
were rather impressed at the idea.
[1] Disclaimer. Our system doesn't run on PG, though I
do have a nearly equivalent prototype system which
does. I'd love to hear any success (or otherwise)
stories about PG partial indexes.