Обсуждение: index usage on arrays
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
Never miss a thing. Make Yahoo your homepage.
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
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