Re: 7.4 - FK constraint performance

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