Re: very slow delete

Jakub Ouhrabka <jouh8664(at)ss1000(dot)ms(dot)mff(dot)cuni(dot)cz> writes:
> i'm trying to tune some batches and after some research i located the
> biggest problem in doing something like this:

> begin;
> update ts08 set ts08typ__ = 1111;
> delete from ts08;
> end;

> the update takes about 1m25s (there are aprox. 70000 rows in ts08). but
> the delete then takes more than 20 minutes (i canceled the query...).

I believe the issue here is that the UPDATE leaves a list of 70000
pending trigger events, which would normally be executed at the end of
the transaction. But the DELETE has to search through the list
(linearly) to find the entry for the row being deleted. So the total
time for the DELETE goes up as O(N^2). Even though the constant factor
for this term is very small (just a comparison) it still dominates the
runtime once you get enough rows involved.

This datastructure should be improved, but don't hold your breath
waiting. Do you really need to do both steps in one transaction?
Can you reduce the number of rows processed per transaction?

regards, tom lane