Обсуждение: LIMIT Question
When one uses LIMIT, as in LIMIT 1, is the entire query executed on the server side, but only one record returned? -------------------------------------------------------------------------------------------------------- PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) Thanks... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 terry@turbocorp.com www.turbocorp.com
On Thu, Feb 28, 2008 at 3:20 PM, Terry Lee Tucker <terry@chosen-ones.org> wrote: > When one uses LIMIT, as in LIMIT 1, is the entire query executed on the server > side, but only one record returned? > > -------------------------------------------------------------------------------------------------------- > PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 > 20060404 (Red Hat 3.4.6-9) Sometimes yes, sometimes no. Depends on how complex the query is and whether or not pgsql's query planner can see a shortcut or not. It's more likely that a later version will have the optimizations needed to do that than an older version like 7.4 I'd think. But I'd ask someone more expert on the planner like Tom to be sure.
In my opinion (without looking at the code), if you have a grouping-function or ORDER BY or GROUP BY clause, then yes, the whole query has to be executed to show the first row of the result-set. But if the query doesn't have any of these clauses, then the DB has the ability to send back the first row from the result as soon as it processes it (i.e after WHERE clause processing), and stop the query execution there.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune *
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
Best regards,
On Fri, Feb 29, 2008 at 5:02 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, Feb 28, 2008 at 3:20 PM, Terry Lee Tucker <terry@chosen-ones.org> wrote:Sometimes yes, sometimes no. Depends on how complex the query is and
> When one uses LIMIT, as in LIMIT 1, is the entire query executed on the server
> side, but only one record returned?
>
> --------------------------------------------------------------------------------------------------------
> PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
> 20060404 (Red Hat 3.4.6-9)
whether or not pgsql's query planner can see a shortcut or not. It's
more likely that a later version will have the optimizations needed to
do that than an older version like 7.4 I'd think. But I'd ask someone
more expert on the planner like Tom to be sure.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune *
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
On Fri, Feb 29, 2008 at 02:53:05PM +0530, Gurjeet Singh wrote: > In my opinion (without looking at the code), if you have a grouping-function > or ORDER BY or GROUP BY clause, then yes, the whole query has to be executed > to show the first row of the result-set. But if the query doesn't have any > of these clauses, then the DB has the ability to send back the first row > from the result as soon as it processes it (i.e after WHERE clause > processing), and stop the query execution there. Except if you have an index on the column you're ordering by. Then the server can really return the first row quickly. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Вложения
On Fri, Feb 29, 2008 at 3:55 PM, Martijn van Oosterhout <kleptog@svana.org> wrote:
Quickly for sure... but I don't think 'without processing all the rows that qualify'. I think it will still process all the rows and return just one.
Best regards,On Fri, Feb 29, 2008 at 02:53:05PM +0530, Gurjeet Singh wrote:Except if you have an index on the column you're ordering by. Then the
> In my opinion (without looking at the code), if you have a grouping-function
> or ORDER BY or GROUP BY clause, then yes, the whole query has to be executed
> to show the first row of the result-set. But if the query doesn't have any
> of these clauses, then the DB has the ability to send back the first row
> from the result as soon as it processes it (i.e after WHERE clause
> processing), and stop the query execution there.
server can really return the first row quickly.
Quickly for sure... but I don't think 'without processing all the rows that qualify'. I think it will still process all the rows and return just one.
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune *
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
On Fri, Feb 29, 2008 at 05:28:29PM +0530, Gurjeet Singh wrote: > On Fri, Feb 29, 2008 at 3:55 PM, Martijn van Oosterhout <kleptog@svana.org> wrote: > > Except if you have an index on the column you're ordering by. Then the > > server can really return the first row quickly. > > Quickly for sure... but I don't think 'without processing all the rows that > qualify'. Postgres will always try to do the smallest amount of work possible. Putting a LIMIT in will cause the planner to pick a plan that returns an appropriate number of rows quickly, sometimes it can be a very different plan. > I think it will still process all the rows and return just one. How do you explain that when you run: SELECT 1/v FROM (VALUES (1),(0)) c(v); Without a limit you get a "division by zero" exception, and when you have a limit of one row you get a result back. Sam
On Fri, Feb 29, 2008 at 7:20 PM, Sam Mason <sam@samason.me.uk> wrote:
You are correct, and repeating what I said in the first mail. Your query does not involve an ORDER BY (or other clauses), hence the first row that the executor encounters, satisfies what the query asked for. But if it had an ORDER BY you will again get the division ERROR.
Above I was referring to the case where the planner chooses an Index access.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune *
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
On Fri, Feb 29, 2008 at 05:28:29PM +0530, Gurjeet Singh wrote:
> On Fri, Feb 29, 2008 at 3:55 PM, Martijn van Oosterhout <kleptog@svana.org> wrote:> > Except if you have an index on the column you're ordering by. Then thePostgres will always try to do the smallest amount of work possible.
> > server can really return the first row quickly.
>
> Quickly for sure... but I don't think 'without processing all the rows that
> qualify'.
Putting a LIMIT in will cause the planner to pick a plan that returns an
appropriate number of rows quickly, sometimes it can be a very different
plan.How do you explain that when you run:
> I think it will still process all the rows and return just one.
SELECT 1/v FROM (VALUES (1),(0)) c(v);
Without a limit you get a "division by zero" exception, and when you
have a limit of one row you get a result back.
You are correct, and repeating what I said in the first mail. Your query does not involve an ORDER BY (or other clauses), hence the first row that the executor encounters, satisfies what the query asked for. But if it had an ORDER BY you will again get the division ERROR.
Above I was referring to the case where the planner chooses an Index access.
Best regards,
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune *
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device