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)