Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 826BB9FB878; Fri, 23 Mar 2007 19:30:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 76031-03; Fri, 23 Mar 2007 19:30:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id A30C89FBB2B; Fri, 23 Mar 2007 19:30:36 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Fri, 23 Mar 2007 17:30:35 -0500 Message-Id: <46040EB2.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Fri, 23 Mar 2007 17:30:27 -0500 From: "Kevin Grittner" To: "Tom Lane" , "Martijn van Oosterhout" Cc: ,, "Andrea Olson" , "Bill Severson" , "John Hutchins" , "Randy Peterson" , "Shannon Spranger" Subject: Re: EXISTS optimization References: <4603C1A3.EE98.0025.0@wicourts.gov> <25339.1174686582@sss.pgh.pa.us> <20070323222641.GD9623@svana.org> In-Reply-To: <20070323222641.GD9623@svana.org> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/1428 X-Sequence-Number: 100912 I don't understand -- TRUE OR UNKNOWN evaluates to TRUE, so why would the = IN need to continue? I'm not quite following the rest; could you = elaborate or give an example? (Sorry if I'm lagging behind the rest of = the class here.) =20 -Kevin =20 =20 >>> Martijn van Oosterhout 03/23/07 5:26 PM >>>=20 On Fri, Mar 23, 2007 at 05:49:42PM -0400, Tom Lane wrote: > We don't currently try to flatten EXISTS into a unique/join plan as we > do for IN. I seem to recall not doing so when I rewrote IN planning > because I didn't think it would be exactly semantically equivalent, > but that was awhile ago. Right at the moment it seems like it ought > to be equivalent as long as the comparison operators are strict. Wasn't it due to the fact that IN needs to scan through all possibilites anyway because of its interaction with NULL, whereas EXISTS can stop at the first row? That would mean the subquery to be materialised would not be equivalent if it called any non-immutable functions. It's also much less clear to be a win in the EXISTs case. But then, that's a costs issue the planner can deal with... Have a nice day, --=20 Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to = litigate.