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

Stefan Andreatta <s.andreatta@synedra.com> writes:
> The same anti-join using the text fields, however estimates just 1
> resulting row, while there are still of course 9,999 of them:

> =# explain 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;

That is not an anti-join.  To make it one, you have to constrain the RHS
join column to be IS NULL, not some random other column.  Note the join
type isn't getting shown as Anti:

>   Hash Left Join  (cost=1.02..192.53 rows=1 width=4) (actual time=0.020..3.091 rows=9999 loops=1)

As written, the query could return some rows that weren't actually
antijoin rows, ie tmp_san_1.text *did* have a match in tmp_san_2,
but that row chanced to have a null value of id.

Possibly the planner could be smarter about estimating for this case,
but it doesn't look much like a typical use-case to me.

            regards, tom lane