Re: BUG #14949: array_append() - performance issues (in update)

Hi,

Thanks for quick response.

Is it possible that there might be something wrong with the table when it
was migrated from 9.6.6 -> 10.1 via pg_dump/restore? I have examined it
visually and the data seemed fine..

I will try to provide (not) working example on monday (currently travelling
without stable internet connection).

Regards,
Jaroslav Urik

On Wed, Dec 6, 2017, 17:40 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> jarda(dot)urik(at)gmail(dot)com writes:
> > I am having (time) issues with UPDATE of Array of INTEGERs:
>
> > Update eg:
> > UPDATE id_mng.id_pair SET segment = array_append(segment,7) WHERE
> > project_guid = '...' AND id_p IN ( SELECT... )
>
> Hm. In my hands, an array_append update like that doesn't seem to be
> any slower in v10 than prior versions. I tried both narrow and wide
> initial array values, like this:
>
> drop table if exists id_pair;
> create table id_pair(segment int[]);
>
> insert into id_pair select array[i,i+1] from generate_series(1,1000000) i;
>
> \timing on
> update id_pair set segment = array_append(segment,7);
> \timing off
>
> truncate id_pair;
> insert into id_pair select array_agg(i) from generate_series(1,1000) i;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
> insert into id_pair select * from id_pair;
>
> \timing on
> update id_pair set segment = array_append(segment,7);
> \timing off
>
> For me, each of these updates runs in 5 to 6 seconds in any supported
> PG version.
>
> Can you provide a self-contained test case that shows what you're
> talking about?
>
> regards, tom lane
>