From: Markus Schaber <schabi@logix-tt.com>
To: Oscar Picasso <oscgoogle@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Better index stategy for many fields with few values
Date: Wed, 12 Apr 2006 14:59:32 +0200
Message-ID: <443CF9B4.2080009@logix-tt.com> (raw)
In-Reply-To: <20060410165857.91471.qmail@web54705.mail.yahoo.com>
References: <20060410165857.91471.qmail@web54705.mail.yahoo.com>
Hi, Oscar,
Oscar Picasso wrote:
> [ all the 20 paramXX columns are used in the query}
> How can I optimize this kind of query?
PostgreSQL 8.1 has so-called bitmap index scans, which can combine
several index scans before actually accessing the data.
So I think it's best to create an index on each of the paramXX columns,
and see with EXPLAIN ANALYZE what it is doing.
> I was thinking about using a multicolumns index, but I have read that
> we should limit multicolumns indice to at most 2 or 3 columns.
Yes, that's true, the index overhead gets too high.
> If that's true then 22 columns for a multicolumn incdex seems way too
> much. Or maybe it is workable as every column uses only a very limited
> set of values?
Yes, I think that a 22 column index is way too much, especially with the
new bitmap index scans available.
> I was also thinking about about using a functional index.
If there's a logical relation between those values that they can easily
combined, that may be a good alternative.
I just had another weird idea:
As your paramXX values can have only 10 parameters, it also might be
feasible to use a bunch of 10 conditional indices, like:
CREATE INDEX foo1 ON table (param1, param2 WHERE param0='1st value';
CREATE INDEX foo2 ON table (param1, param2 WHERE param0='2nd value';
CREATE INDEX foo3 ON table (param1, param2 WHERE param0='3rd value';
[...]
This way, you don't have the index bloat of a 3-column index, but 10
2-column indices that cover 1/10th of the table each.
For 22 columns, you'd need a bunch of seven such indices plus a
single-column one, or can use some 3+1 and some 2+1 column index.
I'd like to see the query plans from explain analyze.
Btw, I expect query planning time to get rather significant for so much
columns, so gequo tuning, tuning work_mem (for the bitmap scans) and
prepared statements will pay off.
HTH,
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, oscgoogle@yahoo.com
Subject: Re: Better index stategy for many fields with few values
In-Reply-To: <443CF9B4.2080009@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