pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance delay
4+ messages / 3 participants
[nested] [flat]
* Performance delay
@ 2005-01-13 08:34 Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
2005-01-13 11:02 ` Re: Performance delay Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Hasnul Fadhly bin Hasan @ 2005-01-13 08:34 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance delay
2005-01-13 08:34 Performance delay Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
@ 2005-01-13 11:02 ` Richard Huxton <dev@archonet.com>
2005-01-13 11:14 ` Re: Performance delay Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2005-01-13 11:02 UTC (permalink / raw)
To: Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance delay
2005-01-13 08:34 Performance delay Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
2005-01-13 11:02 ` Re: Performance delay Richard Huxton <dev@archonet.com>
@ 2005-01-13 11:14 ` Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
2005-01-13 13:45 ` Re: Performance delay Jim C. Nasby <decibel@decibel.org>
0 siblings, 1 reply; 4+ messages in thread
From: Hasnul Fadhly bin Hasan @ 2005-01-13 11:14 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance delay
2005-01-13 08:34 Performance delay Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
2005-01-13 11:02 ` Re: Performance delay Richard Huxton <dev@archonet.com>
2005-01-13 11:14 ` Re: Performance delay Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
@ 2005-01-13 13:45 ` Jim C. Nasby <decibel@decibel.org>
0 siblings, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2005-01-13 13:45 UTC (permalink / raw)
To: Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
On Thu, Jan 13, 2005 at 07:14:10PM +0800, Hasnul Fadhly bin Hasan wrote:
> 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..
That is in fact exactly what it's doing. The second query is faster not
because of the where clause, but because of the limit clause. The first
query builds a list of id, long2ip(srcip), long2ip(dstip) for the
timestamp range, then it orders that list and gives you the first 30.
The second query builds a list of everything from sometable for the
timestamp range, orders it, keeps the first 30, THEN in calculates
long2ip based on that list of 30 items.
> 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
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-01-13 13:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-13 08:34 Performance delay Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
2005-01-13 11:02 ` Richard Huxton <dev@archonet.com>
2005-01-13 11:14 ` Hasnul Fadhly bin Hasan <hasnulfadhly.h@mimos.my>
2005-01-13 13:45 ` Jim C. Nasby <decibel@decibel.org>
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