pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@ca.afilias.info>
To: pgsql-performance@postgresql.org
Subject: Re: Bitmap indexes
Date: Fri, 28 Jan 2005 14:45:17 -0500
Message-ID: <60r7k5cp82.fsf@dba2.int.libertyrms.com> (raw)
References: <33c6269f0501280739344f6a18@mail.gmail.com>

armtuk@gmail.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)



view thread (7+ messages)  latest in thread

Message-ID: <60r7k5cp82.fsf@dba2.int.libertyrms.com>
Permalink:  ../60r7k5cp82.fsf@dba2.int.libertyrms.com/
Also on:    postgresql.org/message-id/60r7k5cp82.fsf@dba2.int.libertyrms.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: cbbrowne@ca.afilias.info
  Subject: Re: Bitmap indexes
  In-Reply-To: <60r7k5cp82.fsf@dba2.int.libertyrms.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox