Stefan Andreatta <s.andreatta@xxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
