Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id AC725BDAF14 for ; Wed, 18 Jul 2012 19:36:31 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Srcr7-00088e-8k for pgsql-performance@postgresql.org; Wed, 18 Jul 2012 22:36:30 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q6IMaEB3003692; Wed, 18 Jul 2012 18:36:14 -0400 (EDT) From: Tom Lane To: Nick Hofstede cc: "pgsql-performance@postgresql.org" Subject: Re: optimizing queries using IN and EXISTS In-reply-to: References: Comments: In-reply-to Nick Hofstede message dated "Wed, 18 Jul 2012 16:10:30 -0000" Date: Wed, 18 Jul 2012 18:36:14 -0400 Message-ID: <3691.1342650974@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/229 X-Sequence-Number: 47410 Nick Hofstede writes: > I'm surprised at the difference in speed/execution plan between two logically equivalent queries, one using IN, the other using EXISTS. (At least I think they are logically equivalent) > SELECT * > FROM foo > WHERE 'text6' IN (SELECT value > FROM bar > JOIN foo AS foo2 > ON bar.foo_ref = foo2.id > WHERE foo2.id = foo.id) Hm. convert_ANY_sublink_to_join() rejects subqueries that contain any Vars of the parent query level, so the reference to foo.id prevents this from being converted to a semijoin. However, it seems like that's overly restrictive. I'm not sure that we could remove the test altogether, but at least outer vars used in WHERE seem safe. In the meantime, you can recast like this: SELECT * FROM foo WHERE ('text6', id) IN (SELECT value, foo2.id FROM bar JOIN foo AS foo2 ON bar.foo_ref = foo2.id) and still get a semijoin plan from an IN-style query. regards, tom lane