Thread: General Postgres performance tips when using ARRAY

So, what are the gotcha's around manipulating Arrays in stored procs?
It seems reasonable that an array_cat /etc would cause the creation of a new array, but does mutating an existing array also create a copy?

On Wed, May 25, 2011 at 9:17 PM, bubba postgres
<bubba.postgres@gmail.com> wrote:
>
> So, what are the gotcha's around manipulating Arrays in stored procs?
> It seems reasonable that an array_cat /etc would cause the creation of a new
> array, but does mutating an existing array also create a copy?

Never, ever, if at all possible, build arrays with array_cat, ||
operator, etc.   Try not to work with arrays iteratively.  It will be
very slow.  You have better options:

1. subquery array constructor:
array_var := array(select bar from foo where ...);

2. array_agg()
select into array_var array_agg(bar) from foo where ...

3. values array constructor:
array_var := array[1, 2, 3];

don't forget, in recent postgres, you can make arrays of composite
types as well, and also nest:
complex_type := array(select row(bar, array(select baz from bat where
..))  from foo);

For expanding arrays prefer unnest() and check out the coming 9.1
foreach feature
(http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY):

merlin