pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance delay
Date: Thu, 13 Jan 2005 19:14:10 +0800
Message-ID: <41E65802.5070400@mimos.my> (raw)
In-Reply-To: <41E6552C.3020801@archonet.com>
References: <41E63294.9040605@mimos.my>
<41E6552C.3020801@archonet.com>
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
>
>
view thread (4+ messages) latest in thread
Message-ID: <41E65802.5070400@mimos.my> Permalink: ../41E65802.5070400@mimos.my/ Also on: postgresql.org/message-id/41E65802.5070400@mimos.my ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-performance@postgresql.org Cc: hasnulfadhly.h@mimos.my, dev@archonet.com Subject: Re: Performance delay In-Reply-To: <41E65802.5070400@mimos.my> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox