[GENERAL] Remove useless joins (VARCHAR vs TEXT)
| От | Kim Rose Carlsen |
|---|---|
| Тема | [GENERAL] Remove useless joins (VARCHAR vs TEXT) |
| Дата | |
| Msg-id | VI1PR05MB17091F9A9876528055D6A827C76D0@VI1PR05MB1709.eurprd05.prod.outlook.com обсуждение исходный текст |
| Ответы |
Re: [GENERAL] Remove useless joins (VARCHAR vs TEXT)
|
| Список | pgsql-general |
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;
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)
------------------------------------------------------------------
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;
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)
------------------------------------------------------
Seq Scan on a (cost=0.00..23.60 rows=1360 width=32)
- Kim Carlsen
В списке pgsql-general по дате отправления: