Re: BUG #16967: Extremely slow update statement in trigger - Mailing list pgsql-bugs

Hi all,

thanks for looking into this and sorry about the dropbox link. I will inline all further examples.

I tried running "analyze demo" after the bulk load but it does not have the desired effect. It only works if the analyze call is executed between the two update statements. Is it possible that the speedup in this case is a side effect and not actually the result of the analyze call?

Here is what I tried: I reduced the number of bulk inserted records to 10k as this still results in a significant slowdown of the update statement, but the calls complete within 40s on my machine.

Slow run: Execute "analyze demo" after the insert and before the update calls:
insert into demo (id, type) select i, 'blue' from generate_series(1, 10000) s(i);
analyze demo;
update demo set type = 'black' where type='red'; -- No rows are updated and the query executes in 2ms.
update demo set type = 'black' where type='blue'; -- Execution time: 37 seconds

Fast run: Execute "analyze demo" after the first update statement:
insert into demo (id, type) select i, 'blue' from generate_series(1, 10000) s(i);
update demo set type = 'black' where type='red'; -- No rows are updated and the query executes in 2ms.
analyze demo;
update demo set type = 'black' where type='blue'; -- Execution time: 51ms

Also slow run: Execute "analyze demo" after the bulk load and between the update statements:
insert into demo (id, type) select i, 'blue' from generate_series(1, 10000) s(i);
analyze demo;
update demo set type = 'black' where type='red'; -- No rows are updated and the query executes in 2ms.
analyze demo;
update demo set type = 'black' where type='blue'; -- Execution time: ~37 seconds

Execution times are basically the same, whether I run all statements in one go or if I manually execute them one by one. I even tried waiting for a while after the "analyze demo" calls, in case there were still some background processes running but this also had no effect on the execution times.

Thanks again and best regards
Nicolas