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:
Show quoted text
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 offtruncate 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 offFor 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:
Show quoted text
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 offtruncate 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 offFor 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