X-Original-To: pgsql-hackers@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 33A3C474E4F for ; Wed, 7 May 2003 09:36:43 -0400 (EDT) Received: from flake.decibel.org (unknown [66.143.173.58]) by postgresql.org (Postfix) with SMTP id 97F8D475D0F for ; Wed, 7 May 2003 09:36:13 -0400 (EDT) Received: (qmail 42421 invoked by uid 1001); 7 May 2003 13:36:17 -0000 Date: Wed, 7 May 2003 08:36:16 -0500 From: "Jim C. Nasby" To: Tom Lane Cc: Josh Berkus , pgsql-performance , pgsql-hackers@postgresql.org Subject: Re: [PERFORM] Hypothetical suggestions for planner, indexing improvement Message-ID: <20030507083616.F66185@flake.decibel.org> Reply-To: jim@nasby.net References: <200305051219.58955.josh@agliodbs.com> <22292.1052195133@sss.pgh.pa.us> <200305052133.33680.josh@agliodbs.com> <20030506080747.C66185@flake.decibel.org> <24779.1052228707@sss.pgh.pa.us> <20030507000801.E66185@flake.decibel.org> <25000.1052284836@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5.1i In-Reply-To: <25000.1052284836@sss.pgh.pa.us>; from tgl@sss.pgh.pa.us on Wed, May 07, 2003 at 01:20:36AM -0400 X-Operating-System: FreeBSD 4.8-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net X-Spam-Status: No, hits=-32.9 required=5.0 tests=BAYES_01,IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES, REPLY_WITH_QUOTES,SIGNATURE_SHORT_SPARSE,USER_AGENT_MUTT version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/144 X-Sequence-Number: 38651 > Supposing that tab1.col1 contains 1, NULL, 2, then for an outer > table row where col2 = 42 > > WHERE outer.col2 IN (SELECT col1 FROM tab1) > > will yield NULL (not FALSE). But > > WHERE EXISTS(SELECT * FROM tab1 WHERE col1 = outer.col2) > > will yield FALSE (not NULL). > > The distinction doesn't matter at the top level of WHERE, but it > matters a lot underneath a NOT ... OK, but even if a true transform can't be done, couldn't they share the same set of code to fetch the data for the subquery? Going back to my original post, I tend to use IN only in cases where I think the subquery will return a small result-set, and use EXISTS elsewhere. Presumably, the subquery for an IN will only be run once, while EXISTS will be run as an inner-loop (I'm guessing here, I could be wrong). It might be useful if the subquery was executed based on how many rows it would/might return. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"