I am sorry for the delay, but I am happy to tell you, that it seems to be isolated problem in my (possibly) corrupted table. I have duplicated mentioned table (create tab_new as select * from tab_old) and it works just fine (the update took < 10 Sec).
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).
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?