Re: Query slow as function

On Sat, Feb 18, 2012 at 8:50 AM, Steve Horn <steve(at)stevehorn(dot)cc> wrote:
> Hello all!
>
> I have a very simple query that I am trying to wrap into a function:
>
> SELECT gs.geo_shape_id AS gid,
> gs.geocode
> FROM geo_shapes gs
> WHERE gs.geocode = 'xyz'
> AND geo_type = 1
> GROUP BY gs.geography, gs.geo_shape_id, gs.geocode;
>
> This query runs in about 10 milliseconds.
>
> Now my goal is to wrap the query in a function:
>
> I create a return type:
> CREATE TYPE geocode_carrier_route_by_geocode_result AS
>    (gid integer,
>     geocode character varying(9));
> ALTER TYPE geocode_carrier_route_by_geocode_result
>   OWNER TO root;
>
> ..and the function
> CREATE OR REPLACE FUNCTION geocode_carrier_route_by_geocode(geo_code
> character(9))
>   RETURNS SETOF geocode_carrier_route_by_geocode_result AS
> $BODY$
>
> BEGIN
>
> RETURN QUERY EXECUTE
> 'SELECT gs.geo_shape_id AS gid,
> gs.geocode
> FROM geo_shapes gs
> WHERE gs.geocode = $1
> AND geo_type = 1
> GROUP BY gs.geography, gs.geo_shape_id, gs.geocode'
> USING geo_code;
>
> END;
>
> $BODY$
>   LANGUAGE plpgsql STABLE;
> ALTER FUNCTION geocode_carrier_route_by_geocode(character)
>   OWNER TO root;
>
> Execute the function: select * from geocode_carrier_route_by_geocode('xyz');
>
> This query takes 500 milliseconds to run. My question of course is why?
>
> Related: If I create a function and assign LANGUAGE 'sql', my function runs
> in the expected 10 milliseconds. Is there some overhead to using the plpgsql
> language?
>
> Thanks for any help in clarifying my understanding!

not overhead. it's how the plans are generated. plpgsql builds out
the query plan and caches it. sql language function replan the query
on every execution. caching the plan can help or hurt depending on how
sensitive the plan is to the supplied parameters -- plpgsql can't (and
shouldn't) use the actual parameter value when generating the plan.
OTOH, for very long functions especially the amount of time spent in
plan generation can really add up so plpgsql can often be faster than
vanilla sql.

to force plpgsql smarter plans, you can maybe attempt 'SET LOCAL
enable_xxx' planner directives. pretty hacky, but maybe might help in
your case. also better statistics might help.

merlin