LIMIT clause optimization

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

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

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

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

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.

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/