From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SQL Functions and plan time |
Date: | 2003-07-08 17:21:42 |
Message-ID: | 200307081821.42685.dev@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Travers | 2003-07-08 17:25:02 | Re: Benchmarking |
Previous Message | Alvaro Herrera | 2003-07-08 17:15:45 | Re: Stored Procedure Assistance |