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
builda materialized view which exploits the array feature and heavily relies on constant time access on arrays.
Thank you!
Regards,
Andreas