Re: SQL code runs slower as a stored function

Thanks Stephen, that makes a lot of sense based on some research I was doing
yesterday.

I'm still a bit green to understand the inner workings of the planner and
all the stuff coming out of EXPLAIN, but I'm definitely trying to keep it
all in mind as I progress at this stuff.

I guess to really get down to the issue, I'm curious if what I'm doing is
considered 'standard procedure' to others-- i.e. using funny workarounds
like building the query in a text var and executing it with plpgsql's RETURN
QUERY EXECUTE command. Are there other schools of thought on how to
approach something like this? Could it be more of a sign that my design is
flawed? Something in me just feels like there should be a better way to
approach this. Otherwise I feel like I'm just blaming postgres for the
problem, which I'm not so sure I want to do.

Also re: Raymond's request, I tried humoring myself with the EXPLAIN output,
and I actually don't see anything useful-looking at all when I run it on a
stored function like I'm using. Is there any way to utilize EXPLAIN on a
query embedded in a stored function? I could run it just fine on the raw
sql, but the raw sql wasn't what was running slow, so I'm not sure if it's
even helpful to do that.

Thanks!

On Thu, May 13, 2010 at 12:06 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> 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
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkvsI5UACgkQrzgMPqB3kiilsQCeMsPGf4uTCLV5uhjfQBdhewCr
> XHEAnA99iWd71pUCWv4A0cVPZ2NMvdp6
> =YNPE
> -----END PGP SIGNATURE-----
>
>