X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 61623D1B446 for ; Mon, 5 Jan 2004 12:39:40 +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 59036-09 for ; Mon, 5 Jan 2004 08:38:54 -0400 (AST) Received: from smtp.pspl.co.in (www.pspl.co.in [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id C2195D1B43A for ; Mon, 5 Jan 2004 08:38:48 -0400 (AST) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id i05CcvOw011144 for ; Mon, 5 Jan 2004 18:08:57 +0530 Received: from daithan.intranet.pspl.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id i05CcuJN011131 for ; Mon, 5 Jan 2004 18:08:57 +0530 From: Shridhar Daithankar To: pgsql-performance@postgresql.org Subject: Re: optimizing Postgres queries Date: Mon, 5 Jan 2004 18:07:53 +0530 User-Agent: KMail/1.5.4 References: <4B0023AC-3F72-11D8-B3EB-000A95A6F0DC@cluster9.com> <200401051740.05006.shridhar_daithankar@myrealbox.com> <3796212E-3F79-11D8-A528-000A95A6F0DC@cluster9.com> In-Reply-To: <3796212E-3F79-11D8-A528-000A95A6F0DC@cluster9.com> MIME-Version: 1.0 Content-Disposition: inline Message-Id: <200401051806.27496.shridhar_daithankar@myrealbox.com> Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/31 X-Sequence-Number: 5206 On Monday 05 January 2004 17:48, David Teran wrote: > Hi, > > > The performance will likely to be the same. Its just that integer > > happens to > > be default integer type and hence it does not need an explicit > > typecast. ( I > > don't remember exactly which integer is default but it is either of > > int2,int4 > > and int8...:-)) > > The docs say int4 is much faster than int8, but i will check this. Well yes. That is correct as well. What I (really) meant to say that an index scan to pick few in4 tuples wouldn't be hell much faster than an index scan to pick same number of tuples with int8 definition. The initial boost you got from converting to index scan, would be probably best you can beat out of it.. Of course if you are scanning a few million of them sequentially, then it is different story. > This is my bigger problem: i am using EOF (OR mapping tool) which frees > me more or less form writing a lot of SQL. If i need to typecast to use > an index then i have to see how to do this with this framework. Well, you can direct your queries to a function rather than table, that would cast the argument appropriately and select. Postgresql support function overloading as well, in case you need different types of arguments with same name. Or you can write an instead rule on server side which will perform casting before touching the table. I am not sure of exact details it would take to make it work, but it should work, at least in theory. That way you can preserve the efforts invested in the mapping tool. Of course, converting everything to integer might be a simpler option after all..:-) Shridhar