Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id BEAAC2E0332
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  7 Feb 2008 14:38:58 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 91630-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  7 Feb 2008 14:38:53 -0400 (AST)
Received: from web37305.mail.mud.yahoo.com (web37305.mail.mud.yahoo.com
	[209.191.90.248])
	by postgresql.org (Postfix) with SMTP id 1B0E12E2DE2
	for <pgsql-performance@postgresql.org>;
	Thu,  7 Feb 2008 14:38:54 -0400 (AST)
Received: (qmail 62812 invoked by uid 60001); 7 Feb 2008 18:38:53 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=CkxlRcXaBGgZE6BRi3JsBB6ykJDpc0xM5rtX80kXpGod+1uJPJI63x/vp7ia1nm/ck5nOGxcos3SeYXGNolPDAwr9nXgJAQakog/OMiUKhZXQAmZUdLj4CeR8mpVTM860sKW4JxiOAOJDa6YCwGWzX09fKi5+NPg3qzZGtCQnao=;
X-YMail-OSG: 
 T.V6d5cVM1lAxBhQIBONfBAl19V7VXpGKmokwWS56e8dVatrO5ZkXjIlHycnI3SFLJDqfdL9ixmZ6J1BSoZXFEcEp7KARB1Oq6waHSxAzC1cFSeMwaE-
Received: from [66.226.193.82] by web37305.mail.mud.yahoo.com via HTTP;
	Thu, 07 Feb 2008 10:38:52 PST
X-Mailer: YahooMailRC/818.31 YahooMailWebService/0.7.162
Date: Thu, 7 Feb 2008 10:38:52 -0800 (PST)
From: andrew klassen <aptklassen@yahoo.com>
Subject: index usage on arrays
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-2093284200-1202409532=:61376"
Message-ID: <60380.61376.qm@web37305.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/88
X-Sequence-Number: 28630

--0-2093284200-1202409532=:61376
Content-Type: text/plain; charset=us-ascii

I am using Postgres 8.2.5. 
 
I have a table that has rows containing a variable length array with a known maximum. 
I was doing selects on the array elements using an ANY match. The performance 
was not too good as my table got bigger. So I added an index on the array. 
That didn't help since the select was not using it.  I saw a thread in the 
mailing lists stating the index wouldn't be used. 
 
So I created indices on the individual array elements and then do a select
on each element separately and then combine each match using OR. 
This did substantially increase the select performance. However, it may 
be difficult to maintain this approach over time as the maximum array 
size may increase dramatically and forming the query will become tedious. 
 
Is there any alternative to what am I currently doing other than creating a row for 
each array element, i.e. stop using an array and use a separate row for each 
array index? The reason I didn't want to take this approach is because there are 
other columns in the row that will be duplicated needlessly.
 
Thanks, Andrew


      ____________________________________________________________________________________
Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 

--0-2093284200-1202409532=:61376
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV>I am using Postgres 8.2.5. </DIV>
<DIV>&nbsp;</DIV>
<DIV>I have a table that has rows containing a variable length array with a known maximum. </DIV>
<DIV>I was doing selects on the array elements using an&nbsp;ANY match. The performance </DIV>
<DIV>was not too good&nbsp;as my table got bigger. So I added an index on the array. </DIV>
<DIV>That didn't help since the select was not using it. &nbsp;I saw a&nbsp;thread&nbsp;in the </DIV>
<DIV>mailing lists&nbsp;stating the index wouldn't be used.&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>So I&nbsp;created indices on the individual&nbsp;array elements and then&nbsp;do a select</DIV>
<DIV>on each element separately&nbsp;and then combine each match using&nbsp;OR. </DIV>
<DIV>This did substantially increase the select&nbsp;performance. However, it may&nbsp;</DIV>
<DIV>be difficult to maintain this approach over time as the&nbsp;maximum array </DIV>
<DIV>size may increase dramatically and&nbsp;forming the query will become tedious. </DIV>
<DIV>&nbsp;</DIV>
<DIV>Is there any alternative to what am I currently doing other than creating a row for </DIV>
<DIV>each array element, i.e. stop using an array and use a separate row for each </DIV>
<DIV>array index? The reason&nbsp;I didn't want to take this approach is because there&nbsp;are </DIV>
<DIV>other&nbsp;columns in the row that will be duplicated needlessly.</DIV>
<DIV>&nbsp;</DIV>
<DIV>Thanks, Andrew</DIV>
<DIV>&nbsp;</DIV></div><br>

      <hr size=1>Never miss a thing.  <a href="http://us.rd.yahoo.com/evt=51438/*http://www.yahoo.com/r/hs"> Make Yahoo your homepage.</a>

</body></html>
--0-2093284200-1202409532=:61376--