pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery slow as function
9+ messages / 5 participants
[nested] [flat]
* Query slow as function
@ 2012-02-18 14:50 Steve Horn <steve@stevehorn.cc>
0 siblings, 1 reply; 9+ messages in thread
From: Steve Horn @ 2012-02-18 14:50 UTC (permalink / raw)
To: 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!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Query slow as Function
@ 2012-02-18 15:03 Steve Horn <steve@stevehorn.cc>
0 siblings, 1 reply; 9+ messages in thread
From: Steve Horn @ 2012-02-18 15:03 UTC (permalink / raw)
To: 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!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query slow as Function
@ 2012-02-18 16:02 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Steve Horn <steve@stevehorn.cc>
0 siblings, 1 reply; 9+ messages in thread
From: Andreas Kretschmer @ 2012-02-18 16:02 UTC (permalink / raw)
To: pgsql-performance
Steve Horn <steve@stevehorn.cc> wrote:
> Execute the function: select * from geocode_carrier_route_by_geocode('xyz');
>
> This query takes 500 milliseconds to run. My question of course is why?
Wild guess:
The planner doesn't know the actual value of the input-parameter, so the
planner doesn't use the Index.
>
> 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?
The planner, in this case, knows the actual value.
>
> Thanks for any help in clarifying my understanding!
You can check the plan with the auto_explain - Extension, and you can
force the planner to create a plan based on the actual input-value by
using dynamic SQL (EXECUTE 'your query string' inside the function)
As i said, wild guess ...
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query slow as Function
@ 2012-02-18 16:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 3 replies; 9+ messages in thread
From: Tom Lane @ 2012-02-18 16:37 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: Steve Horn <steve@stevehorn.cc>; pgsql-performance
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> You can check the plan with the auto_explain - Extension, and you can
> force the planner to create a plan based on the actual input-value by
> using dynamic SQL (EXECUTE 'your query string' inside the function)
Steve *is* using EXECUTE, so that doesn't seem to be the answer. I'm
wondering about datatype mismatches myself --- the function form is
forcing the parameter to be char(9), which is not a constraint imposed
in the written-out query. There are lots of other possibilities
though. It would be hard to say much without a self-contained example
to try.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query slow as Function
@ 2012-02-18 16:41 Andrew Dunstan <andrew@dunslane.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 9+ messages in thread
From: Andrew Dunstan @ 2012-02-18 16:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>; Steve Horn <steve@stevehorn.cc>; pgsql-performance
On 02/18/2012 11:37 AM, Tom Lane wrote:
> Andreas Kretschmer<akretschmer@spamfence.net> writes:
>> You can check the plan with the auto_explain - Extension, and you can
>> force the planner to create a plan based on the actual input-value by
>> using dynamic SQL (EXECUTE 'your query string' inside the function)
> Steve *is* using EXECUTE, so that doesn't seem to be the answer. I'm
> wondering about datatype mismatches myself --- the function form is
> forcing the parameter to be char(9), which is not a constraint imposed
> in the written-out query. There are lots of other possibilities
> though. It would be hard to say much without a self-contained example
> to try.
>
>
He's using EXECUTE ... USING. Does that plan with the used parameter?
cheers
andrew
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query slow as Function
@ 2012-02-18 17:15 Steve Horn <steve@stevehorn.cc>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 9+ messages in thread
From: Steve Horn @ 2012-02-18 17:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
Tom,
Thank you for your thoughts as it lead me to the solution. My column
"geocode" is defined as character varying (9), and my function parameter as
character(9). Changing the input parameter type to match the column
definition caused my procedure to execute in 10 milliseconds.
I was even able to refactor the method to a non-dynamic SQL call (allowing
me to take the SQL out of a string and dropping the EXECUTE):
RETURN QUERY
SELECT gs.geo_shape_id AS gid,
gs.geocode
FROM geo_shapes gs
WHERE gs.geocode = geo_code
AND geo_type = 1
GROUP BY gs.geography, gs.geo_shape_id, gs.geocode;
Thanks for your help!
On Sat, Feb 18, 2012 at 11:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andreas Kretschmer <akretschmer@spamfence.net> writes:
> > You can check the plan with the auto_explain - Extension, and you can
> > force the planner to create a plan based on the actual input-value by
> > using dynamic SQL (EXECUTE 'your query string' inside the function)
>
> Steve *is* using EXECUTE, so that doesn't seem to be the answer. I'm
> wondering about datatype mismatches myself --- the function form is
> forcing the parameter to be char(9), which is not a constraint imposed
> in the written-out query. There are lots of other possibilities
> though. It would be hard to say much without a self-contained example
> to try.
>
> regards, tom lane
>
--
Steve Horn
http://www.stevehorn.cc
steve@stevehorn.cc
http://twitter.com/stevehorn
740-503-2300
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query slow as Function
@ 2012-02-18 17:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2012-02-18 17:20 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>; Steve Horn <steve@stevehorn.cc>; pgsql-performance
Andrew Dunstan <andrew@dunslane.net> writes:
> He's using EXECUTE ... USING. Does that plan with the used parameter?
Yeah, it's supposed to. One of the open possibilities is that that's
malfunctioning for some reason, but without a test case to trace through
I wouldn't jump to that as the most likely possibility.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query slow as Function
@ 2012-02-18 17:32 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 9+ messages in thread
From: Andreas Kretschmer @ 2012-02-18 17:32 UTC (permalink / raw)
To: pgsql-performance
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andreas Kretschmer <akretschmer@spamfence.net> writes:
> > You can check the plan with the auto_explain - Extension, and you can
> > force the planner to create a plan based on the actual input-value by
> > using dynamic SQL (EXECUTE 'your query string' inside the function)
>
> Steve *is* using EXECUTE, so that doesn't seem to be the answer. I'm
Ohhh yes, my fault, sorry...
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query slow as function
@ 2012-02-20 17:32 Merlin Moncure <mmoncure@gmail.com>
parent: Steve Horn <steve@stevehorn.cc>
0 siblings, 0 replies; 9+ messages in thread
From: Merlin Moncure @ 2012-02-20 17:32 UTC (permalink / raw)
To: Steve Horn <steve@stevehorn.cc>; +Cc: pgsql-performance
On Sat, Feb 18, 2012 at 8:50 AM, Steve Horn <steve@stevehorn.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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2012-02-20 17:32 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-18 14:50 Query slow as function Steve Horn <steve@stevehorn.cc>
2012-02-20 17:32 ` Merlin Moncure <mmoncure@gmail.com>
2012-02-18 15:03 Query slow as Function Steve Horn <steve@stevehorn.cc>
2012-02-18 16:02 ` Re: Query slow as Function Andreas Kretschmer <akretschmer@spamfence.net>
2012-02-18 16:37 ` Re: Query slow as Function Tom Lane <tgl@sss.pgh.pa.us>
2012-02-18 16:41 ` Re: Query slow as Function Andrew Dunstan <andrew@dunslane.net>
2012-02-18 17:20 ` Re: Query slow as Function Tom Lane <tgl@sss.pgh.pa.us>
2012-02-18 17:15 ` Re: Query slow as Function Steve Horn <steve@stevehorn.cc>
2012-02-18 17:32 ` Re: Query slow as Function Andreas Kretschmer <akretschmer@spamfence.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox