Обсуждение: LIMIT: does it cause the query to find all matching sets first?

Does using LIMIT cause query execution to stop as soon as the LIMIT
number of matches have been found, or are *all* the matching rows found
first and then the first LIMIT number are returned?

Of course this would be for a query without and ORDER BY clause.

Jc

On Mon, Sep 30, 2002 at 23:15:43 +0900,
  Jean-Christian Imbeault <jc@mega-bucks.co.jp> wrote:
> Does using LIMIT cause query execution to stop as soon as the LIMIT
> number of matches have been found, or are *all* the matching rows found
> first and then the first LIMIT number are returned?

Limit can speed things up. Even with an ordered by clause it can speed things
up (if there is an appropiate index). This can be used to quickly find
the maximum or minimum value of an indexed column.

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> Does using LIMIT cause query execution to stop as soon as the LIMIT
> number of matches have been found, or are *all* the matching rows found
> first and then the first LIMIT number are returned?

The current implementation actually stops after fetching, or at least
trying to fetch, one more row than the LIMIT+OFFSET sum.  The extra row
is for internal convenience (keeps the direction-reversal logic in
nodeLimit.c simple).  Some people have complained about that, but no
one's gotten annoyed enough to rewrite nodeLimit to avoid it.

            regards, tom lane

Jean-Christian Imbeault wrote:
> Does using LIMIT cause query execution to stop as soon as the LIMIT
> number of matches have been found, or are *all* the matching rows found
> first and then the first LIMIT number are returned?
>
> Of course this would be for a query without and ORDER BY clause.

A query without ORDER BY is possible, and it will stop after it hits the
limit.  Of course, without ORDER BY there is no way to know which rows
were returned.

Second, if you do use ORDER BY, LIMIT can sometimes use an index and not
have to execute the entire query, so yes, that optimization is in there.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073