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
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.
(416) 673-4124 (land)
In response to
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2005-01-28 19:48:35|
|Subject: Re: Poor Performance on Postgres 8.0 |
|Previous:||From: Alexandre Leclerc||Date: 2005-01-28 19:26:57|
|Subject: Re: Flattening a kind of 'dynamic' table|