From: | Vik Fearing <vik(dot)fearing(at)dalibo(dot)com> |
---|---|
To: | David Rysdam <drysdam(at)ll(dot)mit(dot)edu>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: nested query vs left join: query planner very confused |
Date: | 2013-11-27 16:15:18 |
Message-ID: | 52961A96.40101@dalibo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/27/2013 04:56 PM, David Rysdam wrote:
> I've got two tables, sigs and mags. It's a one-to-one relationship, mags
> is just split out because we store a big, less-often-used field
> there. "signum" is the key field.
>
> Sometimes I want to know if I have any orphans in mags, so I do a query
> like this:
>
> select signum from lp.Mags where signum is not null and signum not
> in (select lp.Sigs.signum from lp.Sigs)
[...]
> I also decided to try doing the query a different way:
>
> select lp.mags.signum from lp.mags left join lp.sigs on
> lp.mags.signum = lp.sigs.signum where lp.mags.signum is not null
> and lp.sigs.signum is null;
>
> This one runs fast for both of us. So I guess my second question is: why
> can't the query planner tell these are the same query?
Because they're not the same query. NOT IN has a bunch of semantics
issues regarding nulls which the anti-join in the second query does not
have.
--
Vik
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-11-27 16:21:09 | Re: nested query vs left join: query planner very confused |
Previous Message | bricklen | 2013-11-27 16:06:51 | Re: nested query vs left join: query planner very confused |