Re: [HACKERS] What about LIMIT in SELECT ? - Mailing list pgsql-hackers

On Tue, 13 Oct 1998, Marc G. Fournier wrote:
> On Tue, 13 Oct 1998, Eric Lee Green wrote:
> > 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
> Ookay, I'm sorry, butyou lost me here.  I haven't gotten into using
> CURSORs/FETCHs yet, since I haven't need it...but can you give an example
> of what you would want to do using a LIMIT?  I may be missing something,

Whoops! Sorry, I goofed in my post (typing  faster than my brain :-).
What I *MEANT* to say was that this superiority of cursors was not
applicable in a web environment.

> but wha is the different between using LIMIT to get X records, and
> definiing a cursor to FETCH X records?

From a logical point of view, none. From an implementation point of
view, it is a matter of speed. Declaring a cursor four times, doing a
query four times, and fetching X records four times takes more time
than just doing a query with a LIMIT clause four times (assuming your
query results in four screenfulls of records).

> Practical example of *at least* the LIMIT side would be good, so that we
> can at least see a physical example of what LIMIT can do that
> CURSORs/FETCH can't...

You can do everything with CURSORs/FETCH that you can do with LIMIT.
In a non-web environment, where you have stateful connections, a FETCH
is always going to be faster than a SELECT...LIMIT statement. (Well,
it would be if implemented correctly, but I'll leave that to others to
haggle over). However: In a CGI-type environment, cursors are a huge
performance drain because in the example above you end up doing this
huge query four times, with its results stored in the cursor four
times, and only a few values are ever fetched from the cursor before it
is destroyed by the end of the CGI script.

Whereas with the SELECT...LIMIT paradigm, the database engine does NOT
process the entire huge query, it quits processing once it reaches the
limit.  (Well, at least MySQL does so, if you happen to be using an
"ORDER BY" supported by an index). Obviously doing 1/4th the work four times
is better than doing the whole tamale four times :-}.

--
Eric Lee Green         eric@linux-hw.com     http://www.linux-hw.com/~eric
"To call Microsoft an innovator is like calling the Pope Jewish ..."
            -- James Love (Consumer Project on Technology)