Re: SQL code runs slower as a stored function - Mailing list pgsql-general

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@snowman.net> wrote:

S G,

* S G (sgennaria2@gmail.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-----