Re: anti-join with small table via text/varchar cannot estimate rows correctly

On Wed, Mar 1, 2017 at 5:24 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, Mar 1, 2017 at 2:12 PM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Wed, Mar 1, 2017 at 3:00 PM, Stefan Andreatta <s(dot)andreatta(at)synedra(dot)com
>> > wrote:
>>
>>> plain analyze
>>> select tmp_san_1.id
>>> from tmp_san_1
>>> left join tmp_san_2 on tmp_san_1.text = tmp_san_2.text
>>> where tmp_san_2.id is null;
>>>
>>> ​Does it help if you check for "tmp_san_2.text is null"?
>>
>>
>>
> Yes. And if you swap it so that the left join is on the integer while IS
> NULL is on the text, that also gets poorly estimated. Also, if you make
> both column of both tables be integers, same thing--you get bad estimates
> when the join condition refers to one column and the where refers to the
> other. I don't know why the estimate is poor, but it is not related to the
> types of the columns, but rather the identities of them.
>
>
​I suspect it has to with the lack of a NOT NULL constraint on either
column causing the planner to disregard the potential to implement a LEFT
JOIN using ANTI-JOIN semantics - or, also possible - the form itself is
invalid regardless of the presence or absence of contraints. IIUC, while a
true anti-join syntax doesn't exist the canonical form for one uses NOT
EXISTS - which would force the author to use only the correct column pair.

David J.
​