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

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(dot)urik(at)gmail(dot)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 Urik
>
> On Wed, Dec 6, 2017, 17:40 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> jarda(dot)urik(at)gmail(dot)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
>>
>