Remove useless joins (VARCHAR vs TEXT)

[Kim Rose Carlsen <krc@xxxxxxxx>]

Hi

It seems there are some difference in VARCHAR vs TEXT when postgres tries to decide if a LEFT JOIN is useful or not. I can't figure out if this is intentional because there are some difference between TEXT and VARCHAR that I dont know about or if it's a bug.

I would expect both examples to produce same query plan

a)

create table a (id varchar primary key);
create table b (id varchar primary key);

explain   select a.*
     from a
left join (select distinct id from b) as b
       on a.id = b.id;

                            QUERY PLAN                            
------------------------------------------------------------------
 Hash Right Join  (cost=67.60..113.50 rows=1360 width=32)
   Hash Cond: ((b.id)::text = (a.id)::text)
   ->  HashAggregate  (cost=27.00..40.60 rows=1360 width=32)
         Group Key: b.id
         ->  Seq Scan on b  (cost=0.00..23.60 rows=1360 width=32)
   ->  Hash  (cost=23.60..23.60 rows=1360 width=32)
         ->  Seq Scan on a  (cost=0.00..23.60 rows=1360 width=32)
(7 rows)

b)

create table a (id text primary key);

create table b (id text primary key);

explain   select a.*
     from a
left join (select distinct id from b) as b
       on a.id = b.id;

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on a  (cost=0.00..23.60 rows=1360 width=32)

- Kim Carlsen