Date: Mon, 29 Sep 2003 17:29:38 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	Bruce Momjian <pgman@candle.pha.pa.us>,
	Peter Childs <Blue.Dragon@blueyonder.co.uk>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta) 
In-Reply-To: <13527.1064880246@sss.pgh.pa.us>
Message-ID: <20030929170825.S1939@megazone.bigpanda.com>
References: <20030815073837.R19338-100000@megazone.bigpanda.com>
	<1718.1064705858@sss.pgh.pa.us>
	<20030928111850.B73144@megazone.bigpanda.com>
	<10377.1064775363@sss.pgh.pa.us>
	<20030929112706.U94710@megazone.bigpanda.com>
	<6950.1064868208@sss.pgh.pa.us>
	<20030929155053.G371@megazone.bigpanda.com>
	<13527.1064880246@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Mon, 29 Sep 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> >> Hm.  Don't suppose you were using EXPLAIN ANALYZE so we could see what's
> >> happening?  This is clearly a planner failure, although I'm unsure if we
> >> can expect the planner to get the right answer with no pg_statistic entries.
>
> > The left join one seems to give me values like the following:
>
> There are some fishy row estimates in here:
>
> >    ->  Index Scan using pktest_a_key on pktest  (cost=0.00..52.00
> > rows=1000 width=8) (actual time=17.82..1609.97 rows=10000 loops=1)
>
> The system definitely should be expected to have the accurate row count
> for the PK table, since an index should have been created on it (and we
> do do that after loading the data, no?).  It is possible that it'd have
> the default 1000 estimate for the FK table, if there are no indexes at
> all on the FK table; otherwise it should have the right number.  It's
> not real clear to me what conditions you're testing under, but the
> estimates in the plans you're quoting aren't consistent ...

Well, they're all from the same load of the same data with only stopping
and starting in between, but I did make the index on the pk table first
loaded the data and then built the fk table index ( because I'd wanted to
try without the index as well), which meant that it wouldn't match the
behavior of a dump. Ugh, I'd forgotten that the primary key didn't get
created until later too.

Okay, that's much better:
 Hash Left Join  (cost=203.00..1487869.29 rows=49501250 width=4) (actual
time=611632.67..611632.67 rows=0 loops=1)
   Hash Cond: (("outer".b = "inner".a) AND ("outer".c = "inner".b))
   Filter: ("inner".a IS NULL)
   ->  Seq Scan on fktest  (cost=0.00..745099.00 rows=49501250 width=8)
(actual time=0.01..169642.48 rows=50000000 loops=1)
         Filter: ((b IS NOT NULL) AND (c IS NOT NULL))
   ->  Hash  (cost=152.00..152.00 rows=10000 width=8) (actual
time=46.04..46.04 rows=0 loops=1)
         ->  Seq Scan on pktest  (cost=0.00..152.00 rows=10000 width=8)
(actual time=0.02..21.38 rows=10000 loops=1)
 Total runtime: 611632.95 msec
(8 rows)

That's much better. :)  As long as the row estimates are reasonable it
seems to be okay, but I do wonder why it chose the merge join for the case
when it thought there was only 1000 rows though.