Re: Bitmap indexes

armtuk(at)gmail(dot)com (Alex Turner) writes:

> I was wondering about index types. Oracle has an index type called a
> 'bitmap' index. They describe this as an index for low cardinality
> fields, where only the cardinal values are indexed in a b-tree, and
> then it uses a bitmap below that to describe rows. They say that this
> type of index is very fast when combined with queries that used the
> indexed row in 'AND' clauses in a sql statement as the index can
> 'mask' the results very fast. I have not been able to benchmark the
> actual effectiveness of this kind of index, but I was wondering if
> anyone has had experience with this an believes it might be a useful
> feature for postgres?
>
> Yes I have a vested interest in this because alot of my searches are
> masked against low cardinality fields 'Y' or 'N' type things where
> this could potentialy benefit me...

There are some ideas on this; nothing likely to be implemented in the
very short term.

If you do a lot of queries on this sort of basis, there's something in
PostgreSQL known as a "partial index" that could be used to improve
some queries.

What you might do is something like:

create index partial_y_for_field_a on some_table (id_column)
where field_a = 'Y';
create index partial_n_for_field_a on some_table (id_column)
where field_a = 'N';

That could provide speedup for queries that might do joins on
id_column where your query has the qualifiers "where field_a = 'Y'" or
"where field_a = 'N'".

That's not going to provide a generalized answer to "star queries,"
but it is an immediate answer for some cases.
--
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)