Improving the timing of a query
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 :
- 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.
Hope this information can help you give me a better idea on how I can
improve this query.
Thanks and regards
Matthew
assign pointfromtext(pojnt) to a variable
other DB's do FTS when there is a function involved in the predicate (WHERE
clause)
so a possible workaround would be to look at all function calls in your
predicate (WHERE clause) and
populate a new column with the results of the function(column)
and then create and populate an index which will reference the
function(column)
Anyone?
M-
----- Original Message -----
Wrom: MQZUIVOTQNQEMSFDULHPQQWOYIYZUNNYCG
To: <pgsql-general@postgresql.org>
Sent: Thursday, December 06, 2007 6:28 PM
Subject: [GENERAL] Improving the timing of a query
Show quoted text
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 functionThe 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 :
- 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 msTotal 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 CETTotal 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.Thanks and regards
Matthew
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
On Fri, Dec 07, 2007 at 12:07:52PM -0500, mgainty@hotmail.com wrote:
other DB's do FTS when there is a function involved in the predicate (WHERE
clause)
so a possible workaround would be to look at all function calls in your
predicate (WHERE clause) and
populate a new column with the results of the function(column)
and then create and populate an index which will reference the
function(column)
You don't need to create a column, yu can create functional indexes in
postgres:
CREATE INDEX foo ON bar ((myfunction(column)));
And it can be used anytime your query says: WHERE myfunction(column) = X
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Those who make peaceful revolution impossible will make violent revolution inevitable.
-- John F Kennedy
Thanks for your suggestions :)
Even though the ideas are good, the function is working really fast now,
1.5-2seconds which is quite enough I guess :)
This is how the function is at the moment :
http://yancho.pastebin.com/d236fd97f
Sorry I forgot to update this thread :(
On 12/7/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
On Fri, Dec 07, 2007 at 12:07:52PM -0500, mgainty@hotmail.com wrote:
other DB's do FTS when there is a function involved in the predicate
(WHERE
clause)
so a possible workaround would be to look at all function calls in your
predicate (WHERE clause) and
populate a new column with the results of the function(column)
and then create and populate an index which will reference the
function(column)You don't need to create a column, yu can create functional indexes in
postgres:CREATE INDEX foo ON bar ((myfunction(column)));
And it can be used anytime your query says: WHERE myfunction(column) = X
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/Those who make peaceful revolution impossible will make violent
revolution inevitable.
-- John F Kennedy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFHWYaJIB7bNG8LQkwRAmOyAJ92ZtXFiQfme/Rn2+2ylISrR3YM+ACfTWO8
ftUieOQhrQCQy+rBAElRr68=
=ALYk
-----END PGP SIGNATURE-----
--
Matthew Pulis
URL : http://www.solutions-lab.net
MSN : pulis_matthew[@]hotmail.com
ICQ : 145951110
Skype : solutions-lab.net