>>>>> "Shridhar" == Shridhar Daithankar <[EMAIL PROTECTED]> writes:
Shridhar> Laurent Martelli wrote: >>>>>>> "Shridhar" == Shridhar Daithankar >>>>>>> <[EMAIL PROTECTED]> 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. Shridhar> I am stripping the analyze outputs and directly jumping to Shridhar> the end. Shridhar> Can you try following? Shridhar> 1. Make all fields integer in all the table. I can't do this because lists.values contains non integer data which do not refer to a classes.id value. It may sound weird. This is because it's a generic schema for a transparent persistence framework. The solution for me would rather be to have varchar everywhere. Shridhar> 2. Try following query EXPLAIN ANALYZE SELECT * from lists Shridhar> join classes on classes.id=lists.value where Shridhar> lists.id='16'::integer; Shridhar> How does it affect the runtime? Shridhar> Shridhar -- Laurent Martelli [EMAIL PROTECTED] Java Aspect Components http://www.aopsys.com/ http://jac.aopsys.com ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
