pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
To: pgsql-performance@postgresql.org
Subject: Performance delay
Date: Thu, 13 Jan 2005 16:34:28 +0800
Message-ID: <41E63294.9040605@mimos.my> (raw)

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

Anyone knows why this is the case?

Hasnul







view thread (4+ messages)  latest in thread

Message-ID: <41E63294.9040605@mimos.my>
Permalink:  ../41E63294.9040605@mimos.my/
Also on:    postgresql.org/message-id/41E63294.9040605@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
  Subject: Re: Performance delay
  In-Reply-To: <41E63294.9040605@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