Message-ID: <20040212000610.13913.qmail@web60806.mail.yahoo.com>
Date: Wed, 11 Feb 2004 16:06:10 -0800 (PST)
From: ow <oneway_111@yahoo.com>
Subject: 7.4 - FK constraint performance
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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