Q:

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

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;

A:

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 

