From: Oscar Picasso <oscgoogle@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Better index stategy for many fields with few values
Date: Mon, 10 Apr 2006 09:58:57 -0700 (PDT)
Message-ID: <20060410165857.91471.qmail@web54705.mail.yahoo.com> (raw)
Hi,
I want to optimize something like this.
- 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';
How can I optimize this kind of query?
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.
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?
I was also thinking about about using a functional index.
What do you think would be the best solution in such a case?
Thanks.
Oscar
---------------------------------
Blab-away for as little as 1ยข/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.
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: oscgoogle@yahoo.com
Subject: Re: Better index stategy for many fields with few values
In-Reply-To: <20060410165857.91471.qmail@web54705.mail.yahoo.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