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

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Wed, Mar 1, 2017 at 2:12 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Wed, Mar 1, 2017 at 3:00 PM, Stefan Andreatta <s.andreatta@xxxxxxxxxxx> 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