From: ow <oneway_111@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: 7.4 - FK constraint performance
Date: Thu, 12 Feb 2004 13:49:23 -0800 (PST)
Message-ID: <20040212214923.98051.qmail@web60803.mail.yahoo.com> (raw)
In-Reply-To: <4559.1076601260@sss.pgh.pa.us>
--- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oneway_111@yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: 7.4 - FK constraint performance
In-Reply-To: <20040212214923.98051.qmail@web60803.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox