Re: SQL code runs slower as a stored function

S G,

* S G (sgennaria2(at)gmail(dot)com) wrote:
> 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.

It's certainly not unusual when it's necessary. We do try to minimize
the times it's necessary by making the planner as smart as possible.

> 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.

I don't think needing to do this would imply that the design is flawed..

> 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.

You can try putting the explain *into* the stored procedure, and then
putting its results into a text variable and then spitting that back
out. You can also play with setting it up as a prepared statement
and then running explain/explain analyze on that; eg:

prepare x as select * from table where column1 = $1;
explain execute x('2010-01-01');

That would hopefully give you the same plan as what's happening in the
stored procedure.

Thanks,

Stephen