Re: [GENERAL] Tuning queries inside a function
Mike Nolan wrote: Maybe you could return a refcursor pointing to the EXPLAIN ANALYZE of the query inside the function. The raw materials exist to do this: if you know which elements of a query will be replaced by plpgsql variables, you can duplicate the results via PREPARE foo(...) AS ... EXPLAIN EXECUTE foo(...) Certainly there is a lot more that we can and must do about making it easier to debug and tune plpgsql functions. But you can fix 'em with a little determination even now... If I know which elements of a query will be replaced by variables, I can enter the query in psql, which I've done. (I can always output the variables to the log from inside the function.) Be aware that if you're pasting values in the place of the variables then PG can come up with a different plan. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Tuning queries inside a function
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Mike Nolan wrote: >> That part I get, but I cannot seem to get an 'explain select' to return >> the explain output inside a function. > Oh interesting. Hmmm. Alvaro can you think of a way to execute the > result into a variable and return it as a notice? I think it's done already, at least if you are using a recent release. I note the following relevant items in the CVS log: 2005-04-05 14:05 tgl * doc/src/sgml/plpgsql.sgml, src/pl/plpgsql/src/gram.y: Adjust grammar for plpgsql's OPEN command so that a cursor can be OPENed on non-SELECT commands such as EXPLAIN or SHOW (anything that returns tuples is allowed). This flexibility already existed for bound cursors, but OPEN was artificially restricting what it would take. Per a gripe some months back. 2005-02-10 15:36 tgl * src/backend/: executor/spi.c, tcop/pquery.c (REL8_0_STABLE), executor/spi.c, tcop/pquery.c: Fix SPI cursor support to allow scanning the results of utility commands that return tuples (such as EXPLAIN). Per gripe from Michael Fuhr. Side effect: fix an old bug that unintentionally disabled backward scans for all SPI-created cursors. (The latter is in 8.0.2 and up, the former only in CVS tip.) This is relevant to plpgsql because both "FOR ... IN query" and plpgsql cursors depend on SPI cursors. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tuning queries inside a function
Mike Nolan wrote:
Mike Nolan wrote:
select * from foo('bar','debug')
But how do I do that inside a pl/pgsql function? 'select into' doesn't
seem to work properly.
You would have to code it. For example:
IF $2 = ''debug'' THEN:
That part I get, but I cannot seem to get an 'explain select' to return
the explain output inside a function.
Oh interesting. Hmmm. Alvaro can you think of a way to execute the
result into a variable and return it as a notice?
Sincerely,
Joshua D. Drake
--
Mike Nolan
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Tuning queries inside a function
> Mike Nolan wrote:
> >>select * from foo('bar','debug')
> >
> >
> > But how do I do that inside a pl/pgsql function? 'select into' doesn't
> > seem to work properly.
>
>
> You would have to code it. For example:
>
> IF $2 = ''debug'' THEN:
That part I get, but I cannot seem to get an 'explain select' to return
the explain output inside a function.
--
Mike Nolan
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Tuning queries inside a function
Mike Nolan wrote:
select * from foo('bar','debug')
But how do I do that inside a pl/pgsql function? 'select into' doesn't
seem to work properly.
You would have to code it. For example:
IF $2 = ''debug'' THEN:
I would have to check be able to include a timestamp at the beginning
of each notice.
You can do that from the config file, but it only gives the time to the
nearest second, which may not be a fine enough time interval.
--
Mike Nolan
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Tuning queries inside a function
> > Maybe you could return a refcursor pointing to the EXPLAIN ANALYZE of > > the query inside the function. > > The raw materials exist to do this: if you know which elements of a > query will be replaced by plpgsql variables, you can duplicate the > results via > > PREPARE foo(...) AS ... > EXPLAIN EXECUTE foo(...) > > Certainly there is a lot more that we can and must do about making > it easier to debug and tune plpgsql functions. But you can fix 'em > with a little determination even now... If I know which elements of a query will be replaced by variables, I can enter the query in psql, which I've done. (I can always output the variables to the log from inside the function.) But what I'd rather have is some way of getting and logging the 'explain' output for a series of function calls, which I can't seem to achieve inside a function. I think in the long run I may have to redefine the database to cut back on the number of queries inside the function. -- Mike Nolan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tuning queries inside a function
Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Fri, Apr 29, 2005 at 12:32:26PM -0500, Mike Nolan wrote: >> Any ideas on how to tune a user function? > Maybe you could return a refcursor pointing to the EXPLAIN ANALYZE of > the query inside the function. The raw materials exist to do this: if you know which elements of a query will be replaced by plpgsql variables, you can duplicate the results via PREPARE foo(...) AS ... EXPLAIN EXECUTE foo(...) Certainly there is a lot more that we can and must do about making it easier to debug and tune plpgsql functions. But you can fix 'em with a little determination even now... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Tuning queries inside a function
We have functions with upwards of 800 lines and we simply pull the queries out and stick them in the PG Lighting Admin or PG Admin III query editor. We then substitue any vars etc with real values. Works ok. > What's the best way to tune the queries inside a user function? > > I have a fairly complicated function that may make as many as 10 queries > on several tables, some of which involve multiple joins. > > Further, in the PHP program that needs this function, it can be called > as many as 400,000 times. The last time I ran the program in production > mode, it took 35 hours to complete! Since then I've done some reworking > to avoid the function calls about half of the time, that cut the run time > down to about 16 hours, but that's still longer than I'd like. > > I need to find out if the function can be tuned further, but 'explain' > doesn't really tell much about what's happening inside the function. > > Any ideas on how to tune a user function? > -- > Mike Nolan > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Tuning queries inside a function
> select * from foo('bar','debug')
But how do I do that inside a pl/pgsql function? 'select into' doesn't
seem to work properly.
> I would have to check be able to include a timestamp at the beginning
> of each notice.
You can do that from the config file, but it only gives the time to the
nearest second, which may not be a fine enough time interval.
--
Mike Nolan
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Tuning queries inside a function
Huh, sorry, this doesn't work ... we don't allow DECLARE for EXPLAIN.
It'd be neat though ...
What about having a debug mode for the function. E.g:
selet * from foo('bar','debug')
When you run with debug it actually runs the function but outputs
notices that are the explain anaylze of each function? You might
I would have to check be able to include a timestamp at the beginning
of each notice.
This will allow you to figure out where your slow downs are that are
not query related?
Sincerely,
Joshua D. Drake
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Tuning queries inside a function
On Fri, Apr 29, 2005 at 02:38:30PM -0400, Alvaro Herrera wrote: > On Fri, Apr 29, 2005 at 12:32:26PM -0500, Mike Nolan wrote: > > > I need to find out if the function can be tuned further, but 'explain' > > doesn't really tell much about what's happening inside the function. > > > > Any ideas on how to tune a user function? > > Maybe you could return a refcursor pointing to the EXPLAIN ANALYZE of > the query inside the function. Huh, sorry, this doesn't work ... we don't allow DECLARE for EXPLAIN. It'd be neat though ... -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Aprender sin pensar es inútil; pensar sin aprender, peligroso" (Confucio) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Tuning queries inside a function
On Fri, Apr 29, 2005 at 12:32:26PM -0500, Mike Nolan wrote: > I need to find out if the function can be tuned further, but 'explain' > doesn't really tell much about what's happening inside the function. > > Any ideas on how to tune a user function? Maybe you could return a refcursor pointing to the EXPLAIN ANALYZE of the query inside the function. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "God is real, unless declared as int" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
