X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0B3153A60F9 for ; Fri, 28 Jan 2005 20:04:12 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96232-02 for ; Fri, 28 Jan 2005 20:04:06 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id 138133A6053 for ; Fri, 28 Jan 2005 20:04:06 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 3DE6D31DA0; Fri, 28 Jan 2005 21:04:06 +0100 (MET) From: Christopher Browne X-Newsgroups: pgsql.performance Subject: Re: Bitmap indexes Date: Fri, 28 Jan 2005 14:45:17 -0500 Organization: Afilias Canada - Operations Group Lines: 42 Message-ID: <60r7k5cp82.fsf@dba2.int.libertyrms.com> References: <33c6269f0501280739344f6a18@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Corporate Culture, linux) Cancel-Lock: sha1:r75Tn06eM8lbi/x4gXQRIkqnR4I= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.041 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/595 X-Sequence-Number: 10285 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" Christopher Browne (416) 673-4124 (land)