Re: [GENERAL] LIMIT Question
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, On Fri, Feb 29, 2008 at 5:02 AM, Scott Marlowe [EMAIL PROTECTED] wrote: On Thu, Feb 28, 2008 at 3:20 PM, Terry Lee Tucker [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad 18° 32' 57.25N, 73° 56' 25.42E - Pune * 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [GENERAL] LIMIT Question
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 [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] LIMIT Question
On Fri, Feb 29, 2008 at 3:55 PM, Martijn van Oosterhout [EMAIL PROTECTED] wrote: 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. 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, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad 18° 32' 57.25N, 73° 56' 25.42E - Pune * 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [GENERAL] LIMIT Question
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] LIMIT Question
On Fri, Feb 29, 2008 at 7:20 PM, Sam Mason [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] 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. 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, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad 18° 32' 57.25N, 73° 56' 25.42E - Pune * 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [GENERAL] LIMIT Question
On Thu, Feb 28, 2008 at 3:20 PM, Terry Lee Tucker [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
RE: [GENERAL] LIMIT QUESTION
dbtest= set QUERY_LIMIT TO 10; ERROR: parser: parse error at or near "10" dbtest= set QUERY_LIMT TO 10; ERROR: parser: parse error at or near "10" Didn't work. I guess I'll have to re-install postgresql 6.5 BTW, is this 6.5 version stable enough for production? Please comment. And also if 6.4.2 is up to production level. I'm sure it is though, haven't found any problems yet. Thanks Martin -Original Message- ·ol : José Soares [EMAIL PROTECTED] ¶æ : Chris Bitmead [EMAIL PROTECTED] CC : Martin Wong [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] ú : 1999N430ú 0:01 ¼ : Re: [GENERAL] LIMIT QUESTION Chris Bitmead ha scritto: Only PostgreSQL 6.5 Beta supports LIMIT. I've an elementary question. What's wrong with the following : dbtest= select * from testusers limit 10; ERROR: parser: parse error at or near "10" dbtest= select version(); version - PostgreSQL 6.4.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2. (1 row) the testuser table has about 1000 rows. Please comment. Thanks Use SET QUERY_LIMT TO 10; this should work in 6.4.2 __ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^ Jose'
RE: [GENERAL] LIMIT QUESTION
Sorry for the previous posting. The following worked. BTW, this affects just this database or throughout the entire postgresql server? And, how does one reset this variable to max? Thanks martin -Original Message- ·ol : Margarita Barvinok [EMAIL PROTECTED] ¶æ : [EMAIL PROTECTED] [EMAIL PROTECTED] ú : 1999N430ú 0:01 ¼ : Re: [GENERAL] LIMIT QUESTION This works in 6.4.2: set QUERY_LIMIT TO '10'; or set QUERY_LIMIT = '10'; On Thu, 29 Apr 1999, [iso-8859-1] José Soares wrote: Use SET QUERY_LIMT TO 10; this should work in 6.4.2 __ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^ Jose'
RE: [GENERAL] LIMIT QUESTION
This is from the Tutorial for version 6.4 of PostgreSQL.
SET - Set run-time parameters for session
SET variable { TO | = } { 'value' | DEFAULT }
SET TIME ZONE { 'timezone' | LOCAL };
QUERY_LIMIT
Sets the number of rows returned by a query.
Value
Maximum number of rows to return for a query. The default is
to allow an unlimited number of rows.
#
Sets the maximum number of rows returned by a query to #.
DEFAULT
Sets the maximum number of rows returned by a query to be
unlimited.
By default, there is no limit to the number of rows returned by a
query.
On Thu, 29 Apr 1999, Martin Wong wrote:
Sorry for the previous posting. The following worked.
BTW, this affects just this database or throughout the entire postgresql
server?
And, how does one reset this variable to max?
Thanks
martin
This works in 6.4.2:
set QUERY_LIMIT TO '10'; or
set QUERY_LIMIT = '10';
Re: [GENERAL] LIMIT QUESTION
Martin Wong ha scritto: Sorry for the previous posting. The following worked. BTW, this affects just this database or throughout the entire postgresql server? Only current_session; And, how does one reset this variable to max? RESET QUERY_LIMIT; __ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^ Jose'
RE: [GENERAL] LIMIT QUESTION
v6.5 is definitely not stable enough for production...purely a "use at own
risk" at this time...
On Thu, 29 Apr 1999, Martin Wong wrote:
dbtest= set QUERY_LIMIT TO 10;
ERROR: parser: parse error at or near "10"
dbtest= set QUERY_LIMT TO 10;
ERROR: parser: parse error at or near "10"
Didn't work. I guess I'll have to re-install postgresql 6.5
BTW, is this 6.5 version stable enough for production?
Please comment.
And also if 6.4.2 is up to production level. I'm sure it is though, haven't
found any problems yet.
Thanks
Martin
-Original Message-
·ol : José Soares [EMAIL PROTECTED]
¶æ : Chris Bitmead [EMAIL PROTECTED]
CC : Martin Wong [EMAIL PROTECTED]; [EMAIL PROTECTED]
[EMAIL PROTECTED]
ú : 1999N430ú 0:01
¼ : Re: [GENERAL] LIMIT QUESTION
Chris Bitmead ha scritto:
Only PostgreSQL 6.5 Beta supports LIMIT.
I've an elementary question. What's wrong with the following :
dbtest= select * from testusers limit 10;
ERROR: parser: parse error at or near "10"
dbtest= select version();
version
-
PostgreSQL 6.4.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2.
(1 row)
the testuser table has about 1000 rows.
Please comment.
Thanks
Use SET QUERY_LIMT TO 10;
this should work in 6.4.2
__
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^
Jose'
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
RE: [GENERAL] LIMIT QUESTION
I think "limit" is new with version 6.5. -Original Message- From: Martin Wong [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, April 28, 1999 8:07 PM To: [EMAIL PROTECTED] Subject:[GENERAL] LIMIT QUESTION Hi, I've an elementary question. What's wrong with the following : dbtest= select * from testusers limit 10; ERROR: parser: parse error at or near "10" dbtest= select version(); version - PostgreSQL 6.4.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2. (1 row) the testuser table has about 1000 rows. Please comment. Thanks
Re: [GENERAL] LIMIT QUESTION
Only PostgreSQL 6.5 Beta supports LIMIT. I've an elementary question. What's wrong with the following : dbtest= select * from testusers limit 10; ERROR: parser: parse error at or near "10" dbtest= select version(); version - PostgreSQL 6.4.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2. (1 row) the testuser table has about 1000 rows. Please comment. Thanks
