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

From: jarda(dot)urik(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Cc: jarda(dot)urik(at)gmail(dot)com
Subject: BUG #14949: array_append() - performance issues (in update)
Date: 2017-12-06 10:50:00
Message-ID: 20171206105000.21691.37546@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14949
Logged by: Jaroslav Urik
Email address: jarda(dot)urik(at)gmail(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tomas Vondra 2017-12-06 13:33:36 Re: BUG #14932: SELECT DISTINCT val FROM table gets stuck in an infinite loop
Previous Message Sandeep Thakkar 2017-12-06 06:51:42 Re: BUG #14947: Installation Errors