X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EC91B3A4F6C for ; Thu, 13 Jan 2005 11:02:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18214-02 for ; Thu, 13 Jan 2005 11:02:07 +0000 (GMT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id 2CCE93A2585 for ; Thu, 13 Jan 2005 11:02:09 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 4.42) id 1Cp2jw-0001Q1-7z; Thu, 13 Jan 2005 11:02:08 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id A64A816996; Thu, 13 Jan 2005 11:02:07 +0000 (GMT) Message-ID: <41E6552C.3020801@archonet.com> Date: Thu, 13 Jan 2005 11:02:04 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Hasnul Fadhly bin Hasan Cc: pgsql-performance@postgresql.org Subject: Re: Performance delay References: <41E63294.9040605@mimos.my> In-Reply-To: <41E63294.9040605@mimos.my> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.074 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/164 X-Sequence-Number: 9854 Hasnul Fadhly bin Hasan wrote: > Hi, > > just want to share with all of you a wierd thing that i found when i > tested it. > > i was doing a query that will call a function long2ip to convert bigint > to ips. > > so the query looks something like this. > > select id, long2ip(srcip), long2ip(dstip) from sometable > where timestamp between timestamp '01-10-2005' and timestamp '01-10-2005 > 23:59' order by id limit 30; > > for your info, there are about 300k rows for that timeframe. > > it cost me about 57+ secs to get the list. > > which is about the same if i query > select id, long2ip(srcip), long2ip(dstip) from sometable > where timestamp between timestamp '01-10-2005' and timestamp '01-10-2005 > 23:59' > > it will cost me about 57+ secs also. > > Now if i did this > select id,long2ip(srcip), long2ip(dstip) from ( > * from sometable > where timestamp between timestamp '01-10-2005' and timestamp '01-10-2005 > 23:59' order by id limit 30) as t; > > it will cost me about 3+ secs The difference will be that in the final case you only make 30 calls to long2ip() whereas in the first two you call it 300,000 times and then throw away most of them. Try running EXPLAIN ANALYSE ... for both - that will show how PG is planning the query. -- Richard Huxton Archonet Ltd