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@sss.pgh.pa.us> wrote:

jarda.urik@gmail.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