[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