Skip site navigation (1) Skip section navigation (2)

Re: Bitmap indexes

From: Christopher Browne <cbbrowne(at)ca(dot)afilias(dot)info>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Bitmap indexes
Date: 2005-01-28 19:45:17
Message-ID: 60r7k5cp82.fsf@dba2.int.libertyrms.com (view raw or flat)
Thread:
Lists: pgsql-performance
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)

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2005-01-28 19:48:35
Subject: Re: Poor Performance on Postgres 8.0
Previous:From: Alexandre LeclercDate: 2005-01-28 19:26:57
Subject: Re: Flattening a kind of 'dynamic' table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group