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

The following bug has been logged on the website:

Bug reference:      14949
Logged by:          Jaroslav Urik
Email address:      jarda.urik@gmail.com
PostgreSQL version: 10.1
Operating system:   Ubuntu server 16.04
Description:

Hello, 
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... )


Table definition:
CREATE TABLE id_mng.id_pair
(
  project_guid character varying NOT NULL,
  id_p character varying NOT NULL,
  type_p integer NOT NULL,
  segment integer[],
  ...
  CONSTRAINT id_pair_pkey PRIMARY KEY (project_guid, id_p, type_p)
)

That inner select return at about  3 sec, but the UPDATE as a whole runs for
minutes (I stopped it after 10mins). On my other system with postgres 9.5.10
with identical data, it finishes after 15sec.

Please advise

Thank you 

Jaroslav Urik

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

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