Re: index usage on arrays

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