Re: [HACKERS] What about LIMIT in SELECT ?

On Wed, 14 Oct 1998, Jan Wieck wrote:

> Date: Wed, 14 Oct 1998 13:09:21 +0200 (MET DST)
> From: Jan Wieck <jwieck@debis.com>
> To: Eric Lee Green <eric@linux-hw.com>
> Cc: jeff@remapcorp.com, hackers@postgreSQL.org
> Subject: Re: [HACKERS] What about LIMIT in SELECT ?
>
> Eric Lee Green wrote:
> >
> > On Tue, 13 Oct 1998, Jeff Hoffmann wrote:
> > > >I agree completely, LIMIT would be VERY usefull in web based apps, which
> > > >is all I run.  It does not matter to me if it is not part of a formal
> > > >standard.  The idea is so common that it is a defacto standard.
> > >
> > > i'm not familiar with mysql and using "LIMIT" but wouldn't this same effect
> > > be achieved by declaring a cursor and fetching however many records in the
> > > cursor?  it's a very noticeable improvement when you only want the first 20
> > > out of 500 in a 200k record database, at least.
> >
> > The problem with declaring a cursor vs. the "LIMIT" clause is that the
> > "LIMIT" clause, if used properly by the database engine (along with the
> > database engine using indexes in "ORDER BY" clauses) allows the database
> > engine to short-circuit the tail end of the query. That is, if you have 25
> > names and the last one ends with BEAVIS, the database engine doesn't have
> > to go through the BUTTHEADS and KENNYs and etc.
> >
> > Theoretically a cursor is superior to the "LIMIT" clause because you're
> > eventually going to want the B's and K's and etc. anyhow -- but only in a
> > stateful enviornment. In the stateless web environment, a cursor is
> > useless because the connection can close at any time even when you're
> > using "persistent" connections (and of course when the connection closes
> > the cursor closes).
>
>     I'm  missing something. Well it's right that in the stateless
>     web environment a cursor has to be declared  and  closed  for
>     any  single  CGI  call.  But even if you have a LIMIT clause,
>     your CGI must know with which key to start.
>
      This is not a problem for CGI-script to know which key to start.
      Without LIMIT every CGI call backend will do *FULL* selection
      and cursor helps just in fetching a definite number of rows,
      in principle I can do this with CGI-script. Also, cursor
      returns data back in  ASCII  format (man l declare) and this requires
      additional job for backend to convert data from intrinsic (binary)
      format. Right implementation of LIMIT offset,number_of_rows could be
      a great win and make postgres superior free database engine for
      Web applications. Many colleagues of mine used mysql instead of
      postgres just because of lacking LIMIT. Tatsuo posted a patch
      for set query_limit to 'num', I just tested it and seems it
      works fine. Now, we need only possibility to specify offset,
      say
         set query_limit to 'offset,num'
      ( Tatsuo, How difficult to do this ?)
      and LIMIT problem will ne gone.

      I'm wonder how many useful patches could be hidden from people  :-),

    Regards,

        Oleg

PS.

    Tatsuo, do you have patch for 6.3.2 ?
        I can't wait for 6.4 :-)
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83