X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E3D8DD1D5AB for ; Wed, 19 Nov 2003 06:54:43 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 14518-03 for ; Wed, 19 Nov 2003 02:54:13 -0400 (AST) Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [192.108.102.143]) by svr1.postgresql.org (Postfix) with ESMTP id C8C3DD1B4E6 for ; Wed, 19 Nov 2003 02:54:11 -0400 (AST) Received: from myrealbox.com shridhar_daithankar@smtp-send.myrealbox.com [202.54.11.72] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 3.44 $ on Novell NetWare via secured & encrypted transport (TLS); Tue, 18 Nov 2003 23:54:10 -0700 Message-ID: <3FBB138C.4090303@myrealbox.com> Date: Wed, 19 Nov 2003 12:24:04 +0530 From: Shridhar Daithankar User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031013 Thunderbird/0.3 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Laurent Martelli Cc: pgsql-performance@postgresql.org Subject: Re: Join on incompatible types References: <87k75ym1v7.fsf@news.nerim.net> <3FB9F855.7010905@myrealbox.com> <87oev9lsgs.fsf@news.nerim.net> In-Reply-To: <87oev9lsgs.fsf@news.nerim.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/151 X-Sequence-Number: 4711 Laurent Martelli wrote: >>>>>>"Shridhar" == Shridhar Daithankar 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