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 32AE9D1D5CF for ; Wed, 19 Nov 2003 10:20:04 +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 40541-09 for ; Wed, 19 Nov 2003 06:19:35 -0400 (AST) Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [192.108.102.143]) by svr1.postgresql.org (Postfix) with ESMTP id 86E61D1D80C for ; Wed, 19 Nov 2003 06:19:32 -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); Wed, 19 Nov 2003 03:19:30 -0700 Message-ID: <3FBB43AB.30302@myrealbox.com> Date: Wed, 19 Nov 2003 15:49:23 +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> <3FBB138C.4090303@myrealbox.com> <87u150k7mc.fsf@news.nerim.net> In-Reply-To: <87u150k7mc.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/154 X-Sequence-Number: 4714 Laurent Martelli wrote: >>>>>>"Shridhar" == Shridhar Daithankar writes: > 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. Fine .I understand. So instead of using a field value, can you use integer version of that field? (Was that one of your queries used that? I deleted the OP) > The solution for me would rather be to have varchar everywhere. You need to cast every occurance of that varchar field appropriately, to start with. The performance might suffer as well for numbers. > Shridhar> 2. Try following query EXPLAIN ANALYZE SELECT * from lists > Shridhar> join classes on classes.id=lists.value where > Shridhar> lists.id='16'::integer; classes.id=lists.value::integer. Try that. The aim is absolute type compatibility. If types aren't exactly same, the plan is effectively dead. I would say postgresql enforces good habits in it's application developers, from a cultural POV. Had C refused to compile without such strict type compatibility, we wouldn't have to worry about 16bit/32bit and 64 bit software. Just upgrade the compiler and everything is damn good..:-) I doubt if C would have so popular with such strict type checking but that is another issue. I think pascal enforces such strict syntax.. Not sure though.. Shridhar