very slow delete

From: Jakub Ouhrabka <jouh8664(at)ss1000(dot)ms(dot)mff(dot)cuni(dot)cz>
To: <pgsql-general(at)postgresql(dot)org>
Subject: very slow delete
Date: 2001-09-02 11:02:49
Message-ID: Pine.LNX.4.33.0109021116300.4090-100000@u-pl0
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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_);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2001-09-02 11:20:09 Effect of Filesystem
Previous Message Lincoln Yeoh 2001-09-02 08:07:07 Re: [WAY OT] Re: PL/java?