From: Markus Schaber <schabi@logix-tt.com>
To: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Re: Better index stategy for many fields with few values
Date: Thu, 13 Apr 2006 16:00:59 +0200
Message-ID: <443E599B.3080305@logix-tt.com> (raw)
In-Reply-To: <20060413131206.97079.qmail@web54714.mail.yahoo.com>
References: <20060413131206.97079.qmail@web54714.mail.yahoo.com>
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
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: schabi@logix-tt.com
Subject: Re: Better index stategy for many fields with few values
In-Reply-To: <443E599B.3080305@logix-tt.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