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

[Stefan Andreatta <s.andreatta@xxxxxxxxxxx>]

Hello,

I have encountered a strange problem when doing an anti-join with a very small table via a varchar or text field as opposed to an integer field. Postgres version is 9.5.3

I did some experiments to extract the problem in a simple form. FIrst generate two tables with a series of numbers - once as integers once as text. The first table has 10,000 rows the second table just one:

=# select generate_series(1, 10000) as id, generate_series(1,10000)::text as text into table tmp_san_1;
SELECT 10000
=# select generate_series(1, 1) as id, generate_series(1,1)::text as text into table tmp_san_2;
SELECT 1

=# analyze tmp_san_1;
ANALYZE
=# analyze tmp_san_2;
ANALYZE

=# \d tmp_san_*
   Table "public.tmp_san_1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 text   | text    | 

   Table "public.tmp_san_2"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 text   | text    | 

Now I do an anti-join between the two tables via the id field (integer). The number of resulting rows are estimated correctly as 9,999:

=# explain analyze 
     select tmp_san_1.id 
     from tmp_san_1 
       left join tmp_san_2 on tmp_san_1.id = tmp_san_2.id 
     where tmp_san_2.id is null;
     
                                                   QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Hash Anti Join  (cost=1.02..281.26 rows=9999 width=4) (actual time=0.019..2.743 rows=9999 loops=1)
   Hash Cond: (tmp_san_1.id = tmp_san_2.id)
   ->  Seq Scan on tmp_san_1  (cost=0.00..154.00 rows=10000 width=4) (actual time=0.007..1.023 rows=10000 loops=1)
   ->  Hash  (cost=1.01..1.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on tmp_san_2  (cost=0.00..1.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.138 ms
 Execution time: 3.218 ms
(8 rows)

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;

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=1.02..192.53 rows=1 width=4) (actual time=0.020..3.091 rows=9999 loops=1)
   Hash Cond: (tmp_san_1.text = tmp_san_2.text)
   Filter: (tmp_san_2.id IS NULL)
   Rows Removed by Filter: 1
   ->  Seq Scan on tmp_san_1  (cost=0.00..154.00 rows=10000 width=8) (actual time=0.008..0.983 rows=10000 loops=1)
   ->  Hash  (cost=1.01..1.01 rows=1 width=6) (actual time=0.004..0.004 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on tmp_san_2  (cost=0.00..1.01 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.173 ms
 Execution time: 3.546 ms
(10 rows)

I cannot explain that behavior and much less think of a fix or workaround. Unfortunately my real-world example has to use varchar for the join.

Thanks for any help,
Stefan