From aptklassen@yahoo.com Thu Feb  7 18:38:58 2008
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--



From oleg@sai.msu.su Thu Feb  7 18:55:50 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id B65C92E02A1
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  7 Feb 2008 14:55:50 -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 41995-05 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  7 Feb 2008 14:55:45 -0400 (AST)
Received: from sn.sai.msu.ru (sn.sai.msu.ru [195.208.220.215])
	by postgresql.org (Postfix) with ESMTP id E700A2E0206
	for <pgsql-performance@postgresql.org>;
	Thu,  7 Feb 2008 14:55:46 -0400 (AST)
Received: from sn.sai.msu.ru (localhost [127.0.0.1])
	by sn.sai.msu.ru (8.14.1/8.12.8) with ESMTP id m17ItiuZ006126;
	Thu, 7 Feb 2008 21:55:44 +0300
Received: from localhost (megera@localhost)
	by sn.sai.msu.ru (8.14.1/8.12.8/Submit) with ESMTP id m17ItiDb006123;
	Thu, 7 Feb 2008 21:55:44 +0300
X-Authentication-Warning: sn.sai.msu.ru: megera owned process doing -bs
Date: Thu, 7 Feb 2008 21:55:44 +0300 (MSK)
From: Oleg Bartunov <oleg@sai.msu.su>
X-X-Sender: megera@sn.sai.msu.ru
To: andrew klassen <aptklassen@yahoo.com>
cc: pgsql-performance@postgresql.org
Subject: Re: index usage on arrays
In-Reply-To: <60380.61376.qm@web37305.mail.mud.yahoo.com>
Message-ID: <Pine.LNX.4.64.0802072154490.13757@sn.sai.msu.ru>
References: <60380.61376.qm@web37305.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/89
X-Sequence-Number: 28631

andrew,
what are your queries ? Have you seen contrib/intarray, 
GIN index ?

On Thu, 7 Feb 2008, andrew klassen wrote:

> 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
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83



From tgl@sss.pgh.pa.us Thu Feb  7 19:02:09 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 906672E4410
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  7 Feb 2008 15:02:09 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 13110-07-3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  7 Feb 2008 15:02:06 -0400 (AST)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 9BDC02E3A29
	for <pgsql-performance@postgresql.org>;
	Thu,  7 Feb 2008 15:01:59 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m17J1wCx008144;
	Thu, 7 Feb 2008 14:01:58 -0500 (EST)
To: andrew klassen <aptklassen@yahoo.com>
cc: pgsql-performance@postgresql.org
Subject: Re: index usage on arrays 
In-reply-to: <60380.61376.qm@web37305.mail.mud.yahoo.com> 
References: <60380.61376.qm@web37305.mail.mud.yahoo.com>
Comments: In-reply-to andrew klassen <aptklassen@yahoo.com>
	message dated "Thu, 07 Feb 2008 10:38:52 -0800"
Date: Thu, 07 Feb 2008 14:01:58 -0500
Message-ID: <8143.1202410918@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/90
X-Sequence-Number: 28632

andrew klassen <aptklassen@yahoo.com> writes:
> Is there any alternative to what am I currently doing other than creating a row for 
> each array element,

Since (I think) 8.2, you could create a GIN index on the array column
and then array overlap (&&) would be indexable.  GIN has some
performance issues if the table is heavily updated, though.

			regards, tom lane