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 B647CD1B745 for ; Fri, 18 Jun 2004 05:37:35 -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 17248-07 for ; Fri, 18 Jun 2004 08:37:34 +0000 (GMT) Received: from damo.axelero.hu (fe01.axelero.hu [195.228.240.89]) by svr1.postgresql.org (Postfix) with ESMTP id 1EFCED1B37D for ; Fri, 18 Jun 2004 05:37:30 -0300 (ADT) Received: from damo (localhost-02 [127.0.2.1]) by damo.axelero.hu (8.12.11/8.12.11) with SMTP id i5I8bTOK013651 for ; Fri, 18 Jun 2004 10:37:30 +0200 (CEST) Received: from fe01.axelero.hu [127.0.2.1] via SMTP gateway by damo [195.228.240.89]; id A0352FEEE4B at Fri, 18 Jun 2004 10:37:29 +0200 Received: from fejleszt4 (121-248-182-81.adsl-fixip.axelero.hu [81.182.248.121]) by fe01.axelero.hu (8.12.11/8.12.11) with SMTP id i5I8bITt013244 for ; Fri, 18 Jun 2004 10:37:22 +0200 (CEST) Message-ID: <008501c4550f$87523a80$0403a8c0@fejleszt4> From: =?iso-8859-1?Q?SZUCS_G=E1bor?= To: 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> Subject: Re: *very* inefficient choice made by the planner (regarding Date: Fri, 18 Jun 2004 10:37:40 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 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/178 X-Sequence-Number: 7236 Dear Gurus, ----- Original Message ----- From: "Stephan Szabo" Sent: Thursday, June 10, 2004 7:14 PM > > On Thu, 10 Jun 2004, Stephan Szabo wrote: > > > > > On Thu, 10 Jun 2004, Jean-Luc Lachance wrote: > > > > > I agree, but it should be a simple rewrite. No? > > > > 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) AFAIK this returns * NULL if (NULL in foo.a) and (1 not in foo.a) * (1 in foo.a) otherwise. The weakness is the doubled exists clause. I'm sure it makes most cases at least doubtful... G. %----------------------- cut here -----------------------% \end