Thread: for/loop performance in plpgsql ?
Hi all, i've a doubt about how FOR/LOOP works in plpgsql. It seems to me that the SELECT query executed in that way is much slower that the same being executed interactively in psql. In particular it seems that it doesn't make use of indexes. Does it have any sense or am i wrong/missing something ? Thanks all. Ciao
On Friday 15 Nov 2002 12:31 pm, Federico wrote: > Hi all, > > i've a doubt about how FOR/LOOP works in plpgsql. > > It seems to me that the SELECT query executed in that way is much slower > that the same being executed interactively in psql. > > In particular it seems that it doesn't make use of indexes. > > Does it have any sense or am i wrong/missing something ? Well - the query might well be pre-parsed which means it wouldn't notice any updated stats. Can you provide an example of your code? -- Richard Huxton Archonet Ltd
On Fri, Nov 15, 2002 at 01:37:25PM +0000, Richard Huxton wrote:
> On Friday 15 Nov 2002 12:31 pm, Federico wrote:
> > Hi all,
> >
> > i've a doubt about how FOR/LOOP works in plpgsql.
> >
> > It seems to me that the SELECT query executed in that way is much slower
> > that the same being executed interactively in psql.
> >
> > In particular it seems that it doesn't make use of indexes.
> >
> > Does it have any sense or am i wrong/missing something ?
>
> Well - the query might well be pre-parsed which means it wouldn't notice any
> updated stats. Can you provide an example of your code?
It's nothing particular strange. It's something like :
result record;
for result in select rai, tem
from data
where (codice LIKE cod_staz and
ora > orain and
ora <= orafin) loop
-- do some calculation
end loop;
If i do the same select with pgsql it runs much faster. Is to be noticed
that the calculations it does in the loop are just "light", nothing that
should matter.
I'll just investigate about this strange behaviour.
Thanks !
Ciao !
Federico <fepede@inwind.it> writes:
>> Well - the query might well be pre-parsed which means it wouldn't notice any
>> updated stats. Can you provide an example of your code?
> It's nothing particular strange. It's something like :
> result record;
> for result in select rai, tem
> from data
> where (codice LIKE cod_staz and
> ora > orain and
> ora <= orafin) loop
Which of these names are columns of the selected tables, and which ones
are plpgsql variables?
The planner has to fall back to default selectivity estimates when it's
looking at queries involving plpgsql variables (since it can't know
their actual values in advance). I suspect your problem is related to
an inaccurate default estimate.
regards, tom lane
On Tue, Nov 19, 2002 at 12:23:56AM -0500, Tom Lane wrote: > > result record; > > > for result in select rai, tem > > from data > > where (codice LIKE cod_staz and > > ora > orain and > > ora <= orafin) loop > > Which of these names are columns of the selected tables, and which ones > are plpgsql variables? rai, tem, codice, ora are columns name cod_staz, orain, orafin are plpgsql variables > The planner has to fall back to default selectivity estimates when it's > looking at queries involving plpgsql variables (since it can't know > their actual values in advance). I suspect your problem is related to > an inaccurate default estimate. mmm... does it mean that i can't do anything about that ?
Federico /* juri */ Pedemonte <fepede@inwind.it> writes:
> On Tue, Nov 19, 2002 at 12:23:56AM -0500, Tom Lane wrote:
>> The planner has to fall back to default selectivity estimates when it's
>> looking at queries involving plpgsql variables (since it can't know
>> their actual values in advance). I suspect your problem is related to
>> an inaccurate default estimate.
> mmm... does it mean that i can't do anything about that ?
A brute-force solution is to use EXECUTE so that the query is re-planned
each time, with the planner seeing constants instead of variables
compared to the column values.
regards, tom lane