Search Postgresql Archives

Re: SQL code runs slower as a stored function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



S G,

* S G (sgennaria2@xxxxxxxxx) 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

Attachment: signature.asc
Description: Digital signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux