Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I wrote a normal query to snap the next way point from a pair of coordinates in EPSG4326, nothing complex. To optimize the performance a bit I made a boundary condition to minimize the points needed to be checked...

Works smooth so far - but then I wrote a stored procedure to make later a neat intergeneration... (quasi wrapper). And here is where my headache starts:

  • Single SQL: 650ms
  • Stored Procedure: 45xxxms
  • Single SQL is 70x faster than my storage procedure!

Code below...


CREATE OR REPLACE FUNCTION next_way_point(p geometry) RETURNS next_way_point_result AS $$
SELECT gid, source, target, the_geom, ST_distance(the_geom, ST_SetSRID($1,4326)) as dist 
FROM ways 
WHERE the_geom && ST_Buffer(ST_setSRID($1,4326), 0.1)
ORDER BY dist LIMIT 1 $$
LANGUAGE SQL;

Called like this:

SELECT * FROM next_way_point(ST_MakePoint(7.26, 47.52));

VS

SELECT gid, source, target, the_geom, ST_distance(the_geom, ST_SetSRID(ST_MakePoint(7.26, 47.52),4326)) as dist 
FROM ways 
WHERE the_geom && ST_Buffer(ST_setSRID(ST_MakePoint(7.26, 47.52),4326),0.1) 
ORDER BY dist LIMIT 1;
share|improve this question
add comment

3 Answers

In your function version something seems to stop the index from working.

But if you look at the sql-definition of a function like ST_DWithin it is:

CREATE OR REPLACE FUNCTION st_dwithin(geography, geography, double precision)
RETURNS boolean AS
'SELECT $1 && _ST_Expand($2,$3) AND $2 && _ST_Expand($1,$3) 
AND _ST_DWithin($1, $2, $3, true)'

What you see here is that the sql function tries both

$1 && _ST_Expand($2,$3)

and

$2 && _ST_Expand($1,$3)

The reason has to do with how the query optimizer chooses how to use the index. I am not the right person to explain this.

I also think it is better to use expand instead of buffering. Buffering is expensive. In geography case it also causes transformations to geometry.

What I would try is:

CREATE OR REPLACE FUNCTION next_way_point(p geometry) RETURNS next_way_point_result AS $$
SELECT gid, source, target, the_geom, ST_distance(the_geom, ST_SetSRID($1,4326)) as dist 
FROM ways 
WHERE ST_DWithin(the_geom,ST_setSRID($1,4326), 0.1)
ORDER BY dist LIMIT 1 $$
LANGUAGE SQL;

But the extremely most effective way to do this is to find a propriate projection and use knn-distance

share|improve this answer
    
+1 for pointing out that knn-distance is the right approach –  Ragi Yaser Burhum Nov 29 '12 at 21:38
    
KNN distance is a very clever idea.... –  Styp Dec 1 '12 at 12:01
add comment

A possible point can be the query optimizer of the database. If you use a normal sql statement the database parse your code, optimize it (depending the current state of database - memory situation, size of tables, ...), compile and run it.

Creating stored procedures summarizes the steps to compile, before the actual execution. Thus the creation of the procedure and the retrieval of data are separated in time, the optimizer may not be as effective. Especially with complex queries on large tables could lead to reduced performance.

share|improve this answer
add comment

Try to compare the output of explain analyse ... maybe it shows the difference

/* 1. start for instance psql and log into */
/* 2. call procedure with the select */
explain analyse SELECT * FROM next_way_point(ST_MakePoint(7.26, 47.52));

/* 3. call the single sql */
explain analyse SELECT gid, source, target, the_geom, ST_distance(the_geom, ST_SetSRID(ST_MakePoint(7.26, 47.52),4326)) as dist 
FROM ways 
WHERE the_geom && ST_Buffer(ST_setSRID(ST_MakePoint(7.26, 47.52),4326),0.1) 
ORDER BY dist LIMIT 1;

/* compare the execution plans */
share|improve this answer
    
EXPLAIN ANALYZE doesn't work with storedprocedures, there is no in detail analysis... –  Styp Nov 29 '12 at 15:34
    
@Martin :-(, can you provide a dump of your ways table? Maybe I find something in my postgresql versions. What version do you use? –  OkieOth Nov 29 '12 at 16:03
    
@Martin btw I often use views for that kind of wrapping –  OkieOth Nov 29 '12 at 19:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.