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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
