Thread: very slow delete

hi,
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...).

when i try:

begin
delete from ts08;
end;

it takes about 1s. the database was vacuum analyzed before running both
queries. dropping the indexes also doesn't help (i dropped last 4 indexes
- see table schema below) - it speeds up only the update, delete is still
very slow (again more than 20 minutes). but when i try it on the same
dataset but without any constraints (CREATE TABLE temp AS SELECT * FROM
ts08) everything works fine (1 sec or so). so i think this strange
behavior occures when there is some foreign key constraint.

why is there so big difference between delete and update then delete (1s
to 'infinity')? how can i drop all constraints for some table before
delete and then recreate them (e.g. using system catalogs because there is
no alter table drop constraint...)?

thanks in advance,
kuba


more info:

there are no foreigns keys in other tables using ts08pk___ or any other
column from ts08. there are no triggers, no rules. i'm using 7.1.3 on
debian installed from .deb.  shared_buffers = 8192.

table schema:

CREATE TABLE TS08 (
TS08PK___ SERIAL PRIMARY KEY,
TS08IDAP_ INTEGER NOT NULL,
TS08IDSMS INTEGER NOT NULL UNIQUE,
TS08IDFLO INTEGER NOT NULL,
TS08IDTEX INTEGER NOT NULL,
TS08MOBIL VARCHAR(20) NOT NULL,
TS08CASTK FLOAT,
TS08DATUM TIMESTAMP NOT NULL,
TS08STAV_ INTEGER NOT NULL,
TS08TYP__ INTEGER NOT NULL,

FOREIGN KEY (TS08IDFLO) REFERENCES TS06(TS06IDFLO),
FOREIGN KEY (TS08IDTEX) REFERENCES TS11(TS11IDTEX)
);

CREATE INDEX TS08_TS08IDFLO_IDX ON TS08 USING BTREE (TS08IDFLO);
CREATE INDEX TS08_TS08IDTEX_IDX ON TS08 USING BTREE (TS08IDTEX);
CREATE INDEX TS08_TS08STAV__IDX ON TS08 USING BTREE (TS08STAV_);
CREATE INDEX TS08_TS08IDAP__IDX ON TS08 USING BTREE (TS08IDAP_);

Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.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