Thread: LIMIT clause optimization

LIMIT clause optimization

From
Felipe Schnack
Date:
  I was wondering... (a newbie starting to understand how to optimize
queries)
  In which step of query execution the LIMIT clause is executed? I mean
I have a query that would return, let's say, 6 rows, but I add a "LIMIT
1" in the end of it.
  I would speed up things because I would have less data fetching from
the database, right?
  But let's go to a extreme case. I just want to check if a query
returns data at all, so I did the following query:
  select 1 from <tablename>
  Seems fair, right? And what would be the difference in query
optimization if I did the following (i know i'm doing strange stuff):
  select 1 from <tablename> limit 1

--

Felipe Schnack
Analista de Sistemas
felipes@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone/Fax.: (51)32303341


Re: LIMIT clause optimization

From
Holger Klawitter
Date:
Am Dienstag, 7. Januar 2003 12:47 schrieb Felipe Schnack:
>   I was wondering... (a newbie starting to understand how to optimize
> queries)
>   In which step of query execution the LIMIT clause is executed? I mean
> I have a query that would return, let's say, 6 rows, but I add a "LIMIT
> 1" in the end of it.
>   I would speed up things because I would have less data fetching from
> the database, right?

Limit kicks in quite late as it has to be done after sorting. And it is not
recommended to use LIMIT without ORDER BY.

With kind regards / mit freundlichem Gruß
    Holger Klawitter
--
Holger Klawitter                          http://www.klawitter.de
lists@klawitter.de

Re: LIMIT clause optimization

From
Felipe Schnack
Date:
  Why? I don't understand. If I create a query and want just the first
row from it, wouldn't speed up things a lot just adding "LIMIT 1" in the
end of the query?

On Tue, 2003-01-07 at 10:44, Holger Klawitter wrote:
> Am Dienstag, 7. Januar 2003 12:47 schrieb Felipe Schnack:
> >   I was wondering... (a newbie starting to understand how to optimize
> > queries)
> >   In which step of query execution the LIMIT clause is executed? I mean
> > I have a query that would return, let's say, 6 rows, but I add a "LIMIT
> > 1" in the end of it.
> >   I would speed up things because I would have less data fetching from
> > the database, right?
>
> Limit kicks in quite late as it has to be done after sorting. And it is not
> recommended to use LIMIT without ORDER BY.
>
> With kind regards / mit freundlichem Gruß
>     Holger Klawitter
> --
> Holger Klawitter                          http://www.klawitter.de
> lists@klawitter.de
--

Felipe Schnack
Analista de Sistemas
felipes@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone/Fax.: (51)32303341


Re: LIMIT clause optimization

From
Bruno Wolff III
Date:
On Tue, Jan 07, 2003 at 10:44:15 -0200,
  Felipe Schnack <felipes@ritterdosreis.br> wrote:
>   Why? I don't understand. If I create a query and want just the first
> row from it, wouldn't speed up things a lot just adding "LIMIT 1" in the
> end of the query?

That depends on the query. For some queries you will only see a small
speed up. It is also possible for limit to effect the optimization results
in a way that ends up slowing things down (though that should be rare)
because an inferior plan ends up being chosen.

Re: LIMIT clause optimization

From
Date:
Further, I think if your query has an order by clause then the whole query
is executed, sorted, then all but the limit'd rows are truncated.  Hence
there is no performance improvement.

A very VERY smart database engine could perhaps in some cases use an index
to determine in advance the sort and get the rows in the correct order, and
hence stop when the limit was reached.  But that would be a rare case at
best, and I doubt anyone has gone to the brain damage of implementing such
complexity considering the very limited payback.

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruno
> Wolff III
> Sent: Tuesday, January 07, 2003 8:17 AM
> To: Felipe Schnack
> Cc: Holger Klawitter; pgsql-general
> Subject: Re: [GENERAL] LIMIT clause optimization
>
>
> On Tue, Jan 07, 2003 at 10:44:15 -0200,
>   Felipe Schnack <felipes@ritterdosreis.br> wrote:
> >   Why? I don't understand. If I create a query and want
> just the first
> > row from it, wouldn't speed up things a lot just adding
> "LIMIT 1" in the
> > end of the query?
>
> That depends on the query. For some queries you will only see a small
> speed up. It is also possible for limit to effect the
> optimization results
> in a way that ends up slowing things down (though that should be rare)
> because an inferior plan ends up being chosen.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>


Re: LIMIT clause optimization

From
Felipe Schnack
Date:
  So, generally selecting all rows from a table an fetching only the
first one is probably faster than limiting the query to its first row?

On Tue, 2003-01-07 at 11:14, terry@ashtonwoodshomes.com wrote:
> Further, I think if your query has an order by clause then the whole query
> is executed, sorted, then all but the limit'd rows are truncated.  Hence
> there is no performance improvement.
>
> A very VERY smart database engine could perhaps in some cases use an index
> to determine in advance the sort and get the rows in the correct order, and
> hence stop when the limit was reached.  But that would be a rare case at
> best, and I doubt anyone has gone to the brain damage of implementing such
> complexity considering the very limited payback.
>
> Terry Fielder
> Manager Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> terry@greatgulfhomes.com
>
>
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruno
> > Wolff III
> > Sent: Tuesday, January 07, 2003 8:17 AM
> > To: Felipe Schnack
> > Cc: Holger Klawitter; pgsql-general
> > Subject: Re: [GENERAL] LIMIT clause optimization
> >
> >
> > On Tue, Jan 07, 2003 at 10:44:15 -0200,
> >   Felipe Schnack <felipes@ritterdosreis.br> wrote:
> > >   Why? I don't understand. If I create a query and want
> > just the first
> > > row from it, wouldn't speed up things a lot just adding
> > "LIMIT 1" in the
> > > end of the query?
> >
> > That depends on the query. For some queries you will only see a small
> > speed up. It is also possible for limit to effect the
> > optimization results
> > in a way that ends up slowing things down (though that should be rare)
> > because an inferior plan ends up being chosen.
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--

Felipe Schnack
Analista de Sistemas
felipes@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone/Fax.: (51)32303341


Re: LIMIT clause optimization

From
Martijn van Oosterhout
Date:
On Tue, Jan 07, 2003 at 11:14:46AM -0200, Felipe Schnack wrote:
>   So, generally selecting all rows from a table an fetching only the
> first one is probably faster than limiting the query to its first row?
>
> On Tue, 2003-01-07 at 11:14, terry@ashtonwoodshomes.com wrote:
> > Further, I think if your query has an order by clause then the whole query
> > is executed, sorted, then all but the limit'd rows are truncated.  Hence
> > there is no performance improvement.
> >
> > A very VERY smart database engine could perhaps in some cases use an index
> > to determine in advance the sort and get the rows in the correct order, and
> > hence stop when the limit was reached.  But that would be a rare case at
> > best, and I doubt anyone has gone to the brain damage of implementing such
> > complexity considering the very limited payback.

Well, I guess that makes postgresql a very VERY smart database engine. If
you give no limit, postgresql will base it's planning on retreiving all
rows. If you specify a LIMIT, it will plan on only calculating those rows.

Play with EXPLAIN and LIMIT and very large tables with indexes. It's fairly
easy to demonstrate.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.

Attachment