pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance delay
Date: Thu, 13 Jan 2005 11:02:04 +0000
Message-ID: <41E6552C.3020801@archonet.com> (raw)
In-Reply-To: <41E63294.9040605@mimos.my>
References: <41E63294.9040605@mimos.my>

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: <41E6552C.3020801@archonet.com>
Permalink:  ../41E6552C.3020801@archonet.com/
Also on:    postgresql.org/message-id/41E6552C.3020801@archonet.com

 · 

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: dev@archonet.com, hasnulfadhly.h@mimos.my
  Subject: Re: Performance delay
  In-Reply-To: <41E6552C.3020801@archonet.com>

* 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