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