Обсуждение: 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 > 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 > > ---------------------------(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/ > 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 :(
--
Matthew Pulis
URL : http://www.solutions-lab.net
MSN : pulis_matthew[@]hotmail.com
ICQ : 145951110
Skype : solutions-lab.net
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