Date: Wed, 11 Feb 2004 17:06:36 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: ow <oneway_111@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: 7.4 - FK constraint performance
In-Reply-To: <20040212000610.13913.qmail@web60806.mail.yahoo.com>
Message-ID: <20040211170317.M67403@megazone.bigpanda.com>
References: <20040212000610.13913.qmail@web60806.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 11 Feb 2004, ow wrote:

> 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?

Hmm, I'd wonder if maybe it's choosing a sequential scan in the second
case?  As a random question, does increasing the statistics target on
Large.small_id and re-analyzing change its behavior?