X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BF20AD1B170 for ; Fri, 18 Jun 2004 11:29:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59567-05 for ; Fri, 18 Jun 2004 14:29:34 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id ED828D1B174 for ; Fri, 18 Jun 2004 11:29:29 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5IETPQ7020173; Fri, 18 Jun 2004 10:29:26 -0400 (EDT) To: =?iso-8859-1?Q?SZUCS_G=E1bor?= Cc: pgsql-performance@postgresql.org Subject: Re: *very* inefficient choice made by the planner (regarding In-reply-to: <008501c4550f$87523a80$0403a8c0@fejleszt4> References: <200406101624.21739.ftm.van.vugt@foxi.nl> <40C881FE.5000301@sympatico.ca> <25304.1086883145@sss.pgh.pa.us> <40C892BA.8080704@sympatico.ca> <20040610100332.D20710@megazone.bigpanda.com> <20040610101410.X21002@megazone.bigpanda.com> <008501c4550f$87523a80$0403a8c0@fejleszt4> Comments: In-reply-to =?iso-8859-1?Q?SZUCS_G=E1bor?= message dated "Fri, 18 Jun 2004 10:37:40 +0200" Date: Fri, 18 Jun 2004 10:29:25 -0400 Message-ID: <20172.1087568965@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/191 X-Sequence-Number: 7249 =?iso-8859-1?Q?SZUCS_G=E1bor?= writes: >> It's NULLs inside the subselect that are the issue. > > select 1 in (select a from foo) > select exists ( select 1 from foo where a=1) > Just a dumb try :) > SELECT (exists(select 1 from foo where a isnull) AND NULL) > OR exists(select 1 from foo where a=1) The more general case is where you have a variable (or expression) on the left of IN. That could be NULL too, and this still doesn't give the right result in that case :-(. With NULL on the left the correct answer would be FALSE if the subselect has zero rows, NULL otherwise. regards, tom lane