Re: Indexes and differing column types
От | Michael Paesold |
---|---|
Тема | Re: Indexes and differing column types |
Дата | |
Msg-id | 0a5f01c25c40$6ae2b560$4201a8c0@beeblebrox обсуждение исходный текст |
Ответ на | Indexes and differing column types ("Michael Paesold" <mpaesold@gmx.at>) |
Список | pgsql-hackers |
I wrote: > Concerning this TODO entry: > > > Allow SELECT * FROM tab WHERE int2col = 4 to use int2col index, > > int8, float4, numeric/decimal too [optimizer] > > What about the case of doing a join on columns that don't have the same > type? Sometimes the index will be used, e.g. on this simple query: > > SELECT * FROM a, b WHERE a.int4col = b.int8col; > > Here the index will be used. But there are other queries where it's > necessary to do explicit type casting. I could provide examples. > Is this a known problem? I am sorry, the problem is not with joins but with subqueries. SELECT a.*, (SELECT max(b.someval) FROM b WHERE b.int8val = a.int4val) FROM a; --> QUERY PLAN: Seq Scan on a (cost=0.00..60.37 rows=2237 width=128) SubPlan -> Seq Scan on b (cost=0.00..373.76 rows=1 width=4) SELECT a.*, (SELECT max(b.someval) FROM b WHERE b.int8val = a.int4val::int8) FROM a; --> QUERY PLAN: Seq Scan on a (cost=0.00..60.37 rows=2237 width=128) SubPlan -> Index Scan using b_pkey on b (cost=0.00..2.04 rows=1width=4) Regards, Michael
В списке pgsql-hackers по дате отправления: