X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 377D511F610C for ; Wed, 12 Apr 2006 19:38:54 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -0.964 X-Spam-Level: X-Spam-Status: No, score=-0.964 required=5 tests=[BAYES_00=-2.599, FORGED_RCVD_HELO=0.135, RCVD_NUMERIC_HELO=1.5] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id pfZEur853ZbQ for ; Wed, 12 Apr 2006 19:38:50 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.Mi8.com (d01gw01.mi8.com [63.240.6.47]) by postgresql.org (Postfix) with ESMTP id 0F5DC11F605B for ; Wed, 12 Apr 2006 19:38:46 -0300 (ADT) Received: from 172.16.1.148 by mail.Mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D1)); Wed, 12 Apr 2006 18:38:17 -0400 X-Server-Uuid: 241911D6-425B-44B9-A073-E3FE0F8FC774 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.175]) by D01HOST02.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 12 Apr 2006 18:38:16 -0400 Received: from 67.103.45.218 ([67.103.45.218]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.106]) with Microsoft Exchange Server HTTP-DAV ; Wed, 12 Apr 2006 22:38:16 +0000 User-Agent: Microsoft-Entourage/11.2.3.060209 Date: Wed, 12 Apr 2006 15:38:14 -0700 Subject: Re: Better index stategy for many fields with few From: "Luke Lonergan" To: "Oscar Picasso" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] Better index stategy for many fields with few values Thread-Index: AcZcwF1L2EOfzjN4Rtua5KXP6AcjRQBwWuOd In-Reply-To: <20060410165857.91471.qmail@web54705.mail.yahoo.com> MIME-Version: 1.0 X-OriginalArrivalTime: 12 Apr 2006 22:38:16.0964 (UTC) FILETIME=[CA9BD440:01C65E81] X-WSS-ID: 68235ED30NK6238558-01-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/275 X-Sequence-Number: 18359 Oscar, On 4/10/06 9:58 AM, "Oscar Picasso" wrote: > - My items table: > code int -- can take one of 100 values > property varchar(250) -- can take one of 5000 values > param01 char(10) -- can take one of 10 values > param02 char(10) -- can take one of 10 values > ... > [ 20 similar columns } > ... > parama20 char(10) -- can take one of 10 values > > - The kind of query I want to optimize: > select * from items > where code betwwen 5 and 22 > and param01 = 'P' > and param02 = 'G' > ... > [ all the 20 paramXX columns are used in the query} > ... > and param20 = 'C'; Bizgres 0.9 has an on-disk bitmap index which will likely improve this query speed by a very large amount over normal Postgres 8.1. - Luke