Skip site navigation (1) Skip section navigation (2)

index usage on arrays

From: andrew klassen <aptklassen(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: index usage on arrays
Date: 2008-02-07 18:38:52
Message-ID: 60380.61376.qm@web37305.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
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 

Responses

pgsql-performance by date

Next:From: Oleg BartunovDate: 2008-02-07 18:55:44
Subject: Re: index usage on arrays
Previous:From: Kenneth MarshallDate: 2008-02-07 17:15:44
Subject: Re: Benchmark Data requested --- pgloader CE design ideas

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group