Re: Improving the timing of a query

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(at)postgresql(dot)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
>