On Sat, Feb 18, 2012 at 8:50 AM, Steve Horn st...@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,
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
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
Steve Horn st...@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
Andreas Kretschmer akretsch...@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
On 02/18/2012 11:37 AM, Tom Lane wrote:
Andreas Kretschmerakretsch...@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
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
Andrew Dunstan and...@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
Tom Lane t...@sss.pgh.pa.us wrote:
Andreas Kretschmer akretsch...@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
