pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / 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