Re: 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