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 95820D1B473 for ; Mon, 5 Jan 2004 12:11:48 +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 55973-08 for ; Mon, 5 Jan 2004 08:11:02 -0400 (AST) Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [192.108.102.143]) by svr1.postgresql.org (Postfix) with ESMTP id 1F3B4D1B44D for ; Mon, 5 Jan 2004 08:10:59 -0400 (AST) Received: from daithan.intranet.pspl.co.in shridhar_daithankar@smtp-send.myrealbox.com [202.54.11.72] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 3.47 $ on Novell NetWare via secured & encrypted transport (TLS); Mon, 05 Jan 2004 05:11:02 -0700 From: Shridhar Daithankar To: pgsql-performance@postgresql.org Subject: Re: optimizing Postgres queries Date: Mon, 5 Jan 2004 17:40:05 +0530 User-Agent: KMail/1.5.4 References: <4B0023AC-3F72-11D8-B3EB-000A95A6F0DC@cluster9.com> <200401051705.55120.shridhar_daithankar@myrealbox.com> <64C8DBBA-3F77-11D8-A528-000A95A6F0DC@cluster9.com> In-Reply-To: <64C8DBBA-3F77-11D8-A528-000A95A6F0DC@cluster9.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200401051740.05006.shridhar_daithankar@myrealbox.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/29 X-Sequence-Number: 5204 On Monday 05 January 2004 17:35, David Teran wrote: > explain analyze SELECT --columns-- FROM KEY_VALUE_META_DATA t0 WHERE > t0.ID_FOREIGN_TABLE = 21110; > > i see that no index is being used whereas when i use > > explain analyze SELECT --columns-- FROM KEY_VALUE_META_DATA t0 WHERE > t0.ID_FOREIGN_TABLE = 21110::bigint; > > an index is used. Very fine, the performance is about 10 to 100 times > faster for the single select. > > I am using WebObjects with JDBC. I will now create a DB with integer > instead of bigint and see how this performs. 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 performance diffference is likely due to use of index, which is in turn due to typecasting. If you need bigint, you should use them. Just remember to typecast whenever required. Shridhar