very slow delete
От | Jakub Ouhrabka |
---|---|
Тема | very slow delete |
Дата | |
Msg-id | Pine.LNX.4.33.0109021116300.4090-100000@u-pl0 обсуждение исходный текст |
Ответы |
Re: very slow delete
|
Список | 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 по дате отправления: