Thread: LIMIT clause optimization
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
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
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
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.
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) >
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 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.