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_);
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? |