| From: | Steve Horn <steve(at)stevehorn(dot)cc> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Query slow as Function |
| Date: | 2012-02-18 15:03:46 |
| Message-ID: | CAFLkBaWRKp7+yVdk3dCecztxEztc+YFS4ks=x3KiSWSfyDZvKw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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!
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andreas Kretschmer | 2012-02-18 16:02:44 | Re: Query slow as Function |
| Previous Message | Steve Horn | 2012-02-18 14:50:28 | Query slow as function |