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
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 Lane||Date: 2008-02-07 19:01:58|
|Subject: Re: index usage on arrays |
|Previous:||From: andrew klassen||Date: 2008-02-07 18:38:52|
|Subject: index usage on arrays|