pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedArray access performance
4+ messages / 2 participants
[nested] [flat]
* Array access performance
@ 2011-08-02 13:00 Andreas Brandl <ml@3.141592654.de>
0 siblings, 2 replies; 4+ messages in thread
From: Andreas Brandl @ 2011-08-02 13:00 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm looking for a hint how array access performs in PostgreSQL in respect to performance. Normally I would expect access of a 1-dimensional Array at slot i (array[i]) to perform in constant time (random access).
Is this also true for postgres' arrays?
May concrete example is a 1-dimensional array d of length <= 600 (which will grow at a rate of 1 entry/day) stored in a table's column. I need to access this array two times per tuple, i.e. d[a], d[b]. Therefore I hope access is not linear. Is this correct?
Also I'm having some performance issues building this array. I'm doing this with a used-defined aggregate function, starting with an empty array and using array_append and some calculation for each new entry. I assume this involves some copying/memory allocation on each call, but I could not find the implementation of array_append in postgres-source/git.
Is there an efficient way to append to an array? I could also start with a pre-initialized array of the required length, but this involves some complexity.
Thank you
Regards,
Andreas
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Array access performance
@ 2011-08-02 13:11 Andreas Brandl <ml@3.141592654.de>
parent: Andreas Brandl <ml@3.141592654.de>
1 sibling, 0 replies; 4+ messages in thread
From: Andreas Brandl @ 2011-08-02 13:11 UTC (permalink / raw)
To: pgsql-performance
> Is this also true for postgres' arrays?
Sorry, I'm using latest postgres 9.0.4 on debian squeeze/amd64.
Greetings
Andreas
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Array access performance
@ 2011-08-02 14:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas Brandl <ml@3.141592654.de>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2011-08-02 14:49 UTC (permalink / raw)
To: Andreas Brandl <ml@3.141592654.de>; +Cc: pgsql-performance
Andreas Brandl <ml@3.141592654.de> writes:
> I'm looking for a hint how array access performs in PostgreSQL in respect to performance. Normally I would expect access of a 1-dimensional Array at slot i (array[i]) to perform in constant time (random access).
> Is this also true for postgres' arrays?
Only if the element type is fixed-length (no strings for instance) and
the array does not contain, and never has contained, any nulls.
Otherwise a scan through all the previous elements is required to find
a particular element.
By and large, if you're thinking of using arrays large enough to make
this an interesting question, I would say stop right there and redesign
your database schema. You're not thinking relationally, and it's gonna
cost ya.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Array access performance
@ 2011-08-02 15:16 Andreas Brandl <ml@3.141592654.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Andreas Brandl @ 2011-08-02 15:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi Tom,
> > I'm looking for a hint how array access performs in PostgreSQL in
> > respect to performance. Normally I would expect access of a
> > 1-dimensional Array at slot i (array[i]) to perform in constant time
> > (random access).
>
> > Is this also true for postgres' arrays?
>
> Only if the element type is fixed-length (no strings for instance) and
> the array does not contain, and never has contained, any nulls.
> Otherwise a scan through all the previous elements is required to find
> a particular element.
We're using bigint elements here and don't have nulls, so this should be fine.
> By and large, if you're thinking of using arrays large enough to make
> this an interesting question, I would say stop right there and
> redesign
> your database schema. You're not thinking relationally, and it's gonna
> cost ya.
In general, I agree. We're having a nice relational database but are facing some perfomance issues. My approach is to build a materialized view which exploits the array feature and heavily relies on constant time access on arrays.
Thank you!
Regards,
Andreas
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-08-02 15:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-02 13:00 Array access performance Andreas Brandl <ml@3.141592654.de>
2011-08-02 13:11 ` Andreas Brandl <ml@3.141592654.de>
2011-08-02 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-08-02 15:16 ` Andreas Brandl <ml@3.141592654.de>
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