Re: Query slow as Function

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andreas Kretschmer <akretschmer(at)spamfence(dot)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(at)stevehorn(dot)cc
http://twitter.com/stevehorn
740-503-2300