Why is a stored procedures much slower than the single query?

Question

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;

Answer 1

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

Answer 2

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.

Answer 3

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 */