Re: LIMIT Optimization

On Fri, 25 Jan 2002, Bruce Momjian wrote:

> alexandre paes :: aldeia digital wrote:
> > Hi,
> >
> > DB2, Sql Server and Oracle have a smart optimization sql-clause (DB2
> > example):
> >
> > SELECT * FROM <table> WHERE <cond> ORDER BY <order> OPTMIZATION FOR n ROWS
> >
> > The [OPTMIZATION FOR] clause turns the query fast by optimize the first "n"
> > rows.
> > If the query returns more than "n" rows, the query is slowest if compared
> > with a normal
> > query, but it does not have the limitation of PostgreSQL's LIMIT clause.
> >
> > I think that clause performs the search twice: one for optimize and other if
> > the # of rows
> > is great then "n".
> >
> > It's possible to include this in future releases of PostgreSQL ????
>
> So it forces our LIMIT optimization, without limiting the number of rows
> returned. That seems to be of questionable value. The only value I can
> see for it is for CURSOR queries but I don't think we can start
> returning rows from even a cursor until the entire query is done
> executing.

if I'm not mistaken, it's called partial sorting, when you stop
sorting process after getting desired number of rows specified by LIMIT clause.
it's extremely friendly for web applications, because 90% of users
just read the first page of results. We already discussed this feature
sometime during 7.1 dev and even made very crude patch. In our tests we
got performance win of factor 5-6 ( getting first 100 row from 1mln ).
We hope sometime we'll return to this.

>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83