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

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.

Cheers,

Jeff