Re: SQL code runs slower as a stored function

S G,

* S G (sgennaria2(at)gmail(dot)com) wrote:
> Can anyone lend a guess as to what I'm running into here, or do I need to
> provide more specifics to recreate the issue? It's repeatable, but it's a
> fair bit of data for me to just post in here as-is. I've already discovered
> a few creative workarounds (e.g. plpgsql: return query execute ...) that
> make it run faster again, but at this point, I'm really more interested in
> finding out what would make sql code run so much slower as a stored function
> in the first place.

The planner doesn't know what values those variables can take when
you're passing them into a function. Therefore, it tries to come up
with a plan which will work in the 'general' case, and then it will
store that plan and reuse it. When there are static values, it can
construct a better plan. If you're using partitioning on the table,
that can mean the difference between a plan that has to scan all parts
of the table and a plan that only has to scan the one part of the table
that matches the constraint. Using 'execute' will cause the planner to
re-plan the query every time using the static values that you've put
into the query.

Thanks,

Stephen