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,
archivethread.
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
ratherimpressed at the idea.
[1] Disclaimer. Our system doesn't run on PG, though I do have a nearly equivalent prototype system which does.
I'dlove to hear any success (or otherwise) stories about PG partial indexes.
