pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex usage on arrays
3+ messages / 3 participants
[nested] [flat]
* index usage on arrays
@ 2008-02-07 18:38 andrew klassen <aptklassen@yahoo.com>
0 siblings, 2 replies; 3+ messages in thread
From: andrew klassen @ 2008-02-07 18:38 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: index usage on arrays
@ 2008-02-07 18:55 Oleg Bartunov <oleg@sai.msu.su>
parent: andrew klassen <aptklassen@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Oleg Bartunov @ 2008-02-07 18:55 UTC (permalink / raw)
To: andrew klassen <aptklassen@yahoo.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: index usage on arrays
@ 2008-02-07 19:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: andrew klassen <aptklassen@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2008-02-07 19:01 UTC (permalink / raw)
To: andrew klassen <aptklassen@yahoo.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-02-07 19:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-07 18:38 index usage on arrays andrew klassen <aptklassen@yahoo.com>
2008-02-07 18:55 ` Oleg Bartunov <oleg@sai.msu.su>
2008-02-07 19:01 ` Tom Lane <tgl@sss.pgh.pa.us>
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