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(at)debis(dot)com>
> To: Eric Lee Green <eric(at)linux-hw(dot)com>
> Cc: jeff(at)remapcorp(dot)com, hackers(at)postgreSQL(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83