Re: Question about indexes

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> >
> > I would see that as the next step, But it seems to me it would be only a small
> > set of queries where it would really help enough to outweigh the extra work of
> > the sort.
>
> What sort?

To build the in-memory bitmap you effectively have to do a sort. If the tuples
come out of the index in heap order then you can combine them without having
to go through that step.

> I'm a little dubious that true bitmap indexes would be worth building
> for Postgres. Seems like partial indexes cover the same sorts of
> applications and are more flexible.

I'm clear on the distinction. I think bitmap indexes still have a place, but
if regular btree indexes could be combined efficiently then that would be an
even narrower niche.

Partial indexes are very handy, and they're useful in corner cases where
bitmap indexes are useful, such as flags for special types of records.

But I think bitmap indexes are specifically wanted by certain types of data
warehousing applications where you have an index on virtually every column and
then want to do arbitrary boolean combinations of all of them. btree indexes
would generate more i/o scanning all the indexes than just doing a sequential
scan would. Whereas bitmap indexes are much denser on disk.

However my experience leans more towards the OLTP side and I very rarely saw
applications like this.

--
greg