Re: Slow queries in PL/PGSQL function

On Thursday 19 February 2004 23:00, Jim Crate wrote:
> I have a couple queries in a PL/PGSQL function which execute very slowly
> (around one minute each) which execute in .5 second when not executed from
> within the function. Is there any way to determine why this is happening?
> I couldn't figure out how to run EXPLAIN ANALYZE from within the function.

You can't - hmm, looking here: http://developer.postgresql.org/todo.php
I can't even see a TODO. I'll suggest it on the hackers list.

> explain analyze SELECT DISTINCT i_ip
> FROM x_rbl_ips
> LEFT JOIN filter_ips ON x_rbl_ips.i_ip = filter_ips.i_filter_ip
> WHERE x_rbl_ips.dts_last_modified > '2004-02-18 22:24:15.901689+00'
> AND filter_ips.i_filter_ip IS NOT NULL
> AND (i_filter_type_flags & X'02000000'::integer) <> X'02000000'::integer

I'm guessing that the values in your query are variables/parameters in the
plpgsql function? The problem is that the plan is compiled when the function
is first run, so it doesn't know what values you will use. You might tend to
use values that make sense to index, but it can't tell.

Try rephrasing this query as an EXECUTE ''query-string'' and see if that makes
the problem go away.
--
Richard Huxton
Archonet Ltd