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 37D413A5C31 for ; Thu, 13 Jan 2005 11:14:28 +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 21199-01 for ; Thu, 13 Jan 2005 11:14:18 +0000 (GMT) Received: from ew.mimos.my (ew.mimos.my [192.228.129.34]) by svr1.postgresql.org (Postfix) with ESMTP id 39B233A4F1F for ; Thu, 13 Jan 2005 11:14:19 +0000 (GMT) Received: from mimos.my (mcg189.nat.mimos.my [10.1.18.189]) by ew.mimos.my (8.12.9p2/8.12.9) with ESMTP id j0DBEEsP078887; Thu, 13 Jan 2005 19:14:14 +0800 (MYT) (envelope-from hasnulfadhly.h@mimos.my) Message-ID: <41E65802.5070400@mimos.my> Date: Thu, 13 Jan 2005 19:14:10 +0800 From: Hasnul Fadhly bin Hasan User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard Huxton Cc: pgsql-performance@postgresql.org Subject: Re: Performance delay References: <41E63294.9040605@mimos.my> <41E6552C.3020801@archonet.com> In-Reply-To: <41E6552C.3020801@archonet.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.063 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/165 X-Sequence-Number: 9855 Hi Richard, Thanks for the reply.. is that the case? i thought it would comply to the where condition first.. and after that it will format the output to what we want.. Hasnul Richard Huxton wrote: > 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 > >