Hi,

Are there any best practices for optimizing triggers,
and, I suppose, stored procedures as well? I am now
starting on optimization and before I begin am
hoping to avoid re-inventing the wheel.


The problems I see are:

1) There is no way to profile where a problem lies.
When there are large and/or nested triggers there
could be a 'bad query' anywhere. Finding it seems
difficult.

2) The NEW and OLD tables used by triggers don't exist
outside of a trigger environment, yet EXPLAIN returns
statement results -- and which is basically illegal inside
triggers.


The solutions I see are to use:

SET client_min_messages DEBUG1;
SET debug_print_plan TRUE;

and maybe
SET log_executer_stats TRUE;

Is this the best approach? Any tricks for sorting through the
resultant output?

It'd be nice to have some hints about this in the User's Guide.

Thanks.

Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On 04/01/2005 10:19:55 AM, Karl O. Pinc wrote:
> Hi,
>
> Are there any best practices for optimizing triggers,
> and, I suppose, stored procedures as well?
>
> The solutions I see are to use:
>
> SET client_min_messages DEBUG1;
> SET debug_print_plan TRUE;
>
> and maybe
> SET log_executer_stats TRUE;

Ok, this strategy only works on a per-schema basis
as the way to get it to work is to set the
debug_print_plan before doing the
CREATE FUNCTION for the functions
you want to monitor. (Which might be hard
to do in a production environment.)
It's not something that happens on a per-session
basis.

(So far I've only tried this with regular stored
procedures, rather than triggers.)

(FYI: The above SET statements are missing TO as in
SET debug_print_plan TO TRUE;
)

Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein





---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly