From: | ow <oneway_111(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | 7.4 - FK constraint performance |
Date: | 2004-02-12 00:06:10 |
Message-ID: | 20040212000610.13913.qmail@web60806.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Cott Lang | 2004-02-12 00:28:30 | Re: [BUGS] Bug in pg_autovacuum ? |
Previous Message | Gavin Sherry | 2004-02-11 23:29:20 | Re: SET WITHOUT OIDS and VACUUM badness? |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2004-02-12 01:06:36 | Re: 7.4 - FK constraint performance |
Previous Message | Greg Patnude | 2004-02-11 23:43:37 | Re: determining how many products are in how many categories |