Re: SQL Functions and plan time

On Tuesday 08 Jul 2003 6:39 am, Tom Lane wrote:
> Richard Huxton <dev(at)archonet(dot)com> writes:
> > Does the plan for the body get built at creation or execution time? I
> > could have sworn it was execution time, but timings on one function
> > suggest creation?
>
> It's planned once per query. There's been some talk of building a
> cross-query plan cache for SQL functions, like plpgsql has, but
> nothing done yet.

So why am I getting substantially different times for identical queries
(except for parameter substitution)

[41] LOG: query: SELECT zzz2('2003-07-07 17:00:00+01','2003-07-07
17:20:00+01');
[42-1] LOG: query:
[42-2] DELETE FROM stats_telcon WHERE st_hour >= $1 AND st_hour < $2;
...
[43] LOG: duration: 7.524765 sec

[44] LOG: query: DELETE FROM stats_telcon WHERE st_hour>='2003-07-07
17:00:00+01' AND st_hour<'2003-07-07 17:20:00+01';
[45] LOG: duration: 0.032860 sec

[46-1] LOG: query: INSERT INTO stats_telcon
...
[47] LOG: duration: 1.810267 sec

I don't think I'm getting caching issues here - these aren't the first run.

--
Richard Huxton