Postgres Pro
Downloads
Home   >   mailing lists

Thread: LIMIT: does it cause the query to find all matching sets first?

LIMIT: does it cause the query to find all matching sets first?

From
Jean-Christian Imbeault
Date:
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

Re: LIMIT: does it cause the query to find all matching sets first?

From
Bruno Wolff III
Date:
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.

Re: LIMIT: does it cause the query to find all matching sets first?

From
Tom Lane
Date:
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

Re: LIMIT: does it cause the query to find all matching sets

From
Bruce Momjian
Date:
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