Re: Join on incompatible types

Laurent Martelli wrote:

>>>>>>"Shridhar" == Shridhar Daithankar <shridhar_daithankar(at)myrealbox(dot)com> writes:
>
>
> Shridhar> Laurent Martelli wrote:
>
> [...]
>
> >> Should I understand that a join on incompatible types (such as
> >> integer and varchar) may lead to bad performances ?
>
> Shridhar> Conversely, you should enforce strict type compatibility
> Shridhar> in comparisons for getting any good plans..:-)
>
> Ha ha, now I understand why a query of mine was so sluggish.
>
> Is there a chance I could achieve the good perfs without having he
> same types ? I've tried a CAST in the query, but it's even a little
> worse than without it. However, using a view to cast integers into
> varchar gives acceptable results (see at the end).
>
> I'm using Postgresql 7.3.4.

I am stripping the analyze outputs and directly jumping to the end.

Can you try following?

1. Make all fields integer in all the table.
2. Try following query
EXPLAIN ANALYZE SELECT * from lists join classes on classes.id=lists.value where
lists.id='16'::integer;

How does it affect the runtime?

Shridhar