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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-02-07 19:01:58 | Re: index usage on arrays |
Previous Message | andrew klassen | 2008-02-07 18:38:52 | index usage on arrays |