| View previous topic :: View next topic |
yancho
Site Admin
Joined: 13 Nov 2007
Posts: 58
Location: Iklin
|
 Posted: Fri Dec 07, 2007 9:52 am Post subject: [PostgreSQL] Optimizing 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
The paste bin can be found here : http://yancho.pastebin.com/f2f986b8c
At the end of the paste you can find the EXPLAIN ANALYZE of the query.
As you can see I have highlighted in yellow some RAISE NOTICEs to time the query and these are the results :
Below please find the result :
| Code: |
- 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 :
| Quote: |
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.
|
Hope this information can help you give me a better idea on how I can improve this query.
Cheers
_________________
 |
|
| Back to top |
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
Powered by phpBB © 2001, 2002 phpBB Group |
|
|
Forum Subscriptions
