Re: [PERFORM] anti-join with small table via text/varchar cannotestimate rows correctly

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

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