From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: SZUCS Gábor <surrano@mailbox.hu>
Cc: pgsql-performance@postgresql.org
Subject: Re: *very* inefficient choice made by the planner (regarding
Date: Fri, 18 Jun 2004 08:32:00 -0700 (PDT)
Message-ID: <20040618082006.N86299@megazone.bigpanda.com> (raw)
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>
On Fri, 18 Jun 2004, [iso-8859-1] SZUCS Gábor wrote:
> Dear Gurus,
>
> ----- Original Message -----
> From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
> 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...
Well, once you take into account the lhs being potentially null
lhe in (select rhe from foo) is something like:
case when lhe is null then
not exists(select 1 from foo limit 1) or null
else
(exists(select 1 from foo where rhe is null) and null)
or exists(select 1 from foo where rhe=lhe)
end
I think the real win occurs for where clause cases if it can pull up the
exists that references lhe so that it doesn't try to evaluate it on every
row and that's unlikely to occur in something like the above.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sszabo@megazone.bigpanda.com, surrano@mailbox.hu
Subject: Re: *very* inefficient choice made by the planner (regarding
In-Reply-To: <20040618082006.N86299@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox