X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 11AD311F61C6 for ; Mon, 10 Apr 2006 14:25:34 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.12 X-Spam-Level: X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id Rwzmkr5la9kk for ; Mon, 10 Apr 2006 14:25:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from peufeu.com (boutiquenumerique.com [82.67.9.10]) by postgresql.org (Postfix) with ESMTP id E660A11F61DB for ; Mon, 10 Apr 2006 14:25:27 -0300 (ADT) Received: (qmail 1041 invoked from network); 10 Apr 2006 19:26:14 +0200 Received: from boutiquenumerique.com (HELO apollo13) (82.67.9.10) by boutiquenumerique.com with SMTP; 10 Apr 2006 19:26:14 +0200 To: "Oscar Picasso" , pgsql-performance@postgresql.org Subject: Re: Better index stategy for many fields with few values References: <20060410165857.91471.qmail@web54705.mail.yahoo.com> Message-ID: Date: Mon, 10 Apr 2006 19:26:13 +0200 From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: <20060410165857.91471.qmail@web54705.mail.yahoo.com> User-Agent: Opera M2/8.51 (Linux, build 1462) X-Archive-Number: 200604/213 X-Sequence-Number: 18297 > - 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 Instead of 20 columns, you could instead use a "param" field containing an array of 20 TEXT fields. Then create a simple index on (code, param) and SELECT WHERE code BETWEEN ... AND param = '{P,G,....,C}' If you don't want to modify your structure, you can create a functional index on an array {param1...param20}, but your queries will be a bit uglier.