From: ow <oneway_111@yahoo.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: 7.4 - FK constraint performance
Date: Thu, 12 Feb 2004 06:05:16 -0800 (PST)
Message-ID: <20040212140516.65859.qmail@web60807.mail.yahoo.com> (raw)
In-Reply-To: <20040211170317.M67403@megazone.bigpanda.com>
--- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> 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?
Ran analyze, the result is the same. Here's more info:
1) There's 1 row in "Large" for "small_id" = 239
SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 239 FOR UPDATE OF x
Quick query. Explain shows index scan.
2) There are many rows in "Large" for "small_id" = 1
SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 1 FOR UPDATE OF x
Runs for about 3 min. Explain shows table scan.
3) delete from Small where id = 239
Runs for about 3 min. It does appear that table scan is used for FK
verification. But why? Am deleting "id = 239" not "id = 1" and the query in
(1) runs very quickly. Had suspicion that wrong id is passed during FK
verification but FK constraint DOES work.
4) Domain types used in the example above
my.dint = int
my.dlong = int8
my.dvalue = varchar(15)
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, sszabo@megazone.bigpanda.com
Subject: Re: 7.4 - FK constraint performance
In-Reply-To: <20040212140516.65859.qmail@web60807.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