PostgreSQL 7.4 on i386-linux-gnu, compiled by GCC 2.96 -- about 10 records CREATE TABLE my.Small ( id my.dint NOT NULL, code my.dvalue NOT NULL, CONSTRAINT pk_1 PRIMARY KEY (id), ) WITHOUT OIDS;
-- about 80M rows CREATE TABLE my.Large ( id my.dlong NOT NULL, small_id my.dint NOT NULL, value my.value, CONSTRAINT pk_2 PRIMARY KEY (id), CONSTRAINT fk_2 FOREIGN KEY (small_id) REFERENCES my.small (id) ON UPDATE RESTRICT ON DELETE RESTRICT, ) WITHOUT OIDS; CREATE INDEX small_fk ON my.Large USING btree (small_id); --------------------------------------------- The fowllowing queiries run in less than 40 ms. 1) select 1 from Large where small_id = 239 2) SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 239 FOR UPDATE OF x 3) delete from Small where id = 239 Feb 11 18:07:36 srv07 postgres[2091]: [91-1] LOG: statement: SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = $1 FOR UPDATE OF x Feb 11 18:10:41 srv07 postgres[2091]: [92-1] LOG: duration: 185273.262 ms When I try to delete record, it takes > 3 min. Why is it taking so long if practically the same select query (see (2)) is running very quickly. Anything that can be done to fix it? Thanks __________________________________ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
