X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2842BD1B50A for ; Mon, 29 Sep 2003 22:55:31 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 90759-07 for ; Mon, 29 Sep 2003 19:55:05 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 6878CD1B4FF for ; Mon, 29 Sep 2003 19:55:02 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id E0E0E35380; Mon, 29 Sep 2003 15:55:04 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id DF23535373; Mon, 29 Sep 2003 15:55:04 -0700 (PDT) Date: Mon, 29 Sep 2003 15:55:04 -0700 (PDT) From: Stephan Szabo To: Tom Lane Cc: Christopher Kings-Lynne , Bruce Momjian , Peter Childs , PostgreSQL-development Subject: Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta) In-Reply-To: <6950.1064868208@sss.pgh.pa.us> Message-ID: <20030929155053.G371@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> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/1623 X-Sequence-Number: 44635 On Mon, 29 Sep 2003, Tom Lane wrote: > Stephan Szabo writes: > > As an update, so far I still am getting better results with NOT EXISTS > > than the left join. > > 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: At sort_mem=4096 Merge Right Join (cost=9966071.76..10349763.45 rows=49501250 width=4) (actual time=4383435.48..4383435.48 rows=0 loops=1) Merge Cond: (("outer".a = "inner".b) AND ("outer".b = "inner".c)) Filter: ("outer".a IS NULL) -> 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) -> Sort (cost=9966071.76..10089824.88 rows=49501250 width=8) (actual time=3876614.87..4157850.82 rows=50000000 loops=1) Sort Key: fktest.b, fktest.c -> Seq Scan on fktest (cost=0.00..745099.00 rows=49501250 width=8) (actual time=4.09..273798.65 rows=50000000 loops=1) Filter: ((b IS NOT NULL) AND (c IS NOT NULL)) Total runtime: 4384366.79 msec (9 rows) At sort_mem=128000 Merge Right Join (cost=69.32..134.00 rows=991 width=4) (actual time=2183787.83..2183787.83 rows=0 loops=1) Merge Cond: (("outer".a = "inner".b) AND ("outer".b = "inner".c)) Filter: ("outer".a IS NULL) -> Index Scan using pktest_a_key on pktest (cost=0.00..52.00 rows=1000 width=8) (actual time=13.11..390.40 rows=10000 loops=1) -> Sort (cost=69.32..71.79 rows=991 width=8) (actual time=1944240.67..2048954.65 rows=50000000 loops=1) Sort Key: fktest.b, fktest.c -> Seq Scan on fktest (cost=0.00..20.00 rows=991 width=8) (actual time=2.61..225967.79 rows=50000000 loops=1) Filter: ((b IS NOT NULL) AND (c IS NOT NULL)) Total runtime: 2184348.78 msec I haven't finished a run with it doing an index scan on fktestyet, still... The not exists gives me: Seq Scan on fktest (cost=0.00..242021289.48 rows=24750625 width=8) (actual time=2032607.68..2032607.68 rows=0 loops=1) Filter: ((b IS NOT NULL) AND (c IS NOT NULL) AND (NOT (subplan))) SubPlan -> Index Scan using pktest_a_key on pktest (cost=0.00..4.83 rows=1 width=0) (actual time=0.03..0.03 rows=1 loops=50000000) Index Cond: ((a = $0) AND (b = $1)) Total runtime: 2032607.87 msec (6 rows) But this time was one of the higher times for this query. I'd seen times down at about 1400000 msec yesterday.