Re: for/loop performance in plpgsql ?

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 !