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 CBC4911F6C40 for ; Thu, 13 Apr 2006 11:00:55 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.364 X-Spam-Level: X-Spam-Status: No, score=-2.364 required=5 tests=[AWL=0.100, BAYES_00=-2.599, FORGED_RCVD_HELO=0.135] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id bI1U54rzA8Lh for ; Thu, 13 Apr 2006 11:00:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.logix-tt.com (unknown [212.211.145.186]) by postgresql.org (Postfix) with ESMTP id 3AF1111F6C54 for ; Thu, 13 Apr 2006 11:00:43 -0300 (ADT) Received: from kingfisher.intern.logix-tt.com (T88a7.t.pppool.de [89.55.136.167]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logix-tt.com (Postfix) with ESMTP id 21E3B656CB for ; Thu, 13 Apr 2006 16:01:38 +0200 (CEST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id A5401181C1DF1 for ; Thu, 13 Apr 2006 16:00:59 +0200 (CEST) Message-ID: <443E599B.3080305@logix-tt.com> Date: Thu, 13 Apr 2006 16:00:59 +0200 From: Markus Schaber Organization: Logical Tracking and Tracing International AG, Switzerland User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: PostgreSQL Performance List Subject: Re: Better index stategy for many fields with few values References: <20060413131206.97079.qmail@web54714.mail.yahoo.com> In-Reply-To: <20060413131206.97079.qmail@web54714.mail.yahoo.com> X-Enigmail-Version: 0.93.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/299 X-Sequence-Number: 18383 Hi, Oscar, Please reply to the list and not privately, so others can learn from your replies, and possibly have better Ideas than me. Oscar Picasso wrote: > I cannot group the columns logically. Any column may or may not appear > in a query. That's suboptimal. > Summrarizing what I have learned: > - I cannot use multicolumn indexes because I cannot group the column > logically. > - I cannot use funtional indexes > - I cannot use clustering. You still can have a set of partitioned multi-column indices, overlapping enough that every combination of columns is covered (or risk a sequential sub scan for the last two or three columns, this should not hurt too much if the first 17 columns were selective enough). The main problem with indices is that they also decrease write performance. If disk costs are not limited, it will make sense to have WAL, table and indices on different disks / raid arrays, to parallelize writes. Btw, I guess you have multiple, concurrent users? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org