From: andrew klassen <aptklassen@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: index usage on arrays
Date: Thu, 7 Feb 2008 10:38:52 -0800 (PST)
Message-ID: <60380.61376.qm@web37305.mail.mud.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aptklassen@yahoo.com
Subject: Re: index usage on arrays
In-Reply-To: <60380.61376.qm@web37305.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox