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
