Skip site navigation (1) Skip section navigation (2)

Re: index usage on arrays

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: andrew klassen <aptklassen(at)yahoo(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: index usage on arrays
Date: 2008-02-07 18:55:44
Message-ID: Pine.LNX.4.64.0802072154490.13757@sn.sai.msu.ru (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2008-02-07 19:01:58
Subject: Re: index usage on arrays
Previous:From: andrew klassenDate: 2008-02-07 18:38:52
Subject: index usage on arrays

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group