very slow delete - Mailing list pgsql-general

From Jakub Ouhrabka
Subject very slow delete
Date
Msg-id Pine.LNX.4.33.0109021116300.4090-100000@u-pl0
Whole thread Raw
Responses Re: very slow delete
List pgsql-general
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_);


pgsql-general by date:

Previous
From: Randall Perry
Date:
Subject: Perl won't eval PgSQL boolean value
Next
From: Lincoln Yeoh
Date:
Subject: Re: [WAY OT] Re: PL/java?