Message-ID: <20040212214923.98051.qmail@web60803.mail.yahoo.com>
Date: Thu, 12 Feb 2004 13:49:23 -0800 (PST)
From: ow <oneway_111@yahoo.com>
Subject: Re: 7.4 - FK constraint performance 
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <4559.1076601260@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ow <oneway_111@yahoo.com> writes:
> > When I try to delete record, it takes > 3 min.
> 
> I think it must be using a seqscan for the foreign key check query.
> Could you try this and show the results? 

1) SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = 201 FOR UPDATE OF x;

QUERY PLAN
Index Scan using small_fk on large x  (cost=0.00..6.01 rows=1 width=6) (actual
time=0.251..0.251 rows=0 loops=1)
  Index Cond: ((small_id)::integer = 201)
Total runtime: 0.338 ms

2) prepare foo(my.dint) as
SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = $1 FOR UPDATE OF x;

explain analyze execute foo(201);

QUERY PLAN
Seq Scan on large x  (cost=0.00..1787052.30 rows=7893843 width=6) (actual
time=210566.301..210566.301 rows=0 loops=1)
  Filter: ((small_id)::integer = ($1)::integer)
Total runtime: 210566.411 ms

Thanks


__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html