Skip site navigation (1) Skip section navigation (2)

Query slow as function

From: Steve Horn <steve(at)stevehorn(dot)cc>
To: pgsql-performance(at)postgresql(dot)org
Subject: Query slow as function
Date: 2012-02-18 14:50:28
Message-ID: CAFLkBaVCkZy-rfPakqmYPQW3zWDo2eB_RwBELNF1=7ZJjAvGMQ@mail.gmail.com (view raw or flat)
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!

Responses

pgsql-performance by date

Next:From: Steve HornDate: 2012-02-18 15:03:46
Subject: Query slow as Function
Previous:From: Ants AasmaDate: 2012-02-18 05:29:15
Subject: Re: Why so slow?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group