Thread: Indexes and differing column types

Hi all,

this is my first mail to pgsql-hackers, so first I want to thank you all for
your great work. PostgreSQL is an amazing database management system and
wonderful to use.

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?

Best Regards,
Michael Paesold

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