Re: SQL Functions and plan time

On Tuesday 08 Jul 2003 4:33 pm, Tom Lane wrote:
> Richard Huxton <dev(at)archonet(dot)com> writes:
> > 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
>
> They're not the same query from the planner's viewpoint: one has
> constants from which it can infer the number of rows to be fetched,
> the other has only parameter symbols.
>
> My guess is that the parameterized query is getting stuck with a seqscan
> plan, but it's hard to be sure without more data.

That was my guess, but I couldn't think of a way to get an EXPLAIN out of the
function. I turned the plan debugging on for both but I'll need some free
time to format it up and figure out what's happening.

Can I ask why, since the plan is constructed at query-time the parameters
aren't substitued *before* planning?

--
Richard Huxton