[postgis-users] RE: Improving the timing of a function
Matthew Pulis
mpulis at gmail.com
Thu Dec 6 12:41:09 PST 2007
Hi again,
Thanks to those who already tried to help me I have put some RAISE NOTICE
and here is the result :
This is the pastebin of the new code : http://yancho.pastebin.com/f2f986b8c
<- marked with yellow are the new RAISE NOTICEs
Below please find the result :
- filling up the hospital_location with that query took : 32646ms
- the IF section took 994 ms
- returning back to the FOR loop took : 104 ms
- Shooting Star execution took : 25079ms
- If statement took : 614ms
- Shooting star took : 37927 ms
- If took : 300 ms
Total query runtime: 114250 ms.
2 rows retrieved.
Execution plan :
NOTICE: Entering the FOR IN Thu Dec 06 21:02:46.267488 2007 CET
NOTICE: entering shooting star Thu Dec 06 21:02:46.300164 2007 CET
NOTICE: before IF Thu Dec 06 21:03:24.307906 2007 CET
NOTICE: value of nearest.dist is 585.966565014107
NOTICE: value of hospital.gid is 12712
NOTICE: value of nearest.gid is <NULL>
NOTICE: value of 2ND nearest.gid is POINT(640607.6248615 224673.124400562)
NOTICE: after if Thu Dec 06 21:03:24.308900 2007 CET
NOTICE: before end of loop Thu Dec 06 21:03:24.309029 2007 CET
NOTICE: entering shooting star Thu Dec 06 21:03:24.309133 2007 CET
NOTICE: before IF Thu Dec 06 21:04:02.024339 2007 CET
NOTICE: value of nearest.dist is 91.0322939509899
NOTICE: value of hospital.gid is 38600
NOTICE: value of nearest.gid is POINT(640607.6248615 224673.124400562)
NOTICE: value of 2ND nearest.gid is POINT(640952.4998615 224309.563400563)
NOTICE: after if Thu Dec 06 21:04:02.024953 2007 CET
NOTICE: before end of loop Thu Dec 06 21:04:02.025093 2007 CET
NOTICE: entering shooting star Thu Dec 06 21:04:02.025178 2007 CET
NOTICE: before IF Thu Dec 06 21:04:39.952201 2007 CET
NOTICE: after if Thu Dec 06 21:04:39.952510 2007 CET
NOTICE: before end of loop Thu Dec 06 21:04:39.952598 2007 CET
NOTICE: after end of loop before returning Thu Dec 06 21:04:39.952846 2007
CET
Total query runtime: 114250 ms.
2 rows retrieved.
I hope this can help better J
Thanks
From: Matthew Pulis [mailto:mpulis at gmail.com]
Sent: Thursday, December 06, 2007 8:31 PM
To: 'PostGIS Users Discussion'
Subject: Improving the timing of a function
Hi,
I added this function to find the nearest hospital using the distance
covered on the route itself.
My reasoning was this :
- Find the 3 most near hospitals using distance() function
- Iterate the 3 hospitals and find the one which is the shortest
distance taking into considerations one-ways et al. using shootingstar_sp()
pgRouting Function.
Using Pastebin I have pasted my current function and also inline commented
more on the function
At the end of the paste you can find the EXPLAIN ANALYZE of the query.
Paste can be found here : http://yancho.pastebin.com/f3cca3a41
May I point out that I tried the first SELECT query without LIMIT 3 and it
gave me 52 points in 32ms and with LIMIT 3 it gave me the 3 points in 31 ms.
Any help would be extremely appreciated J
Thanks and regards
Matthew Pulis
_____
I am using the free version of SPAMfighter for private users.
It has removed 18467 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter <http://www.spamfighter.com/len> for free now!
_____
I am using the free version of SPAMfighter for private users.
It has removed 18467 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter <http://www.spamfighter.com/len> for free now!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071206/d113b238/attachment.html>
More information about the postgis-users
mailing list