Re: Why is this query running slowly?

On 15 Září 2011, 16:26, Tom Lane wrote:
> Yang Zhang <yanghatespam@gmail.com> writes:
>> On Thu, Sep 15, 2011 at 1:22 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
>>> [ use a left join instead of NOT IN ]
>
>> This worked great, thank you.  Too bad the planner isn't smart enough
>> to do this yet!
>
> It never will be, because they're not equivalent queries.  NOT IN has
> different (and very hard-to-optimize) behavior in the face of NULLs in
> the right-hand table.

True, but in this case the subquery selects a PK column (at least I
believe so, we haven't seen the structure), so there should be no NULLs.
So the result should be the same.

Could the planner do this reasoning, i.e. see if there's a NOT NULL
constraint on the column and change the plan accordingly?

Tomas