Re: How to avoid "Seq Scans"?

On Wednesday 29 August 2007 11:20:53 Richard Huxton wrote:
> Vincenzo Romano wrote:
> > Hi all.
> >
> > In PG 8.2.4 I have a 4+M rows table like this:
> >
> > I'd need to write a stored function that should do the
> > following:
> >
> > for rec in select * from t order by f2,f2 loop
> > ...
> > end loop;
> >
> > -> Seq Scan on t (cost=0.00..85501.38 rows=4779338 width=28)
> >
> > I'd like to know a hint about a technicque to avoid the
> > sequential scan!
>
> But you're fetching all the rows - what other way would be faster?

Definitely right.

I'm trying to investigate the strange (to me) bahaviour of a couple of
stored procedure.
The outer one is in PL/PGSQL and has the above mentioned loop.
The inner one, called into the loop, is an "SQL stable strict"
function.
The outer "empty" loop takes less than 16 seconds.
The inner function takes between 10 and 50 msec when called by itself.
The inner+outer function needs more than 45 minutes just to run over
the first 10 thousands lines.

The inner function is actually a select over another table (16+M rows)
and always shows very good timing when execute by itself.
What I argue now is that something wrong happens with the query
planner when the inner function gets called by the outer one.

Is there any confirmation (and possibly workaround) for this
behaviour?

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]