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 55F9211F6006
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Apr 2006 13:59:14 -0300 (ADT)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Score: 1.359
X-Spam-Level: *
X-Spam-Status: No, score=1.359 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479,
	DNS_FROM_RFC_WHOIS=0.879, HTML_MESSAGE=0.001]
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id NkuZS1HGdIaw
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Apr 2006 13:58:59 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from web54705.mail.yahoo.com (web54705.mail.yahoo.com
	[206.190.49.195])
	by postgresql.org (Postfix) with SMTP id 2859511F68ED
	for <pgsql-performance@postgresql.org>;
	Mon, 10 Apr 2006 13:58:58 -0300 (ADT)
Received: (qmail 91473 invoked by uid 60001); 10 Apr 2006 16:58:57 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=YnA2AhUzHTCDsAeXWJuJzQknK+OUy0vwyH0dFwojTBiYZYNg1tm5SRHf9vzdNsf5Ufj5wFKntb+EF7XJmg/nFr5xuLbQ1zJxnww4EcAICV4ndla+OW9D2liSkAEXQEev50Crz6D4QR1HukjoWLXdOPwH8+9NLggLFR2+p4HTxSA=
	;
Message-ID: <20060410165857.91471.qmail@web54705.mail.yahoo.com>
Received: from [70.83.4.60] by web54705.mail.yahoo.com via HTTP;
	Mon, 10 Apr 2006 09:58:57 PDT
Date: Mon, 10 Apr 2006 09:58:57 -0700 (PDT)
From: Oscar Picasso <oscgoogle@yahoo.com>
Subject: Better index stategy for many fields with few values
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-459319516-1144688337=:91455"
Content-Transfer-Encoding: 8bit
X-Archive-Number: 200604/211
X-Sequence-Number: 18295

--0-459319516-1144688337=:91455
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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.
--0-459319516-1144688337=:91455
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi,<br><br>I want to optimize something like this.<br><br>- My items table:<br>code int&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- can take one of 100 values<br>property varchar(250) -- can take one of 5000 values<br>param01 char(10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- can take one of 10 values<br>param02 char(10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- can take one of 10 values<br>...<br>[ 20 similar columns }<br>...<br>parama20 char(10)&nbsp;&nbsp;&nbsp;&nbsp; -- can take one of 10 values<br><br>- The kind of query I want to optimize:<br>select * from items<br>where code betwwen 5 and 22<br>and param01 = 'P'<br>and param02 = 'G'<br>...<br>[ all the 20 paramXX columns are used in the query}<br>...<br>and param20 = 'C';<br><br><br>How can I optimize this kind of query? <br><br>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. <br><br>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?<br><br>I was also thinking about about using a functional index. <br><br>What do you think would be the best solution in such a case?<br><br>Thanks.<br><br>Oscar<br><br><p>
		<hr size=1>Blab-away for as little as 1¢/min. Make <a href="http://us.rd.yahoo.com/mail_us/taglines/postman2/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com"> PC-to-Phone Calls</a> using Yahoo! Messenger with Voice.
--0-459319516-1144688337=:91455--