Обсуждение: 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
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
Hi,
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).
Please close this bug report.
Thank you for your time
Jaroslav
On Thu, Dec 7, 2017 at 2:36 PM Jaroslav Urik <jarda.urik@gmail.com> wrote:
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 UrikOn 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