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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
