Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id LAA00552 for ; Mon, 19 Jul 1999 11:15:49 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id LAA27242; Mon, 19 Jul 1999 11:14:56 -0400 (EDT) To: secret cc: PG-SQL , John Ridout Subject: Re: [SQL] Good Optimization In-reply-to: Your message of Mon, 19 Jul 1999 10:02:57 -0400 <37933011.CB0F3E76@kearneydev.com> Date: Mon, 19 Jul 1999 11:14:55 -0400 Message-ID: <27240.932397295@sss.pgh.pa.us> From: Tom Lane secret writes: > Tom Lane wrote: >> How much *actual* speedup is there? I don't trust the optimizer's >> numbers as anything more than relative measures ;-) >> >> I'm a bit surprised that you are getting a nested-loop plan and not >> a merge or hash join. With a merge join, at least, there ought not be >> a large difference from providing the additional qual clause (I think). >> What Postgres version are you using? > The actual performance difference is HUGE. Hours vs minutes or Minutes vs > Seconds... Well, yeah, it could be huge in a nested-loop scenario; in a mergejoin I think it would make little difference. Actually, if the inner path is indexed then this shouldn't make any difference for a nestloop either; each probe into the inner path *ought* to be using the value of the current outer tuple's join variable as an indexqual constraint, which would have the same limiting effect as the explicit restriction you propose adding. There is code in the optimizer that claims to be making that happen. Sounds like it is broken :-( What are the data types of po_id and material_po in your example? And, again, which Postgres version exactly? regards, tom lane