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.