Date: Thu, 12 Feb 2004 07:37:32 -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: <20040212140516.65859.qmail@web60807.mail.yahoo.com>
Message-ID: <20040212072447.H86595@megazone.bigpanda.com>
References: <20040212140516.65859.qmail@web60807.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 12 Feb 2004, ow wrote:

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

You also did the alter table to up the statistics target on the column,
right?

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

It doesn't plan it as id=239 but as id=$1 and then executes it with
$1=239.  The plan data gets reused for other id values if it needs the
same fk action again later in the session.

I'd hoped that upping the statistics target and re-analyzing would make it
choose an index scan for the case where it doesn't know what constant is
going to be used.  Hmm, what is the estimated cost difference and real
time difference on id=1 between seqscan and index scan (explain analyze
output with and without enable_seqscan=off should show you).