Re: [HACKERS] What about LIMIT in SELECT ?

On Tue, 13 Oct 1998, Bruce Momjian 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
> > using "persistent" connections (and of course when the connection closes
> What we could do is _if_ there is only one table(no joins), and an index
> exists that matches the ORDER BY, we could use the index to
> short-circuit the query.

This is exactly what MySQL does in this situation, except that it can use
the ORDER BY to do the short circuiting even if there is a join involved
if all of the elements of the ORDER BY belong to one table. Obviously if
I'm doing an "ORDER BY table1.foo table2.bar" that isn't going to work!
But "select table1.fsname,table1.lname,table2.receivables where
table2.receivables > 0 and table1.custnum=table2.custnum order by
(table1.lname,table1.fsname) limit 50" can be short-circuited by fiddling
with the join order -- table1.fsname table1.lname have to be the first two
things in the join order.

Whether this is feasible in PostgreSQL I have no earthly idea. This would
seem to conflict with the join optimizer.

> happier?  If there is an ORDER BY and no index, or a join, I can't
> figure out how we would short-circuit the query.

If there is an ORDER BY and no index you can't short-circuit the query.
MySQL doesn't either. Under certain circumstances (such as above) you can
short-circuit a join, but it's unclear whether it'd be easy to add such
a capability to PostgreSQL given the current structure of the query
optimizer. (And I certainly am not in a position to tackle it, at the
moment MySQL is sufficing for my project despite the fact that it is
quite limited compared to PostgreSQL, I need to get my project finished
first).

--
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)