Performance delay - Mailing list pgsql-performance

From Hasnul Fadhly bin Hasan
Subject Performance delay
Date
Msg-id 41E63294.9040605@mimos.my
Whole thread Raw
Responses Re: Performance delay  (Richard Huxton <dev@archonet.com>)
List pgsql-performance
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





pgsql-performance by date:

Previous
From: Joe Conway
Date:
Subject: Re: PostgreSQL vs. Oracle vs. Microsoft
Next
From: Mark Kirkwood
Date:
Subject: Re: Postgres Optimizer is not smart enough?