On Wed, Mar 1, 2017 at 2:12 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> 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.
