diff options
Diffstat (limited to 'doc/TODO.detail/limit')
| -rw-r--r-- | doc/TODO.detail/limit | 5708 |
1 files changed, 5708 insertions, 0 deletions
diff --git a/doc/TODO.detail/limit b/doc/TODO.detail/limit new file mode 100644 index 00000000000..401d08c67a0 --- /dev/null +++ b/doc/TODO.detail/limit @@ -0,0 +1,5708 @@ +From owner-pgsql-hackers@hub.org Tue Oct 13 15:05:53 1998 +Received: from hub.org (majordom@hub.org [209.47.148.200]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id PAA09435 + for <maillist@candle.pha.pa.us>; Tue, 13 Oct 1998 15:05:50 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id OAA11700; + Tue, 13 Oct 1998 14:43:31 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Tue, 13 Oct 1998 14:41:03 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id OAA11395 + for pgsql-hackers-outgoing; Tue, 13 Oct 1998 14:41:00 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from terry1.acun.com (terry@terry1.acun.com [206.27.86.12]) + by hub.org (8.8.8/8.8.8) with ESMTP id OAA11372 + for <hackers@postgreSQL.org>; Tue, 13 Oct 1998 14:40:54 -0400 (EDT) + (envelope-from terry@terrym.com) +Received: from localhost (terry@localhost) + by terry1.acun.com (8.8.5/8.8.5) with SMTP id OAA09491 + for <hackers@postgreSQL.org>; Tue, 13 Oct 1998 14:53:22 -0400 +Date: Tue, 13 Oct 1998 14:53:22 -0400 (EDT) +From: Terry Mackintosh <terry@terrym.com> +X-Sender: terry@terry1.acun.com +Reply-To: Terry Mackintosh <terry@terrym.com> +To: PostgreSQL-development <hackers@postgreSQL.org> +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +In-Reply-To: <Pine.GSO.3.96.SK.981013211058.17758A-100000@ra> +Message-ID: <Pine.LNX.3.95.981013141634.9255C-100000@terry1.acun.com> +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=US-ASCII +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: ROr + +Hi, my 2 cents... + +I agree completely, LIMIT would be VERY usefull in web based apps, which +is all I run. It does not matter to me if it is not part of a formal +standard. The idea is so common that it is a defacto standard. + +I would not expect it for this release, but could it get put on the TODO +list for next time? I am even willing to work at an apprentise level on +this with a more expeireanced person that knows this stuff. + +A note on implimentation: +I *used to* :) work with VFP on NT's :( +And the way VFP did LIMIT, it would only return the number of rows asked +for, BUT it still did the WHOLE search! +So on a larger table, which we had (property tax database for the county), +if some one put in too vague a query, it would try to collect ALL of the +rows as the initial result set, then give you the first x rows of that. + +This did save on pushing mass amounts of data out to the browser, but it +would have been even better if it could have simply aborted the select +after having found x rows. + +Also, it did not have the concept of an offset, so one could not select +100 rows, starting 200 rows in, which would be REALLY usefull for "paging" +through data. I do not know if mySQL or any other has such a concept +either, but it would be nice. + +So a properly implemented "LIMIT" could: +1. Save pushing mass amounts of data across the web, that no one wants +any way. +2. Stop vague queries from bogging down the server. +(On very larg tables this could be critical!) +3. Enable "Paging" of data. (easyer then now (app. level)) +4. Would be a very nice feather in PostgreSQL's cap that could make it +even more attractive to those looking at all sorts of databases out there. + +Have a great day. + +On Tue, 13 Oct 1998, Oleg Bartunov wrote: + +> Hi, +> +> I took a look at mysql and was very impressed with possibility +> to limit number of rows returned from select. This is very useful +> feature for Web applications when user need to browse results of +> selection page by page. In my application I have to do full +> select every time user press button [Next] and show requested page +> using perl. This works more or less ok for several thousands rows but +> totally unusable for large selections. But now I'm about to work +> with big database and I don't know how I'll stay with postgres :-) +> It'll just doesn't work if customer will wait several minutes just browse +> next page. Mysql lacks some useful features postgres has +> (subselects, transaction ..) but for most Web applications I need +> just select :-) I dont' know how LIMIT is implemented in Mysql and +> I know it's not in SQL92 standart, but this makes Mysql very popular. +> +> Is it difficult to implement this feature in postgres ? +> +> Regards, +> +> Oleg +> +> +> _____________________________________________________________ +> Oleg Bartunov, sci.researcher, hostmaster of AstroNet, +> Sternberg Astronomical Institute, Moscow University (Russia) +> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ +> phone: +007(095)939-16-83, +007(095)939-23-83 +> +> + +Terry Mackintosh <terry@terrym.com> http://www.terrym.com +sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. + +Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3 +------------------------------------------------------------------- +Success Is A Choice ... book by Rick Patino, get it, read it! + + + + +From owner-pgsql-hackers@hub.org Tue Oct 13 18:12:41 1998 +Received: from hub.org (majordom@hub.org [209.47.148.200]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id SAA12156 + for <maillist@candle.pha.pa.us>; Tue, 13 Oct 1998 18:12:39 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id RAA04181; + Tue, 13 Oct 1998 17:56:17 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Tue, 13 Oct 1998 17:54:49 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id RAA03869 + for pgsql-hackers-outgoing; Tue, 13 Oct 1998 17:54:47 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from remapcorp.com (root@remapcorp.com [206.196.37.193]) + by hub.org (8.8.8/8.8.8) with ESMTP id RAA03838 + for <hackers@postgreSQL.org>; Tue, 13 Oct 1998 17:54:36 -0400 (EDT) + (envelope-from jeff@remapcorp.com) +Received: from go-to-jail (gotojail.remapcorp.com [206.196.37.197]) + by remapcorp.com (8.8.7/8.8.7) with SMTP id QAA25337; + Tue, 13 Oct 1998 16:55:35 -0500 (CDT) + (envelope-from jeff@remapcorp.com) +Message-ID: <006701bdf6f4$60ed75f0$c525c4ce@go-to-jail.remapcorp.com> +From: "Jeff Hoffmann" <jeff@remapcorp.com> +To: "Marc G. Fournier" <scrappy@hub.org>, "Eric Lee Green" <eric@linux-hw.com> +Cc: "PostgreSQL-development" <hackers@postgreSQL.org> +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +Date: Tue, 13 Oct 1998 16:56:48 -0500 +MIME-Version: 1.0 +Content-Type: text/plain; + charset="iso-8859-1" +Content-Transfer-Encoding: 7bit +X-Priority: 3 +X-MSMail-Priority: Normal +X-Mailer: Microsoft Outlook Express 4.72.3115.0 +X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +>On Tue, 13 Oct 1998, Eric Lee Green wrote: +> +>> On Tue, 13 Oct 1998, Jeff Hoffmann wrote: +>> > >I agree completely, LIMIT would be VERY usefull in web based apps, +which +>> > >is all I run. It does not matter to me if it is not part of a formal +>> > >standard. The idea is so common that it is a defacto standard. +>> > +>> > i'm not familiar with mysql and using "LIMIT" but wouldn't this same +effect +>> > be achieved by declaring a cursor and fetching however many records in +the +>> > cursor? it's a very noticeable improvement when you only want the +first 20 +>> > out of 500 in a 200k record database, at least. +>> +>> The problem with declaring a cursor vs. the "LIMIT" clause is that the +>> "LIMIT" clause, if used properly by the database engine (along with the +>> database engine using indexes in "ORDER BY" clauses) allows the database +>> engine to short-circuit the tail end of the query. That is, if you have +25 +>> names and the last one ends with BEAVIS, the database engine doesn't have +>> to go through the BUTTHEADS and KENNYs and etc. +>> +>> Theoretically a cursor is superior to the "LIMIT" clause because you're +>> eventually going to want the B's and K's and etc. anyhow -- but only in a +>> stateful enviornment. In the stateless web environment, a cursor is +>> useless because the connection can close at any time even when you're +>> using "persistent" connections (and of course when the connection closes +>> the cursor closes). +> +>Ookay, I'm sorry, butyou lost me here. I haven't gotten into using +>CURSORs/FETCHs yet, since I haven't need it...but can you give an example +>of what you would want to do using a LIMIT? I may be missing something, +>but wha is the different between using LIMIT to get X records, and +>definiing a cursor to FETCH X records? +> +>Practical example of *at least* the LIMIT side would be good, so that we +>can at least see a physical example of what LIMIT can do that +>CURSORs/FETCH can't... +> + + +fetch with cursors should work properly (i.e., you can short circuit it by +just ending your transaction) my understanding on how this works is exactly +how you explained LIMIT to work. here's some empirical proof from one of my +sample databases: + +the sample table i'm using has 156k records (names of people) +i'm using a PP180 with 128MB RAM and some old slow SCSI drives. + +public_mn=> select count(*) from public_ramsey; + count +------ +156566 +(1 row) + +i did the following query: +public_mn=> select * from public_ramsey where ownerlname ~ 'SMITH'; + +which returned 711 matches and took about 12 seconds. + +i did the same thing with a cursor: + +public_mn=> begin; +BEGIN +public_mn=> declare test cursor for select * from public_ramsey where +ownerlname ~ 'SMITH'; +SELECT + +the select was instantaneous. + +public_mn=> fetch 20 in test; + +returns 20 records almost instantaneously. each additional 20 took less +than a second, as well. + +if this isn't what you're talking about, i don't understand what you're +saying. + +jeff + + + +From eric@ireland.linux-hw.com Tue Oct 13 18:52:42 1998 +Received: from ireland.linux-hw.com (IDENT:eric@ireland.linux-hw.com [199.72.95.215]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id SAA12388 + for <maillist@candle.pha.pa.us>; Tue, 13 Oct 1998 18:52:40 -0400 (EDT) +Received: from localhost (eric@localhost) + by ireland.linux-hw.com (8.8.7/8.8.7) with SMTP id SAA31316; + Tue, 13 Oct 1998 18:55:22 -0400 +Date: Tue, 13 Oct 1998 18:55:22 -0400 (EDT) +From: Eric Lee Green <eric@linux-hw.com> +To: Bruce Momjian <maillist@candle.pha.pa.us> +cc: jeff@remapcorp.com, hackers@postgreSQL.org +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +In-Reply-To: <199810132116.RAA11249@candle.pha.pa.us> +Message-ID: <Pine.LNX.3.96.981013184022.31202B-100000@ireland.linux-hw.com> +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=US-ASCII +Status: RO + +On Tue, 13 Oct 1998, Bruce Momjian wrote: +> > Theoretically a cursor is superior to the "LIMIT" clause because you're +> > eventually going to want the B's and K's and etc. anyhow -- but only in a +> > stateful enviornment. In the stateless web environment, a cursor is +> > useless because the connection can close at any time even when you're +> > using "persistent" connections (and of course when the connection closes +> What we could do is _if_ there is only one table(no joins), and an index +> exists that matches the ORDER BY, we could use the index to +> short-circuit the query. + +This is exactly what MySQL does in this situation, except that it can use +the ORDER BY to do the short circuiting even if there is a join involved +if all of the elements of the ORDER BY belong to one table. Obviously if +I'm doing an "ORDER BY table1.foo table2.bar" that isn't going to work! +But "select table1.fsname,table1.lname,table2.receivables where +table2.receivables > 0 and table1.custnum=table2.custnum order by +(table1.lname,table1.fsname) limit 50" can be short-circuited by fiddling +with the join order -- table1.fsname table1.lname have to be the first two +things in the join order. + +Whether this is feasible in PostgreSQL I have no earthly idea. This would +seem to conflict with the join optimizer. + +> happier? If there is an ORDER BY and no index, or a join, I can't +> figure out how we would short-circuit the query. + +If there is an ORDER BY and no index you can't short-circuit the query. +MySQL doesn't either. Under certain circumstances (such as above) you can +short-circuit a join, but it's unclear whether it'd be easy to add such +a capability to PostgreSQL given the current structure of the query +optimizer. (And I certainly am not in a position to tackle it, at the +moment MySQL is sufficing for my project despite the fact that it is +quite limited compared to PostgreSQL, I need to get my project finished +first). + +-- +Eric Lee Green eric@linux-hw.com http://www.linux-hw.com/~eric +"To call Microsoft an innovator is like calling the Pope Jewish ..." + -- James Love (Consumer Project on Technology) + + +From owner-pgsql-hackers@hub.org Wed Oct 14 09:01:01 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id JAA24574 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 09:01:00 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id HAA17762 for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 07:47:57 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id HAA09214; + Wed, 14 Oct 1998 07:04:59 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 07:00:44 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id HAA09116 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 07:00:40 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id HAA09102 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 07:00:27 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id NAA05037; Wed, 14 Oct 1998 13:02:40 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma004737; Wed, 14 Oct 98 13:02:09 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id MAA20155; + Wed, 14 Oct 1998 12:59:23 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id NAA20772; + Wed, 14 Oct 1998 13:01:35 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<eric@linux-hw.com>> + id m0zTMGL-000B5AC; Wed, 14 Oct 98 10:26 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for eric@linux-hw.com + id m0zTOnx-000EBRC; Wed, 14 Oct 98 13:09 MET DST +Message-Id: <m0zTOnx-000EBRC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: eric@linux-hw.com (Eric Lee Green) +Date: Wed, 14 Oct 1998 13:09:21 +0200 (MET DST) +Cc: jeff@remapcorp.com, hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <Pine.LNX.3.96.981013161955.30555A-100000@ireland.linux-hw.com> from "Eric Lee Green" at Oct 13, 98 04:24:20 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: ROr + +Eric Lee Green wrote: +> +> On Tue, 13 Oct 1998, Jeff Hoffmann wrote: +> > >I agree completely, LIMIT would be VERY usefull in web based apps, which +> > >is all I run. It does not matter to me if it is not part of a formal +> > >standard. The idea is so common that it is a defacto standard. +> > +> > i'm not familiar with mysql and using "LIMIT" but wouldn't this same effect +> > be achieved by declaring a cursor and fetching however many records in the +> > cursor? it's a very noticeable improvement when you only want the first 20 +> > out of 500 in a 200k record database, at least. +> +> The problem with declaring a cursor vs. the "LIMIT" clause is that the +> "LIMIT" clause, if used properly by the database engine (along with the +> database engine using indexes in "ORDER BY" clauses) allows the database +> engine to short-circuit the tail end of the query. That is, if you have 25 +> names and the last one ends with BEAVIS, the database engine doesn't have +> to go through the BUTTHEADS and KENNYs and etc. +> +> Theoretically a cursor is superior to the "LIMIT" clause because you're +> eventually going to want the B's and K's and etc. anyhow -- but only in a +> stateful enviornment. In the stateless web environment, a cursor is +> useless because the connection can close at any time even when you're +> using "persistent" connections (and of course when the connection closes +> the cursor closes). + + I'm missing something. Well it's right that in the stateless + web environment a cursor has to be declared and closed for + any single CGI call. But even if you have a LIMIT clause, + your CGI must know with which key to start. + + So your query must look like + + SELECT ... WHERE key > 'last processed key' ORDER BY key; + + And your key must be unique (or at least contain no duplicate + entries) or you might miss some rows between the pages (have + 100 Brown's in the table and last processed key was a Brown + while using LIMIT). + + In postgres you could actually do the following (but read on + below - it's not optimized correct) + + BEGIN; + DECLARE c CURSOR FOR SELECT ... WHERE key > 'last' ORDER BY key; + FETCH 20 IN c; + (process the 20 rows in CGI) + CLOSE c; + COMMIT; + + Having LIMIT looks more elegant and has less overhead in CGI- + backend communication. But the cursor version is SQL + standard and portable. + +> +> I wanted very badly to use PostgreSQL for a web project I'm working on, +> but it just wouldn't do the job :-(. + + I've done some tests and what I found out might be a bug in + PostgreSQL's query optimizer. Having a table with 25k rows + where key is a text field with a unique index. Now I used + EXPLAIN for some queries + + SELECT * FROM tab; + + results in a seqscan - expected. + + SELECT * FROM tab ORDER BY key; + + results in a sort->seqscan - I would have + expected an indexscan! + + SELECT * FROM tab WHERE key > 'G'; + + results in an indexscan - expected. + + SELECT * FROM tab WHERE key > 'G' ORDER BY key; + + results in a sort->indexscan - hmmm. + + These results stay the same even if I blow up the table by + duplicating all rows (now with a non-unique index) to 100k + rows and have them presorted in the table. + + Needless to say that everything is vacuum'd for statistics. + + The last one is the query we would need in the web + environment used over a cursor as in the example above. But + due to the sort, the backend selects until the end of the + table, sorts them and then returns only the first 20 rows + (out of sorts result). + + This is very painful if the qualification (key > ...) points + to the beginning of the key list. + + Looking at planner.c I can see, that if there is a sortClause + in the parsetree, the planner creates a sort node and does + absolutely not check if there is an index that could be used + to do it. In the examples above, the sort is absolutely + needless because the index scan will already return the + tuples in the right order :-). + + Somewhere deep in my brain I found a statement that sorting + sorted data isn't only unnecessary (except the order + changes), it is slow too compared against sorting randomly + ordered data. + + Can we fix this before 6.4 release, will it be a past 6.4 or + am I doing something wrong here? I think it isn't a fix (it's + a planner enhancement) so it should really be a past 6.4 + item. + + For now, the only possibility is to omit the ORDER BY in the + query and hope the planner will always generate an index scan + (because of the qualification 'key > ...'). Doing so I + selected multiple times 20 rows (with the last key qual like + a CGI would do) in separate transactions. Using cursor and + fetch speeds up the access by a factor of 1000! But it is + unsafe and thus NOT RECOMMENDED! It's only a test if cursors + can do the LIMIT job - and they could if the planner would do + a better job. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + + +From owner-pgsql-hackers@hub.org Wed Oct 14 11:02:04 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id LAA25519 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 11:02:02 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id JAA24583 for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 09:46:21 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id IAA17022; + Wed, 14 Oct 1998 08:59:20 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 08:54:40 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id IAA16687 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 08:54:34 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id IAA16656 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 08:54:00 -0400 (EDT) + (envelope-from oleg@sai.msu.su) +Received: from ra (ra [158.250.29.2]) + by ra.sai.msu.su (8.9.1/8.9.1) with SMTP id PAA11714; + Wed, 14 Oct 1998 15:53:53 +0300 (MSK) +Date: Wed, 14 Oct 1998 16:53:53 +0400 (MSD) +From: Oleg Bartunov <oleg@sai.msu.su> +X-Sender: megera@ra +Reply-To: Oleg Bartunov <oleg@sai.msu.su> +To: hackers@postgreSQL.org +cc: t-ishii@sra.co.jp +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +In-Reply-To: <m0zTOnx-000EBRC@orion.SAPserv.Hamburg.dsh.de> +Message-ID: <Pine.GSO.3.96.SK.981014163020.10948B-100000@ra> +Organization: Sternberg Astronomical Institute (Moscow University) +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=US-ASCII +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +On Wed, 14 Oct 1998, Jan Wieck wrote: + +> Date: Wed, 14 Oct 1998 13:09:21 +0200 (MET DST) +> From: Jan Wieck <jwieck@debis.com> +> To: Eric Lee Green <eric@linux-hw.com> +> Cc: jeff@remapcorp.com, hackers@postgreSQL.org +> Subject: Re: [HACKERS] What about LIMIT in SELECT ? +> +> Eric Lee Green wrote: +> > +> > On Tue, 13 Oct 1998, Jeff Hoffmann wrote: +> > > >I agree completely, LIMIT would be VERY usefull in web based apps, which +> > > >is all I run. It does not matter to me if it is not part of a formal +> > > >standard. The idea is so common that it is a defacto standard. +> > > +> > > i'm not familiar with mysql and using "LIMIT" but wouldn't this same effect +> > > be achieved by declaring a cursor and fetching however many records in the +> > > cursor? it's a very noticeable improvement when you only want the first 20 +> > > out of 500 in a 200k record database, at least. +> > +> > The problem with declaring a cursor vs. the "LIMIT" clause is that the +> > "LIMIT" clause, if used properly by the database engine (along with the +> > database engine using indexes in "ORDER BY" clauses) allows the database +> > engine to short-circuit the tail end of the query. That is, if you have 25 +> > names and the last one ends with BEAVIS, the database engine doesn't have +> > to go through the BUTTHEADS and KENNYs and etc. +> > +> > Theoretically a cursor is superior to the "LIMIT" clause because you're +> > eventually going to want the B's and K's and etc. anyhow -- but only in a +> > stateful enviornment. In the stateless web environment, a cursor is +> > useless because the connection can close at any time even when you're +> > using "persistent" connections (and of course when the connection closes +> > the cursor closes). +> +> I'm missing something. Well it's right that in the stateless +> web environment a cursor has to be declared and closed for +> any single CGI call. But even if you have a LIMIT clause, +> your CGI must know with which key to start. +> + This is not a problem for CGI-script to know which key to start. + Without LIMIT every CGI call backend will do *FULL* selection + and cursor helps just in fetching a definite number of rows, + in principle I can do this with CGI-script. Also, cursor + returns data back in ASCII format (man l declare) and this requires + additional job for backend to convert data from intrinsic (binary) + format. Right implementation of LIMIT offset,number_of_rows could be + a great win and make postgres superior free database engine for + Web applications. Many colleagues of mine used mysql instead of + postgres just because of lacking LIMIT. Tatsuo posted a patch + for set query_limit to 'num', I just tested it and seems it + works fine. Now, we need only possibility to specify offset, + say + set query_limit to 'offset,num' + ( Tatsuo, How difficult to do this ?) + and LIMIT problem will ne gone. + + I'm wonder how many useful patches could be hidden from people :-), + + Regards, + + Oleg + +PS. + + Tatsuo, do you have patch for 6.3.2 ? + I can't wait for 6.4 :-) +_____________________________________________________________ +Oleg Bartunov, sci.researcher, hostmaster of AstroNet, +Sternberg Astronomical Institute, Moscow University (Russia) +Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ +phone: +007(095)939-16-83, +007(095)939-23-83 + + + + + +From owner-pgsql-hackers@hub.org Wed Oct 14 11:02:00 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id LAA25510 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 11:01:59 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id KAA28854 for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 10:40:56 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id KAA21542; + Wed, 14 Oct 1998 10:03:45 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 09:59:10 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id JAA21121 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 09:59:08 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from golem.jpl.nasa.gov (root@hectic-2.jpl.nasa.gov [128.149.68.204]) + by hub.org (8.8.8/8.8.8) with ESMTP id JAA21106 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 09:59:02 -0400 (EDT) + (envelope-from lockhart@alumni.caltech.edu) +Received: from alumni.caltech.edu (localhost [127.0.0.1]) + by golem.jpl.nasa.gov (8.8.5/8.8.5) with ESMTP id NAA19587; + Wed, 14 Oct 1998 13:59:56 GMT +Message-ID: <3624AE5C.752E4E7F@alumni.caltech.edu> +Date: Wed, 14 Oct 1998 13:59:56 +0000 +From: "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> +Organization: Caltech/JPL +X-Mailer: Mozilla 4.07 [en] (X11; I; Linux 2.0.30 i686) +MIME-Version: 1.0 +To: Jan Wieck <jwieck@debis.com> +CC: Eric Lee Green <eric@linux-hw.com>, jeff@remapcorp.com, + hackers@postgreSQL.org +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +References: <m0zTOnx-000EBRC@orion.SAPserv.Hamburg.dsh.de> +Content-Type: text/plain; charset=us-ascii +Content-Transfer-Encoding: 7bit +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: ROr + +> I've done some tests and what I found out might be a bug in +> PostgreSQL's query optimizer. +> SELECT * FROM tab ORDER BY key; +> results in a sort->seqscan - I would have +> expected an indexscan! + +Given that a table _could_ be completely unsorted on disk, it is +probably reasonable to suck the data in for a possible in-memory sort +rather than skipping around the disk to pick up individual tuples via +the index. Don't know if vacuum has a statistic on "orderness"... + +> SELECT * FROM tab WHERE key > 'G' ORDER BY key; +> results in a sort->indexscan - hmmm. +> The last one is the query we would need in the web +> environment used over a cursor as in the example above. But +> due to the sort, the backend selects until the end of the +> table, sorts them and then returns only the first 20 rows +> (out of sorts result). + +So you are saying that for this last case the sort was unnecessary? Does +the backend traverse the index in the correct order to guarantee that +the tuples are coming out already sorted? Does a hash index give the +same plan (I would expect a sort->seqscan for a hash index)? + + - Tom + + +From owner-pgsql-hackers@hub.org Wed Oct 14 11:01:52 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id LAA25504 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 11:01:51 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id KAA00198 for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 10:57:15 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id KAA22877; + Wed, 14 Oct 1998 10:19:47 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 10:15:44 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id KAA22675 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 10:15:41 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id KAA22657 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 10:15:32 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id QAA20563; Wed, 14 Oct 1998 16:18:02 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma020404; Wed, 14 Oct 98 16:17:25 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id QAA05077; + Wed, 14 Oct 1998 16:14:48 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id QAA22248; + Wed, 14 Oct 1998 16:17:06 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<hackers@postgreSQL.org>> + id m0zTPJb-000B5AC; Wed, 14 Oct 98 13:42 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for hackers@postgreSQL.org + id m0zTRrE-000EBRC; Wed, 14 Oct 98 16:24 MET DST +Message-Id: <m0zTRrE-000EBRC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: oleg@sai.msu.su +Date: Wed, 14 Oct 1998 16:24:56 +0200 (MET DST) +Cc: hackers@postgreSQL.org, t-ishii@sra.co.jp +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <Pine.GSO.3.96.SK.981014163020.10948B-100000@ra> from "Oleg Bartunov" at Oct 14, 98 04:53:53 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +Oleg Bartunov wrote: +> This is not a problem for CGI-script to know which key to start. + + Never meant that would be a problem. A FORM variable will of + course do this. + +> Without LIMIT every CGI call backend will do *FULL* selection +> and cursor helps just in fetching a definite number of rows, +> in principle I can do this with CGI-script. Also, cursor +> returns data back in ASCII format (man l declare) and this requires +> additional job for backend to convert data from intrinsic (binary) +> format. Right implementation of LIMIT offset,number_of_rows could be +> a great win and make postgres superior free database engine for +> Web applications. Many colleagues of mine used mysql instead of + + That's the point I was missing. The offset! + +> postgres just because of lacking LIMIT. Tatsuo posted a patch +> for set query_limit to 'num', I just tested it and seems it +> works fine. Now, we need only possibility to specify offset, +> say +> set query_limit to 'offset,num' +> ( Tatsuo, How difficult to do this ?) +> and LIMIT problem will ne gone. + + Think you haven't read my posting completely. Even with the + executor limit, the complete scan into the sort is done by + the backend. You need to specify ORDER BY to get the same + list again (without the offset doesn't make sense). But + currently, ORDER BY forces a sort node into the query plan. + + What the executor limit tells is how many rows will be + returned from the sorted data. Not what goes into the sort. + Filling the sort and sorting the data consumes the most time + of the queries execution. + + I haven't looked at Tatsuo's patch very well. But if it + limits the amount of data going into the sort (on ORDER BY), + it will break it! The requested ordering could be different + from what the choosen index might return. The used index is + choosen by the planner upon the qualifications given, not the + ordering wanted. + + So if you select WHERE b = 1 ORDER BY a, then it will use an + index on attribute b to match the qualification. The complete + result of that index scan goes into the sort to get ordered + by a. If now the executor limit stops sort filling after the + limit is exceeded, only the same tuples will go into the sort + every time. But they have nothing to do with the requested + order by a. + + What LIMIT first needs is a planner enhancement. In file + backend/optimizer/plan/planner.c line 284 it must be checked + if the actual plan is an indexscan, if the indexed attributes + are all the same as those in the given sort clause and that + the requested sort order (operator) is that what the index + will return. If that all matches, it can ignore the sort + clause and return the index scan itself. + + Second enhancement must be the handling of the offset. In + the executor, the index scan must skip offset index tuples + before returning the first. But NOT if the plan isn't a + 1-table-index-scan. In that case the result tuples (from the + topmost unique/join/whatever node) have to be skipped. + + With these enhancements, the index tuples to be skipped + (offset) will still be scanned, but not the data tuples they + point to. Index scanning might be somewhat faster. + + This all will only speedup simple 1-table-queries, no joins + or if the requested order isn't that what the index exactly + returns. + + Anyway, I'll take a look if I can change the planner to omit + the sort if the tests described above are true. I think it + would be good anyway. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + + +From owner-pgsql-hackers@hub.org Wed Oct 14 11:01:36 1998 +Received: from hub.org (majordom@hub.org [209.47.148.200]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id LAA25489 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 11:01:34 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id KAA24286; + Wed, 14 Oct 1998 10:30:14 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 10:26:34 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id KAA23732 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 10:26:27 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id KAA23717 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 10:26:13 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id QAA25644; Wed, 14 Oct 1998 16:28:01 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma025301; Wed, 14 Oct 98 16:27:43 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id QAA05943; + Wed, 14 Oct 1998 16:24:42 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id QAA22339; + Wed, 14 Oct 1998 16:26:57 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<lockhart@alumni.caltech.edu>> + id m0zTPT8-000B5AC; Wed, 14 Oct 98 13:51 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for lockhart@alumni.caltech.edu + id m0zTS0m-000EBRC; Wed, 14 Oct 98 16:34 MET DST +Message-Id: <m0zTS0m-000EBRC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: lockhart@alumni.caltech.edu (Thomas G. Lockhart) +Date: Wed, 14 Oct 1998 16:34:47 +0200 (MET DST) +Cc: jwieck@debis.com, eric@linux-hw.com, jeff@remapcorp.com, + hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <3624AE5C.752E4E7F@alumni.caltech.edu> from "Thomas G. Lockhart" at Oct 14, 98 01:59:56 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +> +> > SELECT * FROM tab WHERE key > 'G' ORDER BY key; +> > results in a sort->indexscan - hmmm. +> > The last one is the query we would need in the web +> > environment used over a cursor as in the example above. But +> > due to the sort, the backend selects until the end of the +> > table, sorts them and then returns only the first 20 rows +> > (out of sorts result). +> +> So you are saying that for this last case the sort was unnecessary? Does +> the backend traverse the index in the correct order to guarantee that +> the tuples are coming out already sorted? Does a hash index give the +> same plan (I would expect a sort->seqscan for a hash index)? + + Good point! As far as I can see, the planner chooses index + usage only depending on the WHERE clause. A hash index is + only usable when the given qualification uses = on the + indexed attribute(s). + + If the sortClause exactly matches the indexed attributes of + the ONE used btree index and all operators request ascending + order I think the index scan already returns the correct + order. Who know's definitely? + + Addition to my last posting: ... and if the index scan is + using a btree index ... + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + + +From owner-pgsql-hackers@hub.org Wed Oct 14 13:55:58 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA29300 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 13:55:56 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id NAA14245 for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 13:49:19 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id NAA13110; + Wed, 14 Oct 1998 13:25:55 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 13:22:14 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id NAA12694 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 13:22:13 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from candle.pha.pa.us (maillist@s5-03.ppp.op.net [209.152.195.67]) + by hub.org (8.8.8/8.8.8) with ESMTP id NAA12677 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 13:22:05 -0400 (EDT) + (envelope-from maillist@candle.pha.pa.us) +Received: (from maillist@localhost) + by candle.pha.pa.us (8.9.0/8.9.0) id NAA28746; + Wed, 14 Oct 1998 13:21:15 -0400 (EDT) +From: Bruce Momjian <maillist@candle.pha.pa.us> +Message-Id: <199810141721.NAA28746@candle.pha.pa.us> +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +In-Reply-To: <3624AE5C.752E4E7F@alumni.caltech.edu> from "Thomas G. Lockhart" at "Oct 14, 1998 1:59:56 pm" +To: lockhart@alumni.caltech.edu (Thomas G. Lockhart) +Date: Wed, 14 Oct 1998 13:21:15 -0400 (EDT) +Cc: jwieck@debis.com, eric@linux-hw.com, jeff@remapcorp.com, + hackers@postgreSQL.org +X-Mailer: ELM [version 2.4ME+ PL47 (25)] +MIME-Version: 1.0 +Content-Type: text/plain; charset=US-ASCII +Content-Transfer-Encoding: 7bit +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: ROr + +> > I've done some tests and what I found out might be a bug in +> > PostgreSQL's query optimizer. +> > SELECT * FROM tab ORDER BY key; +> > results in a sort->seqscan - I would have +> > expected an indexscan! +> +> Given that a table _could_ be completely unsorted on disk, it is +> probably reasonable to suck the data in for a possible in-memory sort +> rather than skipping around the disk to pick up individual tuples via +> the index. Don't know if vacuum has a statistic on "orderness"... + +Thomas is correct on this. Vadim has run some tests, and with our +optimized psort() code, the in-memory sort is often faster than using +the index to get the tuple, because you are jumping all over the drive. +I don't remember, but obviously there is a break-even point where +getting X rows using the index on a table of Y rows is faster , but +getting X+1 rows on a table of Y rows is faster getting all the rows +sequentailly, and doing the sort. + +You would have to pick only certain queries(no joins, index matches +ORDER BY), take the number of rows requested, and the number of rows +selected, and figure out if it is faster to use the index, or a +sequential scan and do the ORDER BY yourself. + + +Add to this the OFFSET capability. I am not sure how you are going to +get into the index and start at the n-th entry, unless perhaps you just +sequential scan the index. + +In fact, many queries just get column already indexed, and we could just +pull the data right out of the index. + +I have added this to the TODO list: + + * Pull requested data directly from indexes, bypassing heap data + +I think this has to be post-6.4 work, but I think we need to work in +this direction. I am holding off any cnfify fixes for post-6.4, but a +6.4.1 performance release certainly is possible. + + +But, you are correct that certain cases where in index is already being +used on a query, you could just skip the sort IF you used the index to +get the rows from the base table. + +-- + Bruce Momjian | http://www.op.net/~candle + maillist@candle.pha.pa.us | (610) 853-3000 + + If your life is a hard drive, | 830 Blythe Avenue + + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 + + +From owner-pgsql-hackers@hub.org Wed Oct 14 13:55:59 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA29303 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 13:55:58 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id NAA13463 for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 13:39:05 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id NAA11655; + Wed, 14 Oct 1998 13:13:32 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 13:09:41 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id NAA11013 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 13:09:39 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from terry1.acun.com (terry@terry1.acun.com [206.27.86.12]) + by hub.org (8.8.8/8.8.8) with ESMTP id NAA10997 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 13:09:30 -0400 (EDT) + (envelope-from terry@terrym.com) +Received: from localhost (terry@localhost) + by terry1.acun.com (8.8.5/8.8.5) with SMTP id NAA14478; + Wed, 14 Oct 1998 13:21:51 -0400 +Date: Wed, 14 Oct 1998 13:21:51 -0400 (EDT) +From: Terry Mackintosh <terry@terrym.com> +X-Sender: terry@terry1.acun.com +To: Jeff Hoffmann <jeff@remapcorp.com> +cc: PostgreSQL-development <hackers@postgreSQL.org> +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +In-Reply-To: <005101bdf6de$f9345150$c525c4ce@go-to-jail.remapcorp.com> +Message-ID: <Pine.LNX.3.95.981014130857.14397B-100000@terry1.acun.com> +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=US-ASCII +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +On Tue, 13 Oct 1998, Jeff Hoffmann wrote: + +> >Hi, my 2 cents... +> > +> >I agree completely, LIMIT would be VERY usefull in web based apps, which +> >is all I run. It does not matter to me if it is not part of a formal +> >standard. The idea is so common that it is a defacto standard. +> +> i'm not familiar with mysql and using "LIMIT" but wouldn't this same effect +> be achieved by declaring a cursor and fetching however many records in the +> cursor? it's a very noticeable improvement when you only want the first 20 +> out of 500 in a 200k record database, at least. + +Yes, while this is an improvement, it still has to do the entire query, +would be nice if the query could be terminated after a designated number +of rows where found, thus freeing system resources that are other wise +consumed. +I have seen web users run ridculous querys, like search for the +letter 'a', and it happens to be a substring search, now the box go'es ape +shit for 5 or 10 min.s while it basically gets the whole db as the search +result. All this befor you can do a 'FETCH', as I understand FETCH, I +will need to read up on it. + +Note that I do not have any databases that larg on my box, I was thinking +back to my VFP/NT experiances. + +Have a great day +Terry Mackintosh <terry@terrym.com> http://www.terrym.com +sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. + +Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3 +------------------------------------------------------------------- +Success Is A Choice ... book by Rick Patino, get it, read it! + + + +From owner-pgsql-hackers@hub.org Wed Oct 14 13:59:05 1998 +Received: from hub.org (majordom@hub.org [209.47.148.200]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA29345 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 13:58:59 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id NAA14021; + Wed, 14 Oct 1998 13:32:51 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 13:29:09 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id NAA13364 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 13:29:07 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from terry1.acun.com (terry@terry1.acun.com [206.27.86.12]) + by hub.org (8.8.8/8.8.8) with ESMTP id NAA13328 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 13:28:56 -0400 (EDT) + (envelope-from terry@terrym.com) +Received: from localhost (terry@localhost) + by terry1.acun.com (8.8.5/8.8.5) with SMTP id NAA14606 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 13:41:25 -0400 +Date: Wed, 14 Oct 1998 13:41:24 -0400 (EDT) +From: Terry Mackintosh <terry@terrym.com> +X-Sender: terry@terry1.acun.com +To: PostgreSQL-development <hackers@postgreSQL.org> +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +In-Reply-To: <199810132116.RAA11249@candle.pha.pa.us> +Message-ID: <Pine.LNX.3.95.981014133641.14397D-100000@terry1.acun.com> +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=US-ASCII +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +On Tue, 13 Oct 1998, Bruce Momjian wrote: + +> What we could do is _if_ there is only one table(no joins), and an index +> exists that matches the ORDER BY, we could use the index to +> short-circuit the query. +> +> I have added this item to the TODO list: +> +> * Allow LIMIT ability on single-table queries that have no ORDER BY or +> a matching index +> +> This looks do-able, and a real win. Would this make web applications +> happier? If there is an ORDER BY and no index, or a join, I can't +> figure out how we would short-circuit the query. +> +Yes, this would do for most of my apps. +It may just be my lack of sophistication, but I find that most web apps +are very simple in nature/table layout, and thus queries are often on only +a single table. + +Thanks +Terry Mackintosh <terry@terrym.com> http://www.terrym.com +sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. + +Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3 +------------------------------------------------------------------- +Success Is A Choice ... book by Rick Patino, get it, read it! + + + +From wieck@sapserv.debis.de Wed Oct 14 13:55:53 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA29290 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 13:55:51 -0400 (EDT) +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id NAA14370 for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 13:51:19 -0400 (EDT) +Received: by dsh.de; id TAA03418; Wed, 14 Oct 1998 19:50:18 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma003369; Wed, 14 Oct 98 19:49:51 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id TAA16746; + Wed, 14 Oct 1998 19:47:14 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id TAA23570; + Wed, 14 Oct 1998 19:49:32 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<lockhart@alumni.caltech.edu>> + id m0zTSdF-000B5AC; Wed, 14 Oct 98 17:14 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for lockhart@alumni.caltech.edu + id m0zTVAt-000EBRC; Wed, 14 Oct 98 19:57 MET DST +Message-Id: <m0zTVAt-000EBRC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Wed, 14 Oct 1998 19:57:27 +0200 (MET DST) +Cc: lockhart@alumni.caltech.edu, jwieck@debis.com, eric@linux-hw.com, + jeff@remapcorp.com, hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810141721.NAA28746@candle.pha.pa.us> from "Bruce Momjian" at Oct 14, 98 01:21:15 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Status: RO + +> But, you are correct that certain cases where in index is already being +> used on a query, you could just skip the sort IF you used the index to +> get the rows from the base table. + + Especially in the case where + + SELECT ... WHERE key > 'val' ORDER BY key; + + creates a Sort->IndexScan plan. The index scan already jumps + around on the disc to collect the sorts input and the sort + finally returns exactly the same output (if the used index is + only on key). + + And this is the case for large tables. The planner first + decides to use an index scan due to the WHERE clause and + later it notices the ORDER BY clause and creates a sort over + the scan. + + I'm actually hacking around on it to see what happens if I + suppress the sort node in some cases. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + +From owner-pgsql-hackers@hub.org Wed Oct 14 16:31:07 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id QAA01119 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 16:31:05 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id PAA22534 for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 15:29:50 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id PAA26335; + Wed, 14 Oct 1998 15:05:26 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 15:02:13 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id PAA26013 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 15:02:11 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67]) + by hub.org (8.8.8/8.8.8) with ESMTP id PAA25996 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 15:01:58 -0400 (EDT) + (envelope-from maillist@candle.pha.pa.us) +Received: (from maillist@localhost) + by candle.pha.pa.us (8.9.0/8.9.0) id OAA29639; + Wed, 14 Oct 1998 14:27:05 -0400 (EDT) +From: Bruce Momjian <maillist@candle.pha.pa.us> +Message-Id: <199810141827.OAA29639@candle.pha.pa.us> +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +In-Reply-To: <199810141721.NAA28746@candle.pha.pa.us> from Bruce Momjian at "Oct 14, 1998 1:21:15 pm" +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Wed, 14 Oct 1998 14:27:05 -0400 (EDT) +Cc: lockhart@alumni.caltech.edu, jwieck@debis.com, eric@linux-hw.com, + jeff@remapcorp.com, hackers@postgreSQL.org +X-Mailer: ELM [version 2.4ME+ PL47 (25)] +MIME-Version: 1.0 +Content-Type: text/plain; charset=US-ASCII +Content-Transfer-Encoding: 7bit +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +> Thomas is correct on this. Vadim has run some tests, and with our +> optimized psort() code, the in-memory sort is often faster than using +> the index to get the tuple, because you are jumping all over the drive. +> I don't remember, but obviously there is a break-even point where +> getting X rows using the index on a table of Y rows is faster , but +> getting X+1 rows on a table of Y rows is faster getting all the rows +> sequentailly, and doing the sort. +> +> You would have to pick only certain queries(no joins, index matches +> ORDER BY), take the number of rows requested, and the number of rows +> selected, and figure out if it is faster to use the index, or a +> sequential scan and do the ORDER BY yourself. +> +> Add to this the OFFSET capability. I am not sure how you are going to +> get into the index and start at the n-th entry, unless perhaps you just +> sequential scan the index. +> +> In fact, many queries just get column already indexed, and we could just +> pull the data right out of the index. +> +> I have added this to the TODO list: +> +> * Pull requested data directly from indexes, bypassing heap data +> +> I think this has to be post-6.4 work, but I think we need to work in +> this direction. I am holding off any cnfify fixes for post-6.4, but a +> 6.4.1 performance release certainly is possible. +> +> +> But, you are correct that certain cases where in index is already being +> used on a query, you could just skip the sort IF you used the index to +> get the rows from the base table. + +I have had more time to think about this. Basically, for pre-sorted +data, our psort code is very fast, because it does not need to sort +anything. It just moves the rows in and out of the sort memory. Yes, +it could be removed in some cases, and probably should be, but it is not +going to produce great speedups. + +The more general case I will describe below. + +First, let's look at a normal query: + + SELECT * + FROM tab + ORDER BY col1 + +This is not going to use an index, and probably should not because it is +faster for large tables to sort them in memory, rather than moving all +over the disk. For small tables, if the entire table fits in the buffer +cache, it may be faster to use the index, but on a small table the sort +doesn't take very long either, and the buffer cache effectiveness is +affected by other backends using it, so it may be better not to count on +it for a speedup. + +However, if you only want the first 10 rows, that is a different story. +We pull all the rows into the backend, sort them, then return 10 rows. +The query, if we could do it, should be written as: + + SELECT * + FROM tab + WHERE col1 < some_unknown_value + ORDER BY col1 + +In this case, the optimizer looks at the column statistics, and properly +uses an index to pull only a small subset of the table. This is the +type of behavior people want for queries returning only a few values. + +But, unfortunately, we don't know that mystery value. + +Now, everyone agrees we need an index matching the ORDER BY to make this +query quick, but we don't know that mystery value, so currently we +execute the whole query, and do a fetch. + +What I am now thinking is that maybe we need a way to walk around that +index. Someone months ago asked how to do that, and we told him he +couldn't, because this not a C-ISAM/dbm type database. However, if we +could somehow pass into the query the index location we want to start +at, and how many rows we need, that would solve our problem, and perhaps +even allow joined queries to work, assuming the table in the ORDER BY is +in an outer join loop. + + SELECT * + FROM tab + WHERE col1 < some_unknown_value + ORDER BY col1 + USING INDEX tab_idx(452) COUNT 100 + +where 452 is an 452th index entry, and COUNT is the number of index rows +you want to process. The query may return more or less than 100 rows if +there is a join and it joins to zero or more than one row in the joined +table, but this seems like perhaps a good way to go at it. We need to +do it this way because if a single index row returns 4 result rows, and +only two of the four rows fit in the number of rows returnd as set by the +user, it is hard to re-start the query at the proper point, because you +would have to process the index rows a second time, and return just part +of the result, and that is hard. + +If the index changes, or rows are added, the results are going to be +unreliable, but that is probably going to be true of any state-less +implementation we can devise. + +I think this may be fairly easy to implement. We could sequential scan +the index to get to the 452th row. That is going to be quick. We can +pass the 452 into the btree index code, so only a certain range of index +tuples are returned, and the system believes it has processed the entire +query, while we know it hasn't. Doesn't really work with hash, so we +will not allow it for those indexes. + +To make it really easy, we could implement it as a 'SET' command, so we +don't actually have it as part of the query, and have to pass it around +through all the modules. You would do the proper 'SET' before running +the query. Optimizer would look at 'SET' value to force index use. + + SET INDEX TO tab_idx START 452 COUNT 100 + +or + + SET INDEX TO tab_idx FROM 452 COUNT 451 + +There would have to be some way to signal that the end of the index had +been reached, because returning zero rows is not enough of a guarantee +in a joined SELECT. + +Comments? + +-- + Bruce Momjian | http://www.op.net/~candle + maillist@candle.pha.pa.us | (610) 853-3000 + + If your life is a hard drive, | 830 Blythe Avenue + + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 + + +From owner-pgsql-hackers@hub.org Wed Oct 14 17:31:23 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id RAA01591 + for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 17:31:21 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id RAA02744 for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 17:26:53 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id RAA05601; + Wed, 14 Oct 1998 17:03:21 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 16:59:54 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id QAA04964 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 16:59:52 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id QAA04943 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 16:59:28 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id WAA28383; Wed, 14 Oct 1998 22:57:42 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma028354; Wed, 14 Oct 98 22:57:28 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id WAA20547; + Wed, 14 Oct 1998 22:54:51 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id WAA24383; + Wed, 14 Oct 1998 22:57:09 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<lockhart@alumni.caltech.edu>> + id m0zTVYr-000B5AC; Wed, 14 Oct 98 20:22 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for lockhart@alumni.caltech.edu + id m0zTY6V-000EBRC; Wed, 14 Oct 98 23:05 MET DST +Message-Id: <m0zTY6V-000EBRC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Wed, 14 Oct 1998 23:05:07 +0200 (MET DST) +Cc: maillist@candle.pha.pa.us, lockhart@alumni.caltech.edu, jwieck@debis.com, + eric@linux-hw.com, jeff@remapcorp.com, hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810141827.OAA29639@candle.pha.pa.us> from "Bruce Momjian" at Oct 14, 98 02:27:05 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: ROr + +> I have had more time to think about this. Basically, for pre-sorted +> data, our psort code is very fast, because it does not need to sort +> anything. It just moves the rows in and out of the sort memory. Yes, +> it could be removed in some cases, and probably should be, but it is not +> going to produce great speedups. + + And I got the time to hack around about this. + + I hacked in a little check into the planner, that compares + the sortClause against the key field list of an index scan + and just suppresses the sort node if it exactly matchs and + all sort operators are "<". + + I tested with a 10k row table where key is a text field. The + base query is a + + SELECT ... WHERE key > 'val' ORDER BY key; + + The used 'val' is always a key that is close to the first of + all keys in the table ('' on the first query and the last + selected value on subsequent ones). + + Scenario 1 (S1) uses exactly the above query but processes + only the first 20 rows from the result buffer. Thus the + frontend receives nearly the whole table. + + Scenario 2 (S2) uses a cursor and FETCH 20. But closes the + cursor and creates a new one for the next selection (only + with another 'val') as it would occur in a web application. + + If there is no index on key, the backend will allways do a + Sort->SeqScan and due to the 'val' close to the lowest + existing key nearly all tuples get scanned and put into the + sort. S1 here runs about 10 seconds and S2 about 6 seconds. + The speedup in S2 results from the reduced overhead of + sending not wanted tuples into the frontend. + + Now with a btree index on key and an unpatched backend. + Produced plan is always a Sort->IndexScan. S1 needs 16 + seconds and S2 needs 12 seconds. Again nearly all data is put + into the sort but this time over the index scan and that is + slower. + + Last with the btree index on key and the patched backend. + This time the plan is a plain IndexScan because the ORDER BY + clause exactly matches the sort order of the choosen index. + S1 needs 13 seconds and S2 less than 0.2! This dramatic + speedup comes from the fact, that this time the index scan is + the toplevel executor node and the executor run is stopped + after 20 tuples have been selected. + + Analysis of the above timings: + + If there is an ORDER BY clause, using an index scan is the + clever way if the indexqual dramatically reduces the the + amount of data selected and sorted. I think this is the + normal case (who really selects nearly all rows from a 5M row + table?). So choosing the index path is correct. This will + hurt if someone really selects most of the rows and the index + scan jumps over the disc. But here the programmer should use + an unqualified query to perform a seqscan and do the + qualification in the frontend application. + + The speedup for the cursor/fetch scenario is so impressive + that I'll create a post 6.4 patch. I don't want it in 6.4 + because there is absolutely no query in the whole regression + test, where it suppresses the sort node. So we have + absolutely no check that it doesn't break anything. + + For a web application, that can use a unique key to select + the next amount of rows, it will be a big win. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + + +From owner-pgsql-hackers@hub.org Thu Oct 15 00:01:10 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id AAA06040 + for <maillist@candle.pha.pa.us>; Thu, 15 Oct 1998 00:01:04 -0400 (EDT) +Received: from hub.org (root@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id XAA29020 for <maillist@candle.pha.pa.us>; Wed, 14 Oct 1998 23:57:58 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id WAA02215; + Wed, 14 Oct 1998 22:39:07 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 14 Oct 1998 22:35:19 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id WAA02061 + for pgsql-hackers-outgoing; Wed, 14 Oct 1998 22:35:16 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from sraigw.sra.co.jp (sraigw.sra.co.jp [202.32.10.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id WAA01851 + for <hackers@postgreSQL.org>; Wed, 14 Oct 1998 22:35:01 -0400 (EDT) + (envelope-from t-ishii@srapc451.sra.co.jp) +Received: from srapc451.sra.co.jp (srapc451 [133.137.44.37]) + by sraigw.sra.co.jp (8.8.7/3.6Wbeta7-sraigw) with ESMTP id LAA17765; + Thu, 15 Oct 1998 11:34:39 +0900 (JST) +Received: from srapc451.sra.co.jp (localhost [127.0.0.1]) by srapc451.sra.co.jp (8.8.8/3.5Wpl7) with ESMTP id LAA08260; Thu, 15 Oct 1998 11:34:54 +0900 (JST) +Message-Id: <199810150234.LAA08260@srapc451.sra.co.jp> +To: jwieck@debis.com (Jan Wieck) +cc: oleg@sai.msu.su, hackers@postgreSQL.org, t-ishii@sra.co.jp +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +From: Tatsuo Ishii <t-ishii@sra.co.jp> +Reply-To: t-ishii@sra.co.jp +In-reply-to: Your message of Wed, 14 Oct 1998 16:24:56 +0200. + <m0zTRrE-000EBRC@orion.SAPserv.Hamburg.dsh.de> +Date: Thu, 15 Oct 1998 11:34:54 +0900 +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +>> postgres just because of lacking LIMIT. Tatsuo posted a patch +>> for set query_limit to 'num', I just tested it and seems it +>> works fine. Now, we need only possibility to specify offset, +>> say +>> set query_limit to 'offset,num' +>> ( Tatsuo, How difficult to do this ?) +>> and LIMIT problem will ne gone. +> +> Think you haven't read my posting completely. Even with the +> executor limit, the complete scan into the sort is done by +> the backend. You need to specify ORDER BY to get the same +> list again (without the offset doesn't make sense). But +> currently, ORDER BY forces a sort node into the query plan. + +I think we have understanded your point. set query_limit is just a +easy alternative of using cursor and fetch. + +> I haven't looked at Tatsuo's patch very well. But if it +> limits the amount of data going into the sort (on ORDER BY), +> it will break it! The requested ordering could be different +> from what the choosen index might return. The used index is +> choosen by the planner upon the qualifications given, not the +> ordering wanted. + +I think it limits the final result. When query_limit is set, +the arg "numberTuples" of ExecutePlan() is set to it instead of 0 +(this means no limit). + +Talking about "offset," it shouldn't be very difficult. I guess all we +have to do is adding a new arg "offset" to ExecutePlan() then making +obvious modifications. (and of course we have to modify set +query_limit syntax but it's trivial) + +However, before going ahead, I would like to ask other hackers about +this direction. This might be convenient for some users, but still the +essential performance issue would remain. In another word, this is a +short-term solution not a intrinsic one, IMHO. +-- +Tatsuo Ishii +t-ishii@sra.co.jp + + +From owner-pgsql-hackers@hub.org Thu Oct 15 10:01:17 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id KAA13960 + for <maillist@candle.pha.pa.us>; Thu, 15 Oct 1998 10:01:15 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id JAA20266 for <maillist@candle.pha.pa.us>; Thu, 15 Oct 1998 09:12:21 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id IAA26142; + Thu, 15 Oct 1998 08:19:49 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Thu, 15 Oct 1998 08:13:48 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id IAA25747 + for pgsql-hackers-outgoing; Thu, 15 Oct 1998 08:13:46 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id IAA25733 + for <hackers@postgreSQL.org>; Thu, 15 Oct 1998 08:13:40 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id OAA18677; Thu, 15 Oct 1998 14:16:12 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma018279; Thu, 15 Oct 98 14:15:39 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id OAA01227; + Thu, 15 Oct 1998 14:13:09 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id OAA28938; + Thu, 15 Oct 1998 14:15:27 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<jwieck@debis.com>> + id m0zTjtm-000B5AC; Thu, 15 Oct 98 11:40 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for jwieck@debis.com + id m0zTmRT-000EBRC; Thu, 15 Oct 98 14:23 MET DST +Message-Id: <m0zTmRT-000EBRC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: t-ishii@sra.co.jp +Date: Thu, 15 Oct 1998 14:23:43 +0200 (MET DST) +Cc: jwieck@debis.com, oleg@sai.msu.su, hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810150234.LAA08260@srapc451.sra.co.jp> from "Tatsuo Ishii" at Oct 15, 98 11:34:54 am +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +Tatsuo Ishii wrote: + +> I think we have understanded your point. set query_limit is just a +> easy alternative of using cursor and fetch. +> +> > I haven't looked at Tatsuo's patch very well. But if it +> > limits the amount of data going into the sort (on ORDER BY), +> > it will break it! The requested ordering could be different +> > from what the choosen index might return. The used index is +> > choosen by the planner upon the qualifications given, not the +> > ordering wanted. +> +> I think it limits the final result. When query_limit is set, +> the arg "numberTuples" of ExecutePlan() is set to it instead of 0 +> (this means no limit). +> +> Talking about "offset," it shouldn't be very difficult. I guess all we +> have to do is adding a new arg "offset" to ExecutePlan() then making +> obvious modifications. (and of course we have to modify set +> query_limit syntax but it's trivial) + + The offset could become + + FETCH n IN cursor [OFFSET n]; + + and + + SELECT ... [LIMIT offset,count]; + + The FETCH command already calls ExecutorRun() with the given + count (the tuple limit). Telling it the offset too is really + simple. And ExecutorRun() could check if the toplevel + executor node is an index scan. Skipping tuples during the + index scan requires, that all qualifications are in the + indexqual, thus any tuple returned by it will become a final + result row (as it would be in the simple 1-table-queries we + discussed). If that isn't the case, the executor must + fallback to skip the final result tuples and that is after an + eventually processed sort/merge of the complete result set. + That would only reduce communication to the client and memory + required there to buffer the result set (not a bad thing + either). + + ProcessQueryDesc() in tcop/pquery.c also calls ExecutorRun() + but with a constant 0 tuple count. Having offset and count in + the parsetree would make it without any state variables or + SET command. And it's the only clean way to restrict LIMIT to + SELECT queries. Any thrown in LIMIT to ExecutorRun() from + another place could badly hurt the rewrite system. Remember + that non-instead actions on insert/update/delete are + processed before the original query! And what about SQL + functions that get processed during the evaluation of another + query (view using an SQL function for count(*))? + + A little better would it be to make the LIMIT values able to + be parameter nodes. C or PL functions use the prepared plan + feature of the SPI manager for performance reasons. + Especially the offset value might there need to be a + parameter that the executor has to pick out first. If we + change the count argument of ExecutorRun to a List *limit, + this one could be NIL (to mean the old 0 count 0 offset + behaviour) or a list of two elements that both can be either + a Const or a Param of type int4. Easy for the executor to + evaluate. + + The only places where ExecutorRun() is called are + tcop/pquery.c (queries from frontend), commands/command.c + (FETCH command), executor/functions.c (SQL functions) and + executor/spi.c (SPI manager). So it is easy to change the + call interface too. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + + +From owner-pgsql-hackers@hub.org Thu Oct 15 14:32:34 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id OAA19803 + for <maillist@candle.pha.pa.us>; Thu, 15 Oct 1998 14:32:31 -0400 (EDT) +Received: from hub.org (root@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id NAA10847 for <maillist@candle.pha.pa.us>; Thu, 15 Oct 1998 13:38:16 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id MAA22772; + Thu, 15 Oct 1998 12:07:20 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Thu, 15 Oct 1998 12:02:33 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id MAA22026 + for pgsql-hackers-outgoing; Thu, 15 Oct 1998 12:02:31 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id MAA22007 + for <hackers@postgreSQL.org>; Thu, 15 Oct 1998 12:02:16 -0400 (EDT) + (envelope-from oleg@sai.msu.su) +Received: from ra (ra [158.250.29.2]) + by ra.sai.msu.su (8.9.1/8.9.1) with SMTP id TAA21024; + Thu, 15 Oct 1998 19:01:23 +0300 (MSK) +Date: Thu, 15 Oct 1998 20:01:23 +0400 (MSD) +From: Oleg Bartunov <oleg@sai.msu.su> +X-Sender: megera@ra +To: Jan Wieck <jwieck@debis.com> +cc: t-ishii@sra.co.jp, hackers@postgreSQL.org +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +In-Reply-To: <m0zTmRT-000EBRC@orion.SAPserv.Hamburg.dsh.de> +Message-ID: <Pine.GSO.3.96.SK.981015193853.19322D-100000@ra> +Organization: Sternberg Astronomical Institute (Moscow University) +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=US-ASCII +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: ROr + +This is a little bit off-topic, +I did some timings with latest cvs on my real database +( all output redirected to /dev/null ), table contains 8798 records, +31 columns, order key have indices. + +1.select count(*) from work_flats; +0.02user 0.00system 0:00.18elapsed 10%CPU (0avgtext+0avgdata 0maxresident)k +0inputs+0outputs (131major+21minor)pagefaults 0swaps + +2.select * from work_flats order by rooms, metro_id; +2.35user 0.25system 0:10.11elapsed 25%CPU (0avgtext+0avgdata 0maxresident)k +0inputs+0outputs (131major+2799minor)pagefaults 0swaps + +3.set query_limit to '150'; +SET VARIABLE +select * from work_flats order by rooms, metro_id; +0.06user 0.00system 0:02.75elapsed 2%CPU (0avgtext+0avgdata 0maxresident)k +0inputs+0outputs (131major+67minor)pagefaults 0swaps + +4.begin; +declare tt cursor for +select * from work_flats order by rooms, metro_id; +fetch 150 in tt; +end; +0.05user 0.01system 0:02.76elapsed 2%CPU (0avgtext+0avgdata 0maxresident)k +0inputs+0outputs (131major+67minor)pagefaults 0swaps + +As you can see timings for query_limit and cursor are very similar, +I didn't expected this. So, in principle, enhanced version of fetch +(with offset) would cover all we need from LIMIT, but query_limit would be +still useful, for example to restrict loadness of server. +Will all enhancements you discussed go to the 6.4 ? +I'm really interested in testing this stuff because I begin new project +and everything we discussed here are badly needed. + + + Regards, + + Oleg + + + +On Thu, 15 Oct 1998, Jan Wieck wrote: + +> Date: Thu, 15 Oct 1998 14:23:43 +0200 (MET DST) +> From: Jan Wieck <jwieck@debis.com> +> To: t-ishii@sra.co.jp +> Cc: jwieck@debis.com, oleg@sai.msu.su, hackers@postgreSQL.org +> Subject: Re: [HACKERS] What about LIMIT in SELECT ? +> +> Tatsuo Ishii wrote: +> +> > I think we have understanded your point. set query_limit is just a +> > easy alternative of using cursor and fetch. +> > +> > > I haven't looked at Tatsuo's patch very well. But if it +> > > limits the amount of data going into the sort (on ORDER BY), +> > > it will break it! The requested ordering could be different +> > > from what the choosen index might return. The used index is +> > > choosen by the planner upon the qualifications given, not the +> > > ordering wanted. +> > +> > I think it limits the final result. When query_limit is set, +> > the arg "numberTuples" of ExecutePlan() is set to it instead of 0 +> > (this means no limit). +> > +> > Talking about "offset," it shouldn't be very difficult. I guess all we +> > have to do is adding a new arg "offset" to ExecutePlan() then making +> > obvious modifications. (and of course we have to modify set +> > query_limit syntax but it's trivial) +> +> The offset could become +> +> FETCH n IN cursor [OFFSET n]; +> +> and +> +> SELECT ... [LIMIT offset,count]; +> +> The FETCH command already calls ExecutorRun() with the given +> count (the tuple limit). Telling it the offset too is really +> simple. And ExecutorRun() could check if the toplevel +> executor node is an index scan. Skipping tuples during the +> index scan requires, that all qualifications are in the +> indexqual, thus any tuple returned by it will become a final +> result row (as it would be in the simple 1-table-queries we +> discussed). If that isn't the case, the executor must +> fallback to skip the final result tuples and that is after an +> eventually processed sort/merge of the complete result set. +> That would only reduce communication to the client and memory +> required there to buffer the result set (not a bad thing +> either). +> +> ProcessQueryDesc() in tcop/pquery.c also calls ExecutorRun() +> but with a constant 0 tuple count. Having offset and count in +> the parsetree would make it without any state variables or +> SET command. And it's the only clean way to restrict LIMIT to +> SELECT queries. Any thrown in LIMIT to ExecutorRun() from +> another place could badly hurt the rewrite system. Remember +> that non-instead actions on insert/update/delete are +> processed before the original query! And what about SQL +> functions that get processed during the evaluation of another +> query (view using an SQL function for count(*))? +> +> A little better would it be to make the LIMIT values able to +> be parameter nodes. C or PL functions use the prepared plan +> feature of the SPI manager for performance reasons. +> Especially the offset value might there need to be a +> parameter that the executor has to pick out first. If we +> change the count argument of ExecutorRun to a List *limit, +> this one could be NIL (to mean the old 0 count 0 offset +> behaviour) or a list of two elements that both can be either +> a Const or a Param of type int4. Easy for the executor to +> evaluate. +> +> The only places where ExecutorRun() is called are +> tcop/pquery.c (queries from frontend), commands/command.c +> (FETCH command), executor/functions.c (SQL functions) and +> executor/spi.c (SPI manager). So it is easy to change the +> call interface too. +> +> +> Jan +> +> -- +> +> #======================================================================# +> # It's easier to get forgiveness for being wrong than for being right. # +> # Let's break this rule - forgive me. # +> #======================================== jwieck@debis.com (Jan Wieck) # +> +> + +_____________________________________________________________ +Oleg Bartunov, sci.researcher, hostmaster of AstroNet, +Sternberg Astronomical Institute, Moscow University (Russia) +Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ +phone: +007(095)939-16-83, +007(095)939-23-83 + + + +From owner-pgsql-hackers@hub.org Thu Oct 15 13:22:48 1998 +Received: from hub.org (majordom@hub.org [209.47.148.200]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA18540 + for <maillist@candle.pha.pa.us>; Thu, 15 Oct 1998 13:22:46 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id MAA01819; + Thu, 15 Oct 1998 12:56:25 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Thu, 15 Oct 1998 12:51:43 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id MAA01305 + for pgsql-hackers-outgoing; Thu, 15 Oct 1998 12:51:40 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id MAA01283 + for <pgsql-hackers@postgresql.org>; Thu, 15 Oct 1998 12:51:28 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id SAA21874; Thu, 15 Oct 1998 18:54:00 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma021705; Thu, 15 Oct 98 18:53:31 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id SAA25226; + Thu, 15 Oct 1998 18:50:57 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id SAA30639; + Thu, 15 Oct 1998 18:53:14 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<jwieck@debis.com>> + id m0zToEf-000B5AC; Thu, 15 Oct 98 16:18 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for jwieck@debis.com + id m0zTqmM-000EBRC; Thu, 15 Oct 98 19:01 MET DST +Message-Id: <m0zTqmM-000EBRC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: hannu@trust.ee (Hannu Krosing) +Date: Thu, 15 Oct 1998 19:01:33 +0200 (MET DST) +Cc: jwieck@debis.com, pgsql-hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <36261DF7.D20368A0@trust.ee> from "Hannu Krosing" at Oct 15, 98 07:08:23 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +Hannu Krosing wrote: + +> Jan Wieck wrote: +> > The speedup for the cursor/fetch scenario is so impressive +> > that I'll create a post 6.4 patch. I don't want it in 6.4 +> > because there is absolutely no query in the whole regression +> > test, where it suppresses the sort node. +> +> Good, then it works as expected ;) +> +> More seriously, it is not within powers of current regression test +> framework to test speed improvements (only the case where +> performance-wise bad implementation will actually crash the backend, +> as in the cnfify problem, but AFAIK we dont test for those now) +> +> > So we have absolutely no check that it doesn't break anything. +> +> If it did pass the regression, then IMHO it did not break anything. + + Thats the point. The check if the sort node is required + returns TRUE for ALL queries of the regression. So the + behaviour when it returns FALSE is absolutely not tested. + +> +> I would vote for putting it in (maybe with a +> 'set fix_optimiser_stupidity on' safeguard to enable it). I see no +> reason to postpone it to 6.4.1 and force almost everybody to first +> patch their copy and then upgrade very soon. +> +> I would even go far enough to call it a bugfix, as it does not really +> introduce any new functionality only fixes some existing functionality +> so that much bigger databases can be actually used. + + I can't call it a bugfix because it is only a performance win + in some situations. And I feel the risk is too high to put + untested code into the backend at BETA2 time. The max we + should do is to take this one and the LIMIT thing (maybe + implemented as I suggested lately), and put out a Web- + Performance-Release at the same time we release 6.4. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + + +From Inoue@tpf.co.jp Thu Oct 15 20:31:01 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id UAA26050 + for <maillist@candle.pha.pa.us>; Thu, 15 Oct 1998 20:31:00 -0400 (EDT) +Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id UAA12888 for <maillist@candle.pha.pa.us>; Thu, 15 Oct 1998 20:10:03 -0400 (EDT) +Received: from cadzone ([126.0.1.40]) + by sd.tpf.co.jp (2.0 Build 2131 (Berkeley 8.8.4)/8.8.4) with SMTP + id JAA02574; Fri, 16 Oct 1998 09:00:34 +0900 +From: "Hiroshi Inoue" <Inoue@tpf.co.jp> +To: "Jan Wieck" <jwieck@debis.com>, + "Bruce Momjian" <maillist@candle.pha.pa.us> +Subject: RE: [HACKERS] What about LIMIT in SELECT ? +Date: Fri, 16 Oct 1998 09:12:55 +0900 +Message-ID: <000201bdf899$b953bf00$2801007e@cadzone.tpf.co.jp> +MIME-Version: 1.0 +Content-Type: text/plain; + charset="iso-8859-1" +Content-Transfer-Encoding: 7bit +X-Priority: 3 (Normal) +X-MSMail-Priority: Normal +X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 +In-Reply-To: <199810150552.BAA07576@candle.pha.pa.us> +Importance: Normal +X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4 +Status: ROr + +> -----Original Message----- +> From: owner-pgsql-hackers@postgreSQL.org +> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Bruce Momjian +> Sent: Thursday, October 15, 1998 2:52 PM +> To: jwieck@debis.com +> Cc: lockhart@alumni.caltech.edu; jwieck@debis.com; eric@linux-hw.com; +> jeff@remapcorp.com; hackers@postgreSQL.org +> Subject: Re: [HACKERS] What about LIMIT in SELECT ? +> +> +> > > I have had more time to think about this. Basically, for pre-sorted +> > > data, our psort code is very fast, because it does not need to sort +> > > anything. It just moves the rows in and out of the sort memory. Yes, +> > > it could be removed in some cases, and probably should be, +> but it is not +> > > going to produce great speedups. +> > +> > And I got the time to hack around about this. +> > +> > I hacked in a little check into the planner, that compares +> > the sortClause against the key field list of an index scan +> > and just suppresses the sort node if it exactly matchs and +> > all sort operators are "<". +> > +> > I tested with a 10k row table where key is a text field. The +> > base query is a +> > +> > SELECT ... WHERE key > 'val' ORDER BY key; +> > +> > The used 'val' is always a key that is close to the first of +> > all keys in the table ('' on the first query and the last +> > selected value on subsequent ones). +> +> This is good stuff. I want to think about it for a day. Sounds very +> promising. +> + +Did you see my contribution about this subject ? +I have already implemented above cases and used on trial for three +months or more. +It is good to be formally supported by PostgreSQL community. + +And please remember that there are descending order cases. +(Moreover there are compound cases such as + SELECT * from ... order by key1 desc,key2 asc; + I didn't implement such cases.) + +Thanks. + +Hiroshi Inoue +Inoue@tpf.co.jp + + +From owner-pgsql-hackers@hub.org Fri Oct 16 04:01:07 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id EAA02029 + for <maillist@candle.pha.pa.us>; Fri, 16 Oct 1998 04:01:04 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id DAA05509 for <maillist@candle.pha.pa.us>; Fri, 16 Oct 1998 03:43:53 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id CAA11278; + Fri, 16 Oct 1998 02:00:01 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Fri, 16 Oct 1998 01:57:25 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id BAA11129 + for pgsql-hackers-outgoing; Fri, 16 Oct 1998 01:57:21 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67]) + by hub.org (8.8.8/8.8.8) with ESMTP id BAA11116 + for <hackers@postgreSQL.org>; Fri, 16 Oct 1998 01:57:00 -0400 (EDT) + (envelope-from maillist@candle.pha.pa.us) +Received: (from maillist@localhost) + by candle.pha.pa.us (8.9.0/8.9.0) id BAA29942; + Fri, 16 Oct 1998 01:34:33 -0400 (EDT) +From: Bruce Momjian <maillist@candle.pha.pa.us> +Message-Id: <199810160534.BAA29942@candle.pha.pa.us> +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +In-Reply-To: <m0zTY6V-000EBRC@orion.SAPserv.Hamburg.dsh.de> from Jan Wieck at "Oct 14, 1998 11: 5: 7 pm" +To: jwieck@debis.com +Date: Fri, 16 Oct 1998 01:34:33 -0400 (EDT) +Cc: lockhart@alumni.caltech.edu, jwieck@debis.com, eric@linux-hw.com, + jeff@remapcorp.com, hackers@postgreSQL.org +X-Mailer: ELM [version 2.4ME+ PL47 (25)] +MIME-Version: 1.0 +Content-Type: text/plain; charset=US-ASCII +Content-Transfer-Encoding: 7bit +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +OK, I have had my day of thinking, and will address this specific +posting first, because it is the most fundamental concerning the future +direction of the optimization. + +> +> And I got the time to hack around about this. +> +> I hacked in a little check into the planner, that compares +> the sortClause against the key field list of an index scan +> and just suppresses the sort node if it exactly matchs and +> all sort operators are "<". +> +> I tested with a 10k row table where key is a text field. The +> base query is a +> +> SELECT ... WHERE key > 'val' ORDER BY key; +> +> The used 'val' is always a key that is close to the first of +> all keys in the table ('' on the first query and the last +> selected value on subsequent ones). +> +> Scenario 1 (S1) uses exactly the above query but processes +> only the first 20 rows from the result buffer. Thus the +> frontend receives nearly the whole table. + +OK. + +> +> Scenario 2 (S2) uses a cursor and FETCH 20. But closes the +> cursor and creates a new one for the next selection (only +> with another 'val') as it would occur in a web application. +> +> If there is no index on key, the backend will allways do a +> Sort->SeqScan and due to the 'val' close to the lowest +> existing key nearly all tuples get scanned and put into the +> sort. S1 here runs about 10 seconds and S2 about 6 seconds. +> The speedup in S2 results from the reduced overhead of +> sending not wanted tuples into the frontend. + +Makes sense. All rows are processed, but not sent to client. + +> +> Now with a btree index on key and an unpatched backend. +> Produced plan is always a Sort->IndexScan. S1 needs 16 +> seconds and S2 needs 12 seconds. Again nearly all data is put +> into the sort but this time over the index scan and that is +> slower. + +VACUUM ANALYZE could affect this. Because it had no stats, it thought +index use would be faster, but in fact because 'val' was near the lowest +value, it as selecting 90% of the table, and would have been better with +a sequential scan. pg_statistics's low/hi values for a column could +have told that to the optimizer. + +I know the good part of the posting is coming. + +> Last with the btree index on key and the patched backend. +> This time the plan is a plain IndexScan because the ORDER BY +> clause exactly matches the sort order of the chosen index. +> S1 needs 13 seconds and S2 less than 0.2! This dramatic +> speedup comes from the fact, that this time the index scan is +> the toplevel executor node and the executor run is stopped +> after 20 tuples have been selected. + +OK, seems like in the S1 case, the use of the psort/ORDER BY code on top +of the index was taking and extra 3 seconds, which is 23%. That is a +lot more than I thought for the psort code, and shows we could gain a +lot by removing unneeded sorts from queries that are already using +matching indexes. + +Just for clarity, added to TODO. I think everyone is clear on this one, +and its magnitude is a surprise to me: + + * Prevent psort() usage when query already using index matching ORDER BY + + +> Analysis of the above timings: +> +> If there is an ORDER BY clause, using an index scan is the +> clever way if the indexqual dramatically reduces the the +> amount of data selected and sorted. I think this is the +> normal case (who really selects nearly all rows from a 5M row +> table?). So choosing the index path is correct. This will +> hurt if someone really selects most of the rows and the index +> scan jumps over the disc. But here the programmer should use +> an unqualified query to perform a seqscan and do the +> qualification in the frontend application. + +Fortunately, the optimizer already does the index selection for us, and +guesses pretty well if the index or sequential scan is better. Once we +implement the above removal of psort(), we will have to change the +timings because now you have to compare index scan against sequential +scan AND psort(), because in the index scan situation, you don't need +the psort(), assuming the ORDER BY matches the index exactly. + +> The speedup for the cursor/fetch scenario is so impressive +> that I'll create a post 6.4 patch. I don't want it in 6.4 +> because there is absolutely no query in the whole regression +> test, where it suppresses the sort node. So we have +> absolutely no check that it doesn't break anything. +> +> For a web application, that can use a unique key to select +> the next amount of rows, it will be a big win. + +OK, I think the reason the regression test did not show your code being +used is important. + +First, most of the tables are small in the regression test, so sequential +scans are faster. Second, most queries using indexes are either joins, +which do the entire table, or equality tests, like col = 3, where there +is no matching ORDER BY because all the col values are 3. Again, your +code can't help with these. + +The only regression-type code that would use it would be a 'col > 3' +qualification with a col ORDER BY, and there aren't many of those. + +However, if we think of the actual application you are addressing, it is +a major win. If we are going after only one row of the index, it is +fast. If we are going after the entire table, it is faster to +sequential scan and psort(). You big win is with the partial queries, +where you end up doing a full sequential scan or index scan, then and +ORDER BY, while you really only need a few rows from the query, and if +you deal directly with the index, you can prevent many rows from being +processed. It is the ability to skip processing those extra rows that +makes it a big win, not so much the removal of the ORDER BY, though that +helps too. + +Your solution really is tailored for this 'partial' query application, +and I think it is a big need for certain applications that can't use +cursors, like web apps. Most other apps have long-time connections to +the database, and are better off with cursors. + +I did profiling to improve startup time, because the database +requirements of web apps are different from normal db apps, and we have +to adjust to that. + +So, to reiterate, full queries are not benefited as much from the new +code, because sequential scan/psort is faster, or because the index only +retrieves a small number of rows because the qualification of values is +very specific. + +Those open-ended, give me the rows from 100 to 199 really need your +modifications. + +OK, we have QUERY_LIMIT, and that allows us to throw any query at the +system, and it will return that many of the first rows for the ORDER BY. +No fancy stuff required. If we can get a matching index, we may be able +to remove the requirement of scanning all the row (with Jan's patch), +and that is a big win. If not, we at least prevent the rows from being +returned to the client. + +However, there is the OFFSET issue. This is really a case where the +user wants to _restart_ the query where they left off. That is a +different problem. All of a sudden, we need to evaluate more of the +query, and return a segment from the middle of the result set. + +I think we need to decide how to handle such a restart. Do we +re-evaluate the entire query, skipping all the rows up to OFFSET, and +return the number of rows they requested after OFFSET. I would think we +don't want to do that, do we. It would be much easier to code. If it +is a single table, skipping forward has to be done anyway, because we +can't just _jump_ to the 100th entry in the index, unless we pass some +_tid_ to the user, and expect them to pass that back to start the query. +I don't think we went to do that. It is ugly, and the row may have +moved since we started. So, for a single table, adding a QUERY_OFFSET +would do exactly what we need, with Jan's patches. + +For a joined query, I think you will have to do the entire _join_ before +returning anything. + +You can't just process all the joins up to the OFFSET location, and you +can't just jump to the 100th index location, because you don't know that +the 100th index location produced the 100th result just returned to the +user. You have to process the whole query, and because of the join and +not knowing which data row from each table is going to make which entry +in the final result. If you are really craft, and the ORDER BY table is +in the outer part of the join loop, you could start processing the table +that is part of the outer loop in _index_ order, because you know that +the rows processed in index order are going to produce the output in +result order. You then could process and throw away the results up to +offset, and generate the needed rows and stop. + +The other way of doing it is to specify a query limit based on specific +index entries, so you say I want the query returned by the first 20 +index entries matching the ORDER BY, or entries 100-199, and the query +is limited to using only those entries in the index. In that case, +though, in joins, you could return more or less rows in the result +depending on the other tables, and that may be unacceptable. However, +for this case, the advantage is that you don't need to process the rows +from 1 to 99 because you have been told the user only wants rows from +certain index slots. If the user requests rows 50000-50100, this would +be much faster because you don't have to process the 50000 rows before +returning any data. However, I question how often people grab stuff +from the center of large data sets. Seems the QUERY_OFFSET idea may be +easier for users. + +I will be commenting on the rest of the optimization postings tomorrow. + +-- + Bruce Momjian | http://www.op.net/~candle + maillist@candle.pha.pa.us | (610) 853-3000 + + If your life is a hard drive, | 830 Blythe Avenue + + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 + + +From Inoue@tpf.co.jp Fri Oct 16 03:31:02 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id DAA01767 + for <maillist@candle.pha.pa.us>; Fri, 16 Oct 1998 03:31:00 -0400 (EDT) +Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id DAA04551 for <maillist@candle.pha.pa.us>; Fri, 16 Oct 1998 03:13:40 -0400 (EDT) +Received: from cadzone ([126.0.1.40]) + by sd.tpf.co.jp (2.0 Build 2131 (Berkeley 8.8.4)/8.8.4) with SMTP + id QAA02680; Fri, 16 Oct 1998 16:04:09 +0900 +From: "Hiroshi Inoue" <Inoue@tpf.co.jp> +To: "Bruce Momjian" <maillist@candle.pha.pa.us> +Cc: <jwieck@debis.com> +Subject: RE: [HACKERS] What about LIMIT in SELECT ? +Date: Fri, 16 Oct 1998 16:16:29 +0900 +Message-ID: <000001bdf8d4$e4cdf520$2801007e@cadzone.tpf.co.jp> +MIME-Version: 1.0 +Content-Type: text/plain; + charset="iso-8859-1" +Content-Transfer-Encoding: 7bit +X-Priority: 3 (Normal) +X-MSMail-Priority: Normal +X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 +Importance: Normal +X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4 +In-Reply-To: <199810160621.CAA01030@candle.pha.pa.us> +Status: RO + +Where's my contibution to hackers@potsgreSQL.org ? +I will resend it. + +> -----Original Message----- +> From: Bruce Momjian [mailto:maillist@candle.pha.pa.us] +> Sent: Friday, October 16, 1998 3:22 PM +> To: Hiroshi Inoue +> Cc: jwieck@debis.com +> Subject: Re: [HACKERS] What about LIMIT in SELECT ? +> +> +> [Charset iso-8859-1 unsupported, filtering to ASCII...] +> > > > The used 'val' is always a key that is close to the first of +> > > > all keys in the table ('' on the first query and the last +> > > > selected value on subsequent ones). +> > > +> > > This is good stuff. I want to think about it for a day. Sounds very +> > > promising. +> > > +> > +> > Did you see my contribution about this subject ? +> +> I am sorry. I have not seen it, and I am confused how I could have +> missed it. +> +> > I have already implemented above cases and used on trial for three +> > months or more. +> > It is good to be formally supported by PostgreSQL community. +> > +> > And please remember that there are descending order cases. +> > (Moreover there are compound cases such as +> > SELECT * from ... order by key1 desc,key2 asc; +> > I didn't implement such cases.) +> +> Where is the discussion of this? I am confused. You have been using +> code for three months that does this? +> + +Hi all. +I didn't follow all the posts about this thread. +So this post may be out of center. + +I think current PostgreSQL lacks the concern to the response to get first +rows quickly. +For example,queries with ORDER BY clause necessarily include sort steps +and process all target rows to get first rows only. +So I modified my code for ORDER BY cases and use on trial. +I don't understand PostgreSQL sources,so my code is not complete. + +I modified my code for the following 2 cases. + +1.In many cases the following query uses index scan. + SELECT * from ... where key > ...; (where (key) is an index) + If so,we can omit sort steps from the access plan for the following + query. + SELECT * from ... where key > ... order by key; + + Currently cursors without sort steps may be sensitive diffrent from + cursors with sort steps. But no one mind it. + +2.In many cases the following query uses index scan same as case 1. + SELECT * from ... where key < ...;(where (key) is an index) + If so and if we scan the index backward,we can omit sort steps from + the access plan for the following query. + SELECT * from ... where key < ... order by key desc; + + To achive this(backward scan),I used hidden(provided for the future ?)code + that is never executed and is not necessarily correct. + +In the following cases I didn't modify my code to use index scan, +because I couldn't formulate how to tell PostgreSQL optimizer whether +the response to get first rows is needed or the throughput to process +sufficiently many target rows is needed. + +3.The access plan made by current PostgreSQL optimizer for a query with + ORDER BY clause doesn't include index scan. + +I thought the use of Tatsuo's QUERY_LIMIT to decide that the responce +is needed. It is sufficient but not necessary ? +In Oracle the hints FIRST_ROWS,ALL_ROWS are used. + +Thanks. + +Hiroshi Inoue +Inoue@tpf.co.jp + + +From wieck@sapserv.debis.de Fri Oct 16 05:01:03 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id FAA02500 + for <maillist@candle.pha.pa.us>; Fri, 16 Oct 1998 05:01:02 -0400 (EDT) +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id EAA06270 for <maillist@candle.pha.pa.us>; Fri, 16 Oct 1998 04:13:59 -0400 (EDT) +Received: by dsh.de; id KAA11635; Fri, 16 Oct 1998 10:12:45 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma011343; Fri, 16 Oct 98 10:12:15 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id KAA21793; + Fri, 16 Oct 1998 10:09:49 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id KAA01799; + Fri, 16 Oct 1998 10:12:11 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zU2aB-000B5AC; Fri, 16 Oct 98 07:37 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zU57w-000EBQC; Fri, 16 Oct 98 10:20 MET DST +Message-Id: <m0zU57w-000EBQC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: Inoue@tpf.co.jp (Hiroshi Inoue) +Date: Fri, 16 Oct 1998 10:20:47 +0200 (MET DST) +Cc: maillist@candle.pha.pa.us, jwieck@debis.com +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <000001bdf8d4$e4cdf520$2801007e@cadzone.tpf.co.jp> from "Hiroshi Inoue" at Oct 16, 98 04:16:29 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Status: RO + +Hiroshi Inoue wrote: + +> In the following cases I didn't modify my code to use index scan, +> because I couldn't formulate how to tell PostgreSQL optimizer whether +> the response to get first rows is needed or the throughput to process +> sufficiently many target rows is needed. +> +> 3.The access plan made by current PostgreSQL optimizer for a query with +> ORDER BY clause doesn't include index scan. +> +> I thought the use of Tatsuo's QUERY_LIMIT to decide that the responce +> is needed. It is sufficient but not necessary ? +> In Oracle the hints FIRST_ROWS,ALL_ROWS are used. + + I still think that the QUERY LIMIT should be part of the + parse tree and not thrown in by a magic SET command. If + rewriting or function calls turn the one query sent to the + backend into multiple queries processed internal, how should + this QUERY LIMIT variable know to which of all the queries it + has to be applied? It can really break functions and rewrite + rules if this variable is used on all queries while it is + set. + + For your case 3 I think, if there is a QUERY LIMIT in the + parse tree, the (future) optimizer definitely knows that not + all rows will get processed even if there is no qualification + given. So if there is an index, that matches the ORDER BY + clause and it is no a join and the (future) executor handles + OFFSET in single table index scans fast, it could choose an + index scan for this query too. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + +From owner-pgsql-hackers@hub.org Fri Oct 16 12:02:27 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id MAA13063 + for <maillist@candle.pha.pa.us>; Fri, 16 Oct 1998 12:02:23 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id MAA18435 for <maillist@candle.pha.pa.us>; Fri, 16 Oct 1998 12:01:46 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id LAA24469; + Fri, 16 Oct 1998 11:28:54 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Fri, 16 Oct 1998 11:25:54 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id LAA24370 + for pgsql-hackers-outgoing; Fri, 16 Oct 1998 11:25:52 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id LAA24356 + for <pgsql-hackers@postgreSQL.org>; Fri, 16 Oct 1998 11:25:34 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id RAA06506; Fri, 16 Oct 1998 17:28:04 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma006149; Fri, 16 Oct 98 17:27:12 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id RAA00811 + for <pgsql-hackers@postgreSQL.org>; Fri, 16 Oct 1998 17:24:37 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id RAA04532 + for <pgsql-hackers@postgreSQL.org>; Fri, 16 Oct 1998 17:26:54 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<pgsql-hackers@postgreSQL.org>> + id m0zU9N0-000B5AC; Fri, 16 Oct 98 14:52 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for pgsql-hackers@postgreSQL.org + id m0zUBum-000EBQC; Fri, 16 Oct 98 17:35 MET DST +Message-Id: <m0zUBum-000EBQC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: [HACKERS] SELECT ... LIMIT (trial implementation) +To: pgsql-hackers@postgreSQL.org (PostgreSQL HACKERS) +Date: Fri, 16 Oct 1998 17:35:39 +0200 (MET DST) +Reply-To: jwieck@debis.com (Jan Wieck) +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: ROr + +Here we go, + + this is up to now only for discussion, do not apply to CVS! + + Those involved into the LIMIT discussion please comment. + + Here is what I had in mind for the SELECT ... LIMIT. It adds + + SELECT ... [LIMIT count [, offset]] + + to the parser and arranges that these values are passed down + to the executor. + + It is a clean implementation of LIMIT (regression tested) and + the open items on it are to enable parameters and handle it + in SQL functions and SPI stuff (currently ignored in both). + Optimizing the executor would require the other sort node + stuff discussion first to come to a conclusion. For now it + skips final result rows - but that's already one step forward + since it reduces the rows sent to the frontend to exactly + that what LIMIT requested. + + I've seen the queryLimit by SET variable stuff and that + really can break rewrite rules, triggers or functions. This + is because the query limit will be inherited by any query + (inserts, updates, deletes too) done by them. Have a rule for + constraint deletes of referencing tuples + + CREATE RULE del_table1 AS ON DELETE TO table1 DO + DELETE FROM table2 WHERE ref = OLD.key; + + If the user now sets the query limit to 1 via SET and deletes + a row from table1, only the first found record in table2 will + be constraint deleted, not all of them. + + This is a feature where users can get around rules that + ensure data integrity. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + +begin 644 opt_limit.diff.gz +M'XL(`$]=)S8"`^4\:W?B1K*?R:_H82=98&1;$F^<>`^+F1DV&!S`N<DGCBP: +M6SM"(I*PQSOQ?[]5_9!:(`$S<1Y[+V<&I.[JZJ[NZGIUM1?.<DE.[("$@7WJ +M!\[=V:UE?Z#>XLSV5RO+6X3RX=1&F#W57U4JE6/0%-X&#AG;$3$:Q#`Z]6JG +MVB)&N]WZZN3DY$`?J;9FI][HU&J\;27]88.IMC6H9J^(&E_K!)Y.OB+D;XYG +MNYL%)<5-Y+CAV<K^&)W>%S-JUGX066YV7?@4VI9]3['VC5(;1H'CW?$V\.^L +MPKI5/U!**H7"-4/>_TCM3>0'[ZFUOJ(K/W@B8;19+C.I,G1#,_1&0I>AFU#0 +MDI05'"\J%`I+:D6;@)YCR0\;&CQ=TM"&/G^1SZR&]];SO8A^C(C-?\^!E@*4 +MA8#'=5:VO_&B<TY)(9N4`M`26IX3/1&8#?M#F#URHZH91DL9N5'7C&HM'OGS +MGD[>L$YZ`9!%B85##2/B^3#;R\!?D>B>DCOG@7J$#9<\6.Z&\D8YN,[P9T57 +M(8U*WT@R-:)K)'3^0_UE29:5RVQ"Y.MI]+2FA<)W9#9G<Y2N9.-B$-^1P6A6 +M&P\N,P!<ZD&]Z`?6JU;.`&(D`%CI$E9R51;+L`/FA-[&=0'N;7<X[6<`W#X! +M)ASOY*:?V1YF8$]S!U@\3`$@3#X?W-&(K0;?-\GJB'?/6E$.F8/@+)MWF@W- +M:)F<=P`N=^/`?*68NHS0`OP=C1+($A]0F7'V*U*0N"8;KQ1O$HW$+:<1<)YL +MI!&QOS3.;V6Y/YPE*2THL.9WWY&1[]$R;$68JZOQCWTD#<>R7@:4)EU`4[87 +MVK"+V\DN_NL06!KA+JN41S?#8?(6;YG?1OHB5P%1,5SV<&4YWI8&RJC/5D$9 +M@,?KH$.-&QU#[]1;^4JH4=,:[43DP6M3EXL<PHP[-GGPG07I>XMKU_)*^$4J +M:_C62)\M":E0!*1LHD63V6;MTIEUZ]*IZT>DPM>6,@SI5K#`"4:V"%P=0(D5 +MA'0&"Z*1WFHQ`YE%_#4-`+\/D&\0$D03\9=+$!"LPU`@P&)OL[JE@2@F4]OR +M+IV`VMB8+.23@&<$EBIK4(K1<N/995(JJ]3P"0`2)A04)WV@I6WZ0OC62!8> +M+5M<-$#5R'E'5;LC;#[WP[`@%V\/#;:6NK,275M1-MGVFN`,QAL,7_A6`O2? +M<,K$>A0*\8(D>KR``XG73@@/8"LCX:N_-KE<@(#XX)RE%'SY)&2K##"4FJJA +MU`01VVS'(G:;M("&&Y=9.F#],(OS$L49RC16B.Q7*)0J6.!X;$3(@4QG,J.+ +M$P026D_*N#G"BJ1QL_,YWH)J`TEME:1V33/K"4E\PD\N:#A?![Y-0;<O9.=J +MI0LJ'3<3&"D>6`?.8NPLV`#?9`^06U\@X;F-)76\(-A?LK?AX&HP([9K;4)A +M>N4@8J87JHN8"<@KT!F@7\I8\XD)'V&`5FP.P684QL9^KJW`6D'=6JUC:-E3 +M^.A$]CTIH7DXL^Z2;LH,O^B@`'8-E59<AQ<5['@-2ZP<-%W<^ES`)""PPN62 +M:')RD5AM90EZ"_;J!VDSQ3VRX<L>J>O?E?J3R7BBD>(::VA$`V*%!#IVI/P% +M4S<BS@H8=D5![2_($XV*>=TLZ-("3L[L8>/1CVN0SH""6<]HJY*O%]O]%362 +M,7N\OV?9$2ZA&-ZW1.=SF^XM10+H"*3BEA*/WL'V>:""@N?8J#R:\_BV^BV, +MQS'LX;O$X-YA.Z5J+]<)_^$@TTDAH?*<TD<B103'L=??B^%X7W\8O['NTNRF +MNEUI;N-CV\ML'&0_KX$PE*LEU!*SHE'MY,C=MF;J]=C[$!^B&%6B(+:IU%+% +MK'J5E':'P_GLYGK8GZ:`W_K!HQ4L4A95"D!5/^>\0JP':@2S86IF(]$(+S)4 +MH:G5(NXNO]#`L^;<U%N::50376>"^C:;K1>@[,TN9>0%*<NDQH3!FTV%FE93 +M,]O5/X&:?X)K\YO)J<+H:Z827-.K6E5OORPY>@IF/.KS_?+2:],`JZJA6E7- +M>DMK-FH*,8=]*%[\6QTI7OP%WE0F8<V:J35K"M.U#%UKF<HJ?>(HN>5+N"Y, +M;\V4",Y1T*BA81U`A#^21]3-J#<BZCX1[CY3'@#".02CFH0?G+5L)GJ.&/D$ +M1'Q`P8\(45W</@G%SB?N-.X)5M,%W-!3>.]OW`6)8"DBGRQ\`O+?@1FC%.:? +MN.!)NJ%L!KC7?A@Z8.Z3TF*#`5J`7="/)(3I+DNP$_(ORQ,O><2>;5D_?/'( +MA51+0M6?G*C50G=BC-7Q-CPP&T<\,V?VB-@(KC^R1I@7'%$`#D1'%,@O"(_D +MM:YV=*.CF_GQ$5/70;4VI&K%R1`J&<V=87<*ULMX.OOAIC^<]\975]W198F& +MY3+Y!^G_U._-)_T9"`;2$6\W(QGN"B@@\5+.*@U/+GY9:`1_I6L:NZ4ZTTEB +M093`!)?68I1BX_QYH\P.QC$3-F?X1W!1N';R^(=5'>`<!I/!,^T#/+/;#KBE +MU3';^=S2!%'=3(EJ5M",)=JM[[NP-4,921IXD3^A+I/%L%1+RPWYUK/OK8!% +M$4"*^B.P@57IQX]0`K%I<XY"I`49RWIA0V:-NV76M5955R2QV=!:R7$4ZXUQ +MPO1Z,&<&[7Q\?3/Z?C3^'\8K7%*\(?*3?402?RK\YXCCDBA]7J)@V(O_3'D[ +M_A!%?E)G*0E6DCI4V0&.SU8DL'+`D@WL`H$2>/NT);L%FP?10AR\1+$GEMV$ +M'\#P)O$9238H.XJ1E(KCF#RDZ#$?A90?T&R!RH`\DQ\L(H\B@D?DF2)>!Q1] +M/[8O1'1><"!C>SSK7()?Q_D1A-;-#%AR.NO.IMD,7@,&KQN)AZ2P\_C[>>]F +M,AU/$D;./XX0`H_)R;?HUD7I4X8Y8K0W04#1`U:#7!FAKW,Y#?$>Q:GH75W. +MI_UAOS<CWWQ#XKY/+N3I!711YKNT`?92HY'LTA>CZMA#E3^,W'PU@5(#S^#7 +M3ZCDMRV-G=IL9;$#MJLO:GJVOMC?%/2,V:G6\U6&46^V-*/>4CQ)6:0LK$<? +ML9^3BXT'T]9CH228Y(BNUG/7$0%@(8,+N%H@I]9/)12F&A&--1[1&/-@V?EA +MR)Y8:KY=!6\)D%B;'UH8?Q/EKTM2N6]9$JC/796<EK`H>J>N[['ZZFW-;"C) +M&;P@<>ZM]1IZF;(\BH&W]$&0!1J/K9U<W%OA=',[=+P/(1A8Q2C8T"+85D6F +MW8O\:"D'`\"I*USDL'.@`Y>*P^PP`E_+7(3*JA,19<I"N,,<^Q$RYCB(KQ=+ +MB\\R_?CJ@76PR.<=I78?\RA@G\L]>4V!?8Q.K;IG3[/C/363!`N:B<<>^=`- +M[%\W7,-CB4O9;\`<N(ONR^=,"Z(7FF8&K@9=W[;<>98PP+()#+F$#%>6QR5' +M=Z6R"7<AU:[4VMVNWGQ!5YR!<GKJB8!R1D>)*$I:'2&-6"@D.+/`.7_Z#]WB +MJ.W*;(;:ALK@)R.;G_:VK()+T3&;>]BII6M&2SU6PX*VKKI[,I0^I2ZUHVFT +MBCHBA@/Z]E6IE)2#'D^B7&50VBRC`O->1-!:!%V2J$L46%ZX](-5@J2T%B9# +M+F(9B^=8MAEH:T#J>!3`3!R2-0ZAZ"D'%,^<)`KR-Y>XWB8(_>!8XM08U$&F +MNPNLU>E3%D?PFKWLQD$R>,W<RVL9S>H=L[HW(Q&,Y+KB!.*KL9N1:%O`+_[= +M&?PBUV2G'N+)QW:-9:,=>/;1LJ/MC$2)<WTW1P\J3DT45O[5S7`V^.?/LWX* +MW^H6X1_15<8&1R1:%`JPQ-&<'\6!V0LOMT_,@-)XL&W.#IQ"^<9KD$K^R%"@ +MJ12C8"\<S%]'<RL(K*?Y+;#>(D1S*MPJ8@CHQW4@VEA1%``@<.,\LH([&HGR +MK0*3GVW`5"A]P1#CT"NY"_S-.A[5#/CHC@9O07EU@[N0S^77S#?]%N7E10'U +MVIS+T:0*HQ/4\BX*L/?FT(62O&$DMNC_RRGDK;%-R&3"G,F8+Y[8[%,\3+%K +M;F5/Z(8BY@4Z!]QTZ(E&]OW\WG^<KRSOB:O[U%#4D<YE!"7!@M-P41BOHRG] +M90C/20U,$G7CNKY+5WDGCV:CJ9G-:K*_F(<_O];(V_ZL]QY^AN/N#'[0J82O +M_N#="!XFXROX1ON`-7HWZ8X`Z-UD?`,MWW=_'(S>P>_X9@*8&,0`6@U&HSX6 +MP,.T/YH.9H,?^_QE,L/?67_R8W?(GL;P/>5+]J\QMOV^_[-&AMW1NYON.V@T +M['<O61_#_EMH.QQ\CX7C7E<,Z*K+!G\U&-W,9X]'L/3Z,NE?R0'34G0W& +M(^P1GFXF[*'WO@N$COH_`=;1&/_C`Q#*VMY<]2>#'F\]?JN1\0C_#V%HXVO$ +M!;\3_'_9QQ_HFM'/#Q<-F.<DCOA?/<]X:O+7G>X<?]#`1*9&RB.L@TNH*X*Q +M0(15H8@W\GA/`RI>!)0J:,B]]0#^51J`V?420!&WW)@A&5*HH-AM!=5J07M\ +M97V@S#M+*J0%4RAXS(WX98,>Q&LSYSBUH>.IO:Z>#\,;%)EFZDC50^<7Z8F= +MDM>MK9X4A^5U6ZU#.I,J0Y?&'[-@7QL&RX49#,NR^%-\%.OMN"?1?4G7`(D1 +MFZ$)5.Q:`)"Q!?0L'YBE>+@C'I'/ZR%5^RQ)??T:C5<14?/D#/#JO.G'_(ZF +MKL8CF@;HAJ8:H?^5=*<]>:Q<^$18/\5OB^?D6=1?]C,`+A2`LTK_ZGKV<^4L +M"\M91>315,X(GR?A]VVS8Z?`ST5WU0_YN_9WDJF59$\N#SJ4Y"\L."Q1K0Q? +M9OE<]/LKR>O@$"869=["!5XIHUJXHJ(Y@)^GR=SMK5,8L`BW=)OX2O/CF/3& +M8V4QFWDB-2KC'H92FW,)0X'8NH'QVM@&R+A[H=3N7+Q(M=R^=9&JW+YRD<77 +MPO'BD\PRO$9^>J9R,KW`&1.Y5]SBVYOK)?>-N,ATV!?[0)\>_6`19D<`DMJ] +M/ED"]ME^64[3.K3KF'M"D@9L>,-,3C`^%9E]740]?-G_Z5F3A:!OG"ADY>]! +M_\VF295'@V*LWZ'XA!?#L**BU/`)=$B]T&&I:2E30`&(J+7@E3-0]$D%K"*P +M5C$Q%I0JGQ>/GS5QQXJ3)<782Y!U</QO]M/]&\G*7#VSI1E5-<;/"FH*W>`@ +M+$!S%F.K26)WZ3(J<A,J+G(^(%5H3TERV(XI"NLJ@0-B/%8*M(SB8I\1"";C +M95)D,]*850:%^?LHH(^P$E3^7M*EXVW'TW)@LO=4#G#&SFID[ZQC$$#K5J>^ +MYQRF:IA:U5`L'%9@*AEP]]1"J\T/:8D^@"0"+\]-L@5%FHQ(P1$:RO'(@T,? +MB<.%6+A98[2-+G82==BQ5]JZ$*F_Y-=?B5K;V\D+WA*D:K9QNE<YG+`8AVQQ +MQ(2/^/3TE*4_+47^$I[!!QN7DM4FY)FJ()\7!'-%$`D+_8BV_'PVY"E?)9E0 +M.T<0&!"V^!IW+I\U__;?)Q<P=2SBN"]X%MG^^FS-:-]BKU1--E.E0#)8J9G- +M2OG-:AW=[%0;>X)GNE:+\X24R-;V+6$9VA(G(1B](A6>"3&6YZ*8:,QR\N(" +M,!5@LEZEKB1=\X/5^(Y+YFT7R9S9]WP_^]X-BPLR2G?C@G\RI8>O[KSL5&2G +MBX%3:!B_Q\4N]>3LU9%K+^\J95Y*PEN<KN7Q8G@0"=J"@M_AKM874'#\=:PC +M2<P4_55=JU;CO8MIEL2RHPT3@\'&B]-#3T\/7`T^+L-B<C.2^7SB.GEH/5"0 +MSDL_9-9OWUN(&UY"MMZL%]!<E+&SV"2#E^Q<F]*RDB_$/5Y)JW(C^O>G55F] +MG0R2/XSZS)5O5K5JLYF$+YD*FR/])7['E3%3%)_Y,'>#!0<PYK7#PFFI^\QG +M7/01VQ$OU8?2,&4Y[):KI_"[^E8(\/1=8GPXO6>Z<4]]6O?N`=S5P/4M8^[8 +MQF9'KW6,/:>EK:K64F_6`@]$/KK:(<\MC%.!T1Q2+F=+*44_@IWO\8NE3!I) +M+I]&5A`==:.4Z2\5S\Y5Z!>XC'N>C#6^H(R(8?L<.\@8`=[*9P-E6%BT(H!9 +MB<(25]K@&[@TT$B<J@KVFZ:T8OGQ924M[X>;_N3G.3-%>:8:6Y1$I_S?6I1C +M[([_BJ7*%PX\(X8)%O:X)1MVJ[-%PRY<AF1H94N&`VVKG6J]4]V3RM_0M49= +M^5,'\-I44RCP#(Q9"DJ4^ASU$WL/B1504%_>!W""-MZ"!EQ5!O3!\3>A>J6G +MPATVX=\A+S"T:F(#HOT;W@]-7S")?)XAD]62YS,4\EN*K!BV7X1F93$+<.>P +M&G6-!Z/F.C4F]M8**7JR/(T0&V$)D;XMP>*\OWK2J+:UAOHWC!HU`PJ2-+DX +MBSU),N$4*']QI62SQ(LRCM'F">%\B#Q7_A;<T>#IG*<&6X2_LHNFC+*R0/./ +J5".V8K`1>&?LC3PZT;V_B8@X[<#CAC]SA9Y)<AHC_9+_!=<!%F+O2@`` +` +end + + +From oleg@sai.msu.su Sun Oct 18 14:01:49 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id OAA01739 + for <maillist@candle.pha.pa.us>; Sun, 18 Oct 1998 14:01:48 -0400 (EDT) +Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id NAA23532 for <maillist@candle.pha.pa.us>; Sun, 18 Oct 1998 13:51:14 -0400 (EDT) +Received: from ra (ra [158.250.29.2]) + by ra.sai.msu.su (8.9.1/8.9.1) with SMTP id UAA17832; + Sun, 18 Oct 1998 20:45:25 +0300 (MSK) +Date: Sun, 18 Oct 1998 21:45:24 +0400 (MSD) +From: Oleg Bartunov <oleg@sai.msu.su> +X-Sender: megera@ra +To: Tom Lane <tgl@sss.pgh.pa.us> +cc: Bruce Momjian <maillist@candle.pha.pa.us>, pgsql-hackers@postgreSQL.org, + jwieck@debis.com +Subject: Re: [HACKERS] SELECT ... LIMIT (trial implementation) +In-Reply-To: <2292.908726689@sss.pgh.pa.us> +Message-ID: <Pine.GSO.3.96.SK.981018213213.17519C-100000@ra> +Organization: Sternberg Astronomical Institute (Moscow University) +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=US-ASCII +Status: RO + +On Sun, 18 Oct 1998, Tom Lane wrote: + +> Date: Sun, 18 Oct 1998 12:04:49 -0400 +> From: Tom Lane <tgl@sss.pgh.pa.us> +> To: Bruce Momjian <maillist@candle.pha.pa.us> +> Cc: pgsql-hackers@postgreSQL.org +> Subject: Re: [HACKERS] SELECT ... LIMIT (trial implementation) +> +> Bruce Momjian <maillist@candle.pha.pa.us> writes: +> > What if someone wants the rows from 500 to the end. Should we allow +> > the syntax to be: +> > SELECT ... [LIMIT count] [OFFSET offset] +> > LIMIT and OFFSET are independent. +> +> I like that syntax the best, but remember we are not inventing in +> a green field here. Isn't this a feature that already exists in +> other DBMs? We should probably copy their syntax, unless it's +> truly spectacularly awful... +> +> regards, tom lane +> + +Mysql uses LIMIT [offset,] rows +>From documentation: + + LIMIT takes one or two numeric arguments. A single argument + represents the maximum number of rows to return in a result. If two + arguments are given the first argument is the offset to the first row to + return, while the second is the maximum number of rows to return in the + result. + +What would be nice if somehow total number of rows could be returned. +This is often needed for altavista-like application. +Of course, I can do +select count(*) from sometable ... LIMIT offset, rows +and then +select ... from sometable ... LIMIT offset, rows +but this seems not elegant solution. + + Regards, + + Oleg +_____________________________________________________________ +Oleg Bartunov, sci.researcher, hostmaster of AstroNet, +Sternberg Astronomical Institute, Moscow University (Russia) +Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ +phone: +007(095)939-16-83, +007(095)939-23-83 + + +From owner-pgsql-hackers@hub.org Sun Oct 18 14:31:12 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id OAA02288 + for <maillist@candle.pha.pa.us>; Sun, 18 Oct 1998 14:31:10 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id OAA24844 for <maillist@candle.pha.pa.us>; Sun, 18 Oct 1998 14:15:35 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id OAA26655; + Sun, 18 Oct 1998 14:00:03 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Sun, 18 Oct 1998 13:58:57 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id NAA26381 + for pgsql-hackers-outgoing; Sun, 18 Oct 1998 13:58:55 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id NAA26367 + for <pgsql-hackers@postgreSQL.org>; Sun, 18 Oct 1998 13:58:49 -0400 (EDT) + (envelope-from oleg@sai.msu.su) +Received: from ra (ra [158.250.29.2]) + by ra.sai.msu.su (8.9.1/8.9.1) with SMTP id UAA18077; + Sun, 18 Oct 1998 20:58:41 +0300 (MSK) +Date: Sun, 18 Oct 1998 21:58:41 +0400 (MSD) +From: Oleg Bartunov <oleg@sai.msu.su> +X-Sender: megera@ra +To: Jan Wieck <jwieck@debis.com> +cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org> +Subject: Re: [HACKERS] SELECT ... LIMIT (trial implementation) +In-Reply-To: <m0zUBum-000EBQC@orion.SAPserv.Hamburg.dsh.de> +Message-ID: <Pine.GSO.3.96.SK.981018215259.17519D-100000@ra> +Organization: Sternberg Astronomical Institute (Moscow University) +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=US-ASCII +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +Jan, + +I tested your patch on my Linux box and it works ok, except +aggregates functions doesn't work properly, for example +count(*) always produces 0 + +kdo=> select count(*) from work_flats limit 10,1000; +count +----- +(0 rows) + +while + +kdo=> select rooms from work_flats limit 10,1000; +rooms +----- + 3 + 3 + 3 + 3 + 3 + 3 + 3 + 3 + 3 + 3 +(10 rows) + + + Regards, + + Oleg +_____________________________________________________________ +Oleg Bartunov, sci.researcher, hostmaster of AstroNet, +Sternberg Astronomical Institute, Moscow University (Russia) +Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ +phone: +007(095)939-16-83, +007(095)939-23-83 + + + +From wieck@sapserv.debis.de Sun Oct 18 15:17:53 1998 +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id PAA03203 + for <maillist@candle.pha.pa.us>; Sun, 18 Oct 1998 15:17:49 -0400 (EDT) +Received: by dsh.de; id VAA01180; Sun, 18 Oct 1998 21:19:50 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma001117; Sun, 18 Oct 98 21:19:33 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id VAA25465; + Sun, 18 Oct 1998 21:17:29 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id VAA14993; + Sun, 18 Oct 1998 21:19:58 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zUvyS-000B5AC; Sun, 18 Oct 98 18:46 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zUyWO-000EBPC; Sun, 18 Oct 98 21:29 MET DST +Message-Id: <m0zUyWO-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] SELECT ... LIMIT (trial implementation) +To: oleg@sai.msu.su (Oleg Bartunov) +Date: Sun, 18 Oct 1998 21:29:43 +0200 (MET DST) +Cc: tgl@sss.pgh.pa.us, maillist@candle.pha.pa.us, pgsql-hackers@postgreSQL.org, + jwieck@debis.com +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <Pine.GSO.3.96.SK.981018213213.17519C-100000@ra> from "Oleg Bartunov" at Oct 18, 98 09:45:24 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Status: RO + +Oleg Bartunov wrote: + +> On Sun, 18 Oct 1998, Tom Lane wrote: +> +> > Bruce Momjian <maillist@candle.pha.pa.us> writes: +> > > What if someone wants the rows from 500 to the end. Should we allow +> > > the syntax to be: +> > > SELECT ... [LIMIT count] [OFFSET offset] +> > > LIMIT and OFFSET are independent. +> > +> > I like that syntax the best, but remember we are not inventing in +> > a green field here. Isn't this a feature that already exists in +> > other DBMs? We should probably copy their syntax, unless it's +> > truly spectacularly awful... +> > +> > regards, tom lane +> > +> +> Mysql uses LIMIT [offset,] rows +> >From documentation: +> +> LIMIT takes one or two numeric arguments. A single argument +> represents the maximum number of rows to return in a result. If two +> arguments are given the first argument is the offset to the first row to +> return, while the second is the maximum number of rows to return in the +> result. + + Simple change, just flip them in gram.y. + + And for the 500 to end: + + SELECT ... LIMIT 500, 0 (after flipped) + + The 0 has the same meaning as ALL. And that could also be + added to the parser easily so one can say + + SELECT ... LIMIT 500, ALL + + too. + +> +> What would be nice if somehow total number of rows could be returned. +> This is often needed for altavista-like application. +> Of course, I can do +> select count(*) from sometable ... LIMIT offset, rows +> and then +> select ... from sometable ... LIMIT offset, rows +> but this seems not elegant solution. + + Absolutely makes no sense for me. As said in the other + posting, aggregates do the counting scan in a deeper level + and thus cannot get limited. So if you invoke an aggregate, + the whole scan is always done. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + +From owner-pgsql-hackers@hub.org Sun Oct 18 19:08:47 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id TAA00573 + for <maillist@candle.pha.pa.us>; Sun, 18 Oct 1998 19:08:46 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id QAA01305 for <maillist@candle.pha.pa.us>; Sun, 18 Oct 1998 16:14:30 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id PAA06110; + Sun, 18 Oct 1998 15:55:20 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Sun, 18 Oct 1998 15:54:07 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id PAA05771 + for pgsql-hackers-outgoing; Sun, 18 Oct 1998 15:54:05 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id PAA05753 + for <hackers@postgreSQL.org>; Sun, 18 Oct 1998 15:53:52 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id VAA09240; Sun, 18 Oct 1998 21:56:10 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma008902; Sun, 18 Oct 98 21:55:19 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id VAA28158; + Sun, 18 Oct 1998 21:53:16 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id VAA15349; + Sun, 18 Oct 1998 21:55:45 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<hackers@postgreSQL.org>> + id m0zUwX6-000B5AC; Sun, 18 Oct 98 19:22 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for hackers@postgreSQL.org + id m0zUz52-000EBPC; Sun, 18 Oct 98 22:05 MET DST +Message-Id: <m0zUz52-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] SELECT ... LIMIT (trial implementation) +To: terry@terrym.com (Terry Mackintosh) +Date: Sun, 18 Oct 1998 22:05:31 +0200 (MET DST) +Cc: hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <Pine.LNX.3.95.981018155322.29282B-100000@terry1.acun.com> from "Terry Mackintosh" at Oct 18, 98 03:58:57 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +> +> On Sun, 18 Oct 1998, Tom Lane wrote: +> +> > Bruce Momjian <maillist@candle.pha.pa.us> writes: +> > > What if someone wants the rows from 500 to the end. Should we allow +> > > the syntax to be: +> > > SELECT ... [LIMIT count] [OFFSET offset] +> > > LIMIT and OFFSET are independent. +> > +> > I like that syntax the best, but remember we are not inventing in +> > a green field here. Isn't this a feature that already exists in +> > other DBMs? We should probably copy their syntax, unless it's +> > truly spectacularly awful... +> > +> > regards, tom lane +> +> None that I have used (VFP, M$ SQL Server) that had 'LIMIT', had 'OFFSET'. +> So it would seem that the very idea of OFFSET is to break with what others +> are doing. +> +> I too like the above syntax. +> Why mimic, when you can do better? Go for it! +> + + We have a powerful parser. So we can provide + + ... [ LIMIT { rows | ALL } ] [ OFFSET skip ] + + or + + ... [ LIMIT [ skip , ] { rows | ALL } ] + + at the same time. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + + +From owner-pgsql-hackers@hub.org Sun Oct 18 19:08:39 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id TAA00557 + for <maillist@candle.pha.pa.us>; Sun, 18 Oct 1998 19:08:37 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id QAA03555 for <maillist@candle.pha.pa.us>; Sun, 18 Oct 1998 16:56:03 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id QAA10374; + Sun, 18 Oct 1998 16:36:26 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Sun, 18 Oct 1998 16:35:16 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id QAA10298 + for pgsql-hackers-outgoing; Sun, 18 Oct 1998 16:35:15 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id QAA09974 + for <hackers@postgreSQL.org>; Sun, 18 Oct 1998 16:32:21 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id WAA18249; Sun, 18 Oct 1998 22:34:46 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma018115; Sun, 18 Oct 98 22:34:11 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id WAA29950; + Sun, 18 Oct 1998 22:32:01 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id WAA15581; + Sun, 18 Oct 1998 22:34:28 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<jwieck@debis.com>> + id m0zUx8Z-000B5AC; Sun, 18 Oct 98 20:01 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for jwieck@debis.com + id m0zUzgV-000EBPC; Sun, 18 Oct 98 22:44 MET DST +Message-Id: <m0zUzgV-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] SELECT ... LIMIT (trial implementation) +To: jwieck@debis.com +Date: Sun, 18 Oct 1998 22:44:15 +0200 (MET DST) +Cc: terry@terrym.com, hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <m0zUz52-000EBPC@orion.SAPserv.Hamburg.dsh.de> from "Jan Wieck" at Oct 18, 98 10:05:31 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +> We have a powerful parser. So we can provide +> [...] + + This version now accepts all of the following + + ... [ LIMIT rows ] [ OFFSET skip ] + ... [ OFFSET skip ] [ LIMIT rows ] + ... [ LIMIT [ skip , ] rows ] + + rows can be a positive integer constant greater that 0, a $n + parameter (in SPI_prepare()) or the keyword ALL. 0 isn't + accepted as constant to force ALL in that case making clear + that this is wanted. In the parameter version the integer + value 0 still is used to mean ALL. + + skip can be a positive integer constant greater or equal to 0 + or a $n parameter for SPI_prepare. + + If any of these syntaxes is used in SPI_prepare()'d plans, + the given tcount argument for SPI_execp() is ignored and the + plan or parameter values are used. + + Anyone happy now? + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + +begin 644 opt_limit.diff.gz +M'XL(")%0*C8"`V]P=%]L:6UI="YD:69F`.4\:W?;-K*?U5^!:-.NY-"V*-EZ +MN?$>5:83;67)U2-MSSWWZ-`29/-&(E62BN--_=_OS``@08F4E4>;[JY/&Y'` +M8(`9#&8&@P%GSGS.#J<^"_SID><[M\<W]O0M=V?'4V^YM-U9H!Z.I@BSH_J; +M@X.#?=#D+GV']:<A,ZO,-)NGE6:ESLQ&H_[-X>'A$WTDVI:;I]7FR8EH>Y#\ +MH\%4&@94TRNBQM=3!D^'WS#V-\>=+M8SSO+KT%D$Q\OI^_#H+I]2L_+\T%ZD +MUP4/P=2>WG&L?:'5!J'ON+>B#?QW?$#=ZG]0R@YRN6M";KWGTW7H^:^YO;KB +M2\]_8$&XGL]3J3)+IF&6JC%=9JD,!75%6<YQPUPN-^=VN/;Y&9;\M.;^PP4/ +MIM#G;^J9:D1O;<\-^?N03<7O&="2@[(`\"R<Y=1;N^&9H"273DH.:`ELUPD? +M&'!C^C9('[E9,4RSKHW</#7,RDDT\L<=G;R@3MH^D,69C4,-0N9ZP.VY[RU9 +M>,?9K?..NXR&R][9BS47C3)P'>//DB\#'A:^4V0:K&2PP/D7]^8%558L$D/4 +MZU'XL.*YW$LVFA"/DI4T+H)XR3J]T4F_<Y$"L.`NU,M^8+Y.BBE`1`*`%2Y@ +M)I=%.0U;8$[@KA<+@+ML=8=6"L#-`V#"\0[&5FI[X,".Y@Z(>)``0)AL.;CE +M(<V&6#?Q[,AWUUYR`9F!X#A==FI5PZR7A>P`7.;"`7XEA+J(T!+\%0]CR((8 +M4)$D^QG+*5R#M5N(%HG!HI;#$"1/-3*87%^&D+>B6A_.G!5F'$3SY4O6\UQ> +MA*4(O+KJO[&0-!S+:NYS'G<!36DM-&`5-^)5_-<AL-##5790[(V[W?@M6C*? +M1_HLTP!Q.5QZN+(==\,"I=2GFZ`4P/UMT!.-RZ5FZ;1I-K*-4/7$J#9BE0>O +MM9*:Y``X[DS9.\^9,<N=72]LMX#_L(,5_&LPBZ:$'7`$Y,1HV62T7BWXR+Y9 +M\.'""]F!F%M.&)*M8()CC#0)PAQ`B>T'?`038K#V<C8"G<6\%?<!OP>0+Q`2 +M5!/SYG-0$-1A(!%@L;M>WG!?%K/AU'8O')]/L3&;J2<)3P06#E9@%,/YVIT6 +M6:&H4R,8`"0,.!A._HX7-ND+X%^#I>$QTM5%%4R-XCN:VBUE\[%_A`6E>'-H +ML+3TE17;V@-MD6W."7(P6F#X(I82H/^`+)/SD<M%$Q+;\1P.))H[J3Q`K,Q8 +MKO[:Y`H%`NI#2)96\.E,2#<9X"C5=$>I!BJVUHA4["9I/@_6"_)TP/LAC_," +MU1GJ-"I$\<OE"@=8X+@T(I1`LIGD=`F"0$.7XC+ACE"1<FZV_O;WH!I`4D,G +MJ7%BE.LQ28+AA^<\F*Q\;\K!ML]4YWKE`DPZ+B9P4ESP#IQ9WYG1`%^D#U!X +M7Z#AA8^E;+PDV)O36[=SU1FQZ<)>!]+URD!$KA>:BT@(V#.P&6!?BECS@92/ +M=$`/I@*".)J[MGU["86KK<*N$X0==^[E5O36$54T!8YR6G+!O1-.[U@!_<:1 +M?1OW7Z2.9<\Y<'BX<N^:HB@WC2:W0.5@`J/69Q(F!H%^BP79Y/`\=N>*"O0& +M'-FWT;A4CT2)ZG$5HZ/RM!XEL0"CSSP63A;`D(D#'(E[(9Y'332.XQ]?>+<% +M:S#H#PR6)R`><I_-/9]!MXXR!.!SA[">F3*,I,-Y/B(,X0L.R1R37?V/\[]' +M;QUWAI-\W1JTKB:=WIM6%SQCYKQXH0;P00U$&V34\J5JV1M?L>^^TS$[5+M2 +MO*8:9Q:1%7,:7QZW&;'=AQS=E^;,1I_";T_O)($63"MBON$T7V)G$V/=D#FM +M@UC@TF5NQN<VZ#LE;HD!K%W^?@4V'+0'[;%P1\.^G3$[2)"<-UC*4A)#>U0= +M(=URE-^SDB1X'W)=?@M*]ITB]C':>NRMGX3R_1SU)##LT$[QMBQ63IMEGZJ; +MY/;S2=6D;(RNF;0QQ$9(ZB5Z_62U%"'3M%*BMS]3*8G!_$5UDN3SUU!)>_'E +MHS62P+I;(25E[<_21]1K4AWIP9ND-A)#W*F,-BE-TT7@4JE%*YU;VHNC\YKA +MO36,<NDTBF'(/Z9MS61!M#/32[7-V;.XM-7M3D;CZZXU3`!?>OZ][<\2^[($ +M@.[$GHD*.1_H5Y8;9:/<B/W*+S)4Z>_K12+H]H4&GL;S<JENE,V*%@4NE8V* +M%BW]=,I>;%/&OB!EJ=2485K*-8T:LV94RI6O0,T/]O3M9Y-3J1GED[)&3J5B +M5"J-+TM.*0'3[UEBO7SIN:G"WJRJ[\UJ];I1:YQHQ#P=B1'%GQN.$<6?$)-) +M):QV4C9J)YK0U4]*1OU4FZ4/`J78/S-API-+,Z&",QPX].!@'D"%W[-[]-V6 +M0$S(%P_2?G$11D8>PM:<!6^=E6HF>PZ)?`8JWN>_K6'&P%S</$C'3S#N*.H) +M9G,!N*&GX,Y;+V8LA*D(/3;SF$-6<\8Y\)\M^#N^"%0SP+WR@L"Y67!6F*WQ +MF`=@9_P]"X#=105VR/YIN_(EB]CC#>]83!X[5V9).A^'AWJUM+)X4N.X:W&\ +M$YV;I')VCP@KSC^*1I`58M4`GHBQ:I"?$&3-:MUHELQFJ9P=934K)<.LG&J! +M$2K84O,Y$9D6;!/>*<BK.%5282T1!R^J<`C-$,6V<(4>GI-_T$]$+=COO[-- +MB/;6QF'#S]"W(^*49/A3ET4<(-_M`?IP<!DLN0O"+)P/&CL.RK7]6RDOVBH4 +MQW].ECDL@0M252X(MI"N"WILW=80G/W^</33V.I.VOVKJU;OHL"#8I']@UF_ +M6.W)P!J!`F5-^3;NJ<,%GP,2-Q$:Y,'A^6\S@^&O"@1&0<`2\5<*KA8&%MY' +MJ0*CC!3,UQME^M$'S6C&\/=8;<'*R5IG5/7$"B.8E+75>&)M;;=K-,NGS4HE +M>U75P*35$B:-"N)5=>-Y"Y"U0,7M.V[H#?B";!9,U=Q>!$)%3>]LGV*V()U> +M#W8KNI40$NM+Y29WUB&Y')-I=,2*S*=PH-A>J-:Z%Q[92^F'I]%4+Y\:]4I) +MLV;EFE&OGFJ:0DK)\+HSH<4ZZ5^/>S_V^C^3'`EM^V+7*?4K&7X@G2#-#]DF +MX4<]?2PM-V;"*<A4*!^D-4W%A#W07TOMD(2F`<-&9A2QAX"=>>^X[SO@WV"Y +MLE^"^RS:62J,&3T)4Z:F)G7DT;8)M*"(O^PW_)Z7"-S@^*.Q'[%Q@,D`"V]J +M+U@[2@F(J/#8:@U3(0:&H@>F?2]25%Z`+H5Z;H!>KK:8>ADE";#X+YDMD`2- +M4@84J)XWD`*ZX&Z$=2N%(`6>]MT27N82A%J\9KN!B`.(!G%60`H@918H^E1V +M02I"-)9[(!2Y!AN`"=F*CI^W](/:DN/:D89=KA5Q#DV.X\KG*-.DG^29M%SM +MI'XPPV<^XW.Q]L%XC$>P_(>CUFB8KDQ.0)F<FO&.7E,=_1\G[?%@V!_$2B/[ +M$%X:'K)7E^@>A,FS]0EBG*Y]GU-`23O:23GP.5-LB/0ALJ)]=3$96EVK/<)@ +M5=3WX;DZLX<NBD(CUL&_KU=CC?C%J-K4#'UY`OB5B,TVUJA%,.]L]8`.V:9? +MO%6;;K*WP+:M]DDIW6KO;@K6OMP$;S?;'3ZMU0WSM*[%/521-JTNO\=^#L_7 +M+K"M+?3K2Q;RY8H"M0EKA^L.5-CJH8"^JL%D8X-I$UD\>QJR+:=:+%8I61(D +M\JF>FAAO'6;/2URY:UIBJ(^=E8R6,"FEYFDI>U+*IPVC7-42$D5![$W9JQ7T +M,J3<03PN`#7F&^)`X/#\S@Z&ZYNNX[X-P,W-A_Z:Y\'#S9./E1?I%!D8`$Z? +MX;R`G0`=.%4"9DL0Q%QF(M1W03(FFH9P2SAV(Q0^SE/XVI&V^"@'7,P>;/5F +MV;*CU>X2'@WL8Z4GJRF(C]D\V>&,BY06/7L2"VIQ?"GTH!M8OXM@!8\%L6?Y +M#KR%V_"N>$8V$&,F26$01I`<J$F:,L"R`0RY@`(7[8GW[DH7$^%:Z5WIM=M= +MO?B$KH0`9?34ECY$2D>Q*HI;[:&-R)[YQ[9K+Q[^Q3<D:K,R7:`VH5+DR4R7 +MIYTM*\TR;.]J.\0)C+Q9UU-)L*!1TC?=ZOQOR!=\&@[#9=B4,0:PM\\*A;@< +M/++8N!?!:%,6(>9ZRM"'#$[$,<+0M]U@[OG+&$EA)1V&3,3JC$E@V12@C0'I +MX]$`4W$HT7@*15L[YWP4)-%V)HNX]MH/,**T'W%ZQ/1)H;N%K=G10YI$B)J= +MXB9`4F2MO%/69+/AVA7-ZJQ<;I9+S4HU6]#`13[5MMOX:FYGX4]MD!?O]AA^ +M46K2T^TQ?K998T_1#SQ^;T_#S2Q\A7-U.\'M592.+WW\JW%WU/GAUY&5P+>\ +M0?A[#%A@@SV2"W,YF&+8B9#*!*<77FX>R($R1&A8G'T'ZDW4()7BD5"@JQ2A +MH!<!YJW"B>W[]L/D!D1O%J`[%6P4B6#F^Y4OV]AAZ`,@2.,DM/U;'LKRC8*R +M.(D#5FA]P1"C@P)VZWOK532J$<C1+?<OP7BU_-M`\/);VKA^C_KR/(=V;2+T +M:%R%,2)NN^<Y6'L3Z$)+6#1C7_2_DH6B-;8)2"=,2,=\,F/3SYPQK;RVD3%8 +M,C4U+]$YL)>'GG@XO9O<>?>3I>T^"'.?&(H^THD(+<@B<4PQ43<O8LS(FO-< +M?Q4.^6^8"A/7`./X(JJS%GR9=79>KM:,<JT2KSD*#TRN#79IC=JOX:?;;XW@ +M![>9\(_5>=6#AT'_"OY%GX$:O1JT>@#T:M`?0\O7K3>=WBOX[8\'@(D@.M"J +MT^M96``/0ZLW[(PZ;RSQ,ACA[\@:O&EUZ:D/_P[%-/ZSCVU_M'XU6+?5>S5N +MO8)&7:MU07UTK4MHV^W\B(7]=DL.Z*I%@[_J],8CHN:JWQN]QH=>ZTH=Z?=: +MHTZ_ASW"TWA`#^W7+2"T9_T"6'M]_!\?@%!J.[ZR!IVV:-V_-%B_A_]W86C] +M:\0%OP/\_\+"'^B:Z*<]2<T$/M?BA?GOS&>,&_YUV9TJYHV&4=%31#J]]L"Z +MLGK$D`OK%_QY#;V-AL2I$="-K.E%K`>>M/M7UYTN]M7M`$0/68!@R`@Q''CZ +MD<AO_0(<'EO$$O74?R//QGO6ST@I#*`ULBY^B)_'0T3>Z_<LXL1KXCP\="Y_ +MI5\Q&IRL?N=BB%RP!JT1\N"Z-1S^#(R`IT&_;5T0>ZFS@=7N7`.^@853@;]# +M:X0_,`4]P#'HPV`&8W5N/[1^&EN]-@#"6#HX1P".O!^^1L#AJ(4RA)$[R;WA +MZ`+E!GY@`@P,5P)K8!R$;=Q3G!KW1AU`]J;5'H^O\+?;`:`WUN"'_M"BAR%P +M4&:25&"NM&/P?_>YNKPDEO^GSEE&3`;TW:E^Q@5OIT:Y6M*<DQR3GKWF8K#[ +M.^YS^2*A=&//[NQWCGN;!*"]M0+07!ZQH6`IGD!.VSOE])T#[HF7]EM.$9*X +M0NTB<CF7MO*_K7$7_[R<<1Q<+9615LT_P#<H*I<31^<N!J"0GB@P\+R^T9,6 +M-'C>T.N0SKC*+)WI297/39/.M#IQSF>4=(F--T($X5VA9``2,]H*QE#1]AZ` +MS`V@QSBI47%[9T?B=#&KAT3MHR+U^7/M?,)5'!#56>S'C,!:28\)UDSPSQ)I +M0[^SUK`=)3!\8-1/_OO\&7N4]1=6"L"Y!G!\8%U=CWX].$[#<GP@,R\/CIG@ +MDXR];(IC,R=.XE+\/?9WX^]LVS6D8S[1U4)$_@KJMX=+]GFY"/^<%,]DQ[^S +M1`^ZDRFT4X:ON;N3DR+U]'0G3V&BTZ0-7-GC^N1NMKGR&<0C+I&P$&$[/F`D +M#C).)IL#^%ER_K='WLQUZ,!-Q73$$A`G]DF-1&7%C2SOYR;[GIGI"<:B,SR^ +M$<?5RW5`B;>M;I=Y/K,Q[<K!#%P\Q^6PB1)YQYC^DM?[<65>>\H=;*TVXP*V +M!K%Q^_JYN0F0<N]:J]VZ=)UHN7GC.E&Y>=TZ3;?(`)283V#1QTS('\BETE^7 +M291'GV23N*^19!.5Z612X$$DX\?I_IOU=),N(2)43)O<+>[N&&9RX>GK_#-6 +MWG_ABOC+3[;P">3'09Z.];[E#_>>/PO23QCBVITQWQCLH^.^6M-D[)?.&78< +M,H`S8Y;C_(@/>8K?Y>4&Z=%0A>!+.V&0CW=,<97+_7P4*X#B0U$,PPKS*EH0 +M0P?<%68BGP@K:``AMV?Y:$,65X3<!P'-QX$'K<H3Q?U'0WZW1)"E7+0O0=:3 +MXW^QF^[/)"MU]LIUPZSH.014<*+1O8!>85>0CR(P"ON"S\.\",=$1<Y;I`IC +M,XH<,O5Y&:F)X8`8-Z]VQ%&Q1P3B]C@NFA)I%.')(N*T:IA5,Y$(`045C0C4 +M6GD5SE&XW?42)F^:C\([JL*;0UG_4I$07804/EH$Y<QPWG$O'14M</SM\0"V +MRS$<$MI'(K.5@,_O08RX^KW@<\?=/&S,@$E7"!G`*6JAFJX6]D$`K>O-TQU) +M*A6S;%3,Q&66<O(RRQVW<3OM!;S`WW$WG/@RMS1*%<H=JVQZZ6T[+GOG\'OF +MB`3J8+W"HT@^V\JYIYR@W=G<GY3)G>A5#2?07-1C$=B#$1\='=%-AKF\BH!Y +MF?YZP2/?%P\X9@S3F1$)G8O)MB)U+1"W-PKJ;MP$06!`V.);%#_!->_F_P[/ +M@75T'+OK9#&<>JOC%=&^(5Z)FG2A2H"DB%(M792RFYTT2^7=)XLEXR1*9=>. +M_38_&Z;._62:"![ML0.1\]]726-X9Y"NUT0%8,Z!6<\2WRBY%EEGT4<O4C]_ +MH80S_<-?'_TA#CHT)4JW#TV_,J5/?\OCR[(B_4;#J5$VS3_B2R]Z6M&S/>=> +M?;PD]2LE^%FGA>V*8GB0=RTE!7_`QUL^@8+]O\^R)XFIJK]2,BJ5:.UBKC:S +MI^&:U*"_=J.;7D='3WPK;+_DT\&XIZZ<R._+!39%$N9>0)>F+7<F/_DB=>MX +M-8/FLHP2U>++>&SK.RI&6F:J_+"7HE7[1-H?3ZLV>UO?Z?K3J$^=^5K%J-1J +M\0$7F;`)TE\0'[V*LON+4?ZIB-KB&<66"">U[J/@N.PC\B.^5!]:PXT$YLUR +M/45QV]Y*!9[\N!@^'-V1;=Q1G[2].P"W+?#IAC.W;^-RLW32-'>DDM4K1EW_ +MU!;(0.CA=C@0]]KB.VWR6P3R:VU*2_'WL$EQQ9>F2!MI-_'\<*]/3)']TO%L +M?1OM"WR=ZRP>:_3%,D0,RV??048(\#-]-%#"0C$;'[@2!@5AM&%CL^"^P:+; +M5."_&5HKNDY4U&XL_#2V!K].R!452?PT*;%-^<^:E'W\CG^+J<I6#B)=F!0+ +M/6[HANWJ=-6P#9>B&>KIFN&)MI5FY;19V7$KMUHRJMJ=7'RMZ?FEF`Q$GH)V +M?'B&]HG>`V;[',R7^Q8V06MWQGUA*GW^SO'6@7X[_T!>M1/[.[HIB&CUK$]$ +M^S?\%%#RKGCHB?3AM)8BV3.7W5*F#--ZD9:5`BZPG<-JM#4NC%K8U(C8&SO@ +MN),5=RRP$98PM;=E6)SU&=1JI6%4]8\:5T],*(CO$$07+>,,7$&!]@G6PI2R +M4HLXQJGXH*X8HKC.>0/;4?_A3-R:LIEXI<^Y$&5%B>8?B48T8[`01&?TQNZ= +>\,Y;ATP>0^,Y\-><H4<6'Y.K?<G_`Z+L!%(`6P`` +` +end + + +From owner-pgsql-hackers@hub.org Mon Oct 19 07:31:10 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id HAA05591 + for <maillist@candle.pha.pa.us>; Mon, 19 Oct 1998 07:31:09 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id HAA13574 for <maillist@candle.pha.pa.us>; Mon, 19 Oct 1998 07:12:57 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id GAA13957; + Mon, 19 Oct 1998 06:25:09 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Mon, 19 Oct 1998 06:22:35 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id GAA13581 + for pgsql-hackers-outgoing; Mon, 19 Oct 1998 06:22:33 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id GAA13566 + for <pgsql-hackers@postgreSQL.org>; Mon, 19 Oct 1998 06:22:27 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id MAA13918; Mon, 19 Oct 1998 12:21:16 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma013635; Mon, 19 Oct 98 12:20:55 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id MAA11037; + Mon, 19 Oct 1998 12:18:27 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id MAA29382; + Mon, 19 Oct 1998 12:20:49 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zVA2V-000B5AC; Mon, 19 Oct 98 09:47 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zVCaT-000EBPC; Mon, 19 Oct 98 12:30 MET DST +Message-Id: <m0zVCaT-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: Inoue@tpf.co.jp (Hiroshi Inoue) +Date: Mon, 19 Oct 1998 12:30:52 +0200 (MET DST) +Cc: jwieck@debis.com, pgsql-hackers@postgreSQL.org, maillist@candle.pha.pa.us +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <002801bdfb46$39ad8ec0$2801007e@cadzone.tpf.co.jp> from "Hiroshi Inoue" at Oct 19, 98 06:52:46 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +Hiroshi Inoue wrote: + +> When using cursors,in most cases the response to get first(next) rows +> is necessary for me,not the throughput. +> How can we tell PostgreSQL optimzer that the response is necessary ? + + With my LIMIT patch, the offset and the row count are part of + the querytree. And if a LIMIT is given, the limitCount elemet + of the querytree (a Node *) isn't NULL what it is by default. + + When a LIMIT is given, the optimizer could assume that first + rows is wanted (even if the limit is ALL maybe - but I have + to think about this some more). And this assumption might let + it decide to use an index to resolve an ORDER BY even if no + qualification was given. + + Telling the optimizer that first rows wanted in a cursor + operation would read + + DECLARE CURSOR c FOR SELECT * FROM mytab ORDER BY a LIMIT ALL; + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + + +From owner-pgsql-hackers@hub.org Tue Oct 20 06:01:49 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id GAA02483 + for <maillist@candle.pha.pa.us>; Tue, 20 Oct 1998 06:01:48 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id FAA07799 for <maillist@candle.pha.pa.us>; Tue, 20 Oct 1998 05:51:19 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id FAA00108; + Tue, 20 Oct 1998 05:17:58 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Tue, 20 Oct 1998 05:16:37 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id FAA29953 + for pgsql-hackers-outgoing; Tue, 20 Oct 1998 05:16:35 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id FAA29939 + for <hackers@postgreSQL.org>; Tue, 20 Oct 1998 05:16:27 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id LAA04585; Tue, 20 Oct 1998 11:15:05 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma004337; Tue, 20 Oct 98 11:14:46 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id LAA14628; + Tue, 20 Oct 1998 11:12:27 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id LAA03564; + Tue, 20 Oct 1998 11:14:52 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zVVUa-000B5AC; Tue, 20 Oct 98 08:42 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zVY2c-000EBPC; Tue, 20 Oct 98 11:25 MET DST +Message-Id: <m0zVY2c-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: Inoue@tpf.co.jp (Hiroshi Inoue) +Date: Tue, 20 Oct 1998 11:25:22 +0200 (MET DST) +Cc: maillist@candle.pha.pa.us, jwieck@debis.com, hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <000601bdfc03$02e67100$2801007e@cadzone.tpf.co.jp> from "Hiroshi Inoue" at Oct 20, 98 05:24:09 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +Hiroshi Inoue wrote: + +> > * Prevent psort() usage when query already using index matching ORDER BY +> > +> > +> +> I can't find the reference to descending order cases except my posting. +> If we use an index scan to remove sorts in those cases,backward positioning +> and scanning are necessary. + + I think it's only thought as a reminder that the optimizer + needs some optimization. + + That topic, and the LIMIT stuff too I think, is past 6.4 work + and may go into a 6.4.1 performance release. So when we are + after 6.4, we have enough time to work out a real solution, + instead of just throwing in a patch as a quick shot. + + What we two did where steps in the same direction. Your one + covers more situations, but after all if multiple people have + the same idea there is a good chance that it is the right + thing to do. + +> +> Let t be a table with 2 indices, index1(key1,key2), index2(key1,key3). +> i.e. key1 is common to index1 and index2. +> +> And for the query +> select * from t where key1>....; +> +> If PosgreSQL optimizer choose [ index scan on index1 ] we can't remove +> sorts from the following query. +> select * from t where key1>... order by key1,key3; +> +> Similarly if [ index scan on index2 ] are chosen we can't remove sorts +> from the following query. +> select * from t where key1>... order by key1,key2; +> +> But in both cases (clever) optimizer can choose another index for scan. + + Right. As I remember, your solution does basically the same + as my one. It does not change the optimizers decision about + the index or if an index at all is used. So I assume they + hook into the same position where depending on the order by + clause the sort node is added. And that is at the very end of + the optimizer. + + What you describe above requires changes in upper levels of + optimization. Doing that is far away from my knowledge about + the optimizer. And some of your earlier statements let me + think you aren't familiar enough with it too. We need at + least help from others to do it well. + + I don't want to dive that deep into the optimizer. There was + a far too long time where the rule system was broken and got + out of sync with the parser/optimizer capabilities. I fixed + many things in it for 6.4. My first priority now is, not to + let such a situation come up again. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + + +From wieck@sapserv.debis.de Tue Oct 20 13:00:04 1998 +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA08269 + for <maillist@candle.pha.pa.us>; Tue, 20 Oct 1998 13:00:01 -0400 (EDT) +Received: by dsh.de; id TAA14203; Tue, 20 Oct 1998 19:02:15 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma014037; Tue, 20 Oct 98 19:01:39 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id SAA24445; + Tue, 20 Oct 1998 18:59:16 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id TAA06159; + Tue, 20 Oct 1998 19:01:40 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zVcmS-000B5AC; Tue, 20 Oct 98 16:29 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zVfKV-000EBPC; Tue, 20 Oct 98 19:12 MET DST +Message-Id: <m0zVfKV-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Tue, 20 Oct 1998 19:12:19 +0200 (MET DST) +Cc: jwieck@debis.com, Inoue@tpf.co.jp, hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810201645.MAA07946@candle.pha.pa.us> from "Bruce Momjian" at Oct 20, 98 12:45:49 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Status: ROr + +> +> I agree. Another good thing is that the LIMIT thing will not require a +> dump/reload, so it is a good candidate for a minor release. + + That's wrong, sorry. + + The limit thing as I implemented it adds 2 new variables to + the Query structure. Rewrite rules are stored as querytrees + and in the existing pg_rewrite entries that would be missing. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + +From owner-pgsql-hackers@hub.org Tue Oct 20 13:24:47 1998 +Received: from hub.org (majordom@hub.org [209.47.148.200]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA08484 + for <maillist@candle.pha.pa.us>; Tue, 20 Oct 1998 13:24:45 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id NAA01878; + Tue, 20 Oct 1998 13:00:06 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Tue, 20 Oct 1998 12:59:59 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id MAA01579 + for pgsql-hackers-outgoing; Tue, 20 Oct 1998 12:59:58 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id MAA01557 + for <hackers@postgreSQL.org>; Tue, 20 Oct 1998 12:59:52 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id TAA14203; Tue, 20 Oct 1998 19:02:15 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma014037; Tue, 20 Oct 98 19:01:39 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id SAA24445; + Tue, 20 Oct 1998 18:59:16 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id TAA06159; + Tue, 20 Oct 1998 19:01:40 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zVcmS-000B5AC; Tue, 20 Oct 98 16:29 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zVfKV-000EBPC; Tue, 20 Oct 98 19:12 MET DST +Message-Id: <m0zVfKV-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Tue, 20 Oct 1998 19:12:19 +0200 (MET DST) +Cc: jwieck@debis.com, Inoue@tpf.co.jp, hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810201645.MAA07946@candle.pha.pa.us> from "Bruce Momjian" at Oct 20, 98 12:45:49 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: ROr + +> +> I agree. Another good thing is that the LIMIT thing will not require a +> dump/reload, so it is a good candidate for a minor release. + + That's wrong, sorry. + + The limit thing as I implemented it adds 2 new variables to + the Query structure. Rewrite rules are stored as querytrees + and in the existing pg_rewrite entries that would be missing. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + + +From wieck@sapserv.debis.de Tue Oct 20 13:10:22 1998 +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA08339 + for <maillist@candle.pha.pa.us>; Tue, 20 Oct 1998 13:10:18 -0400 (EDT) +Received: by dsh.de; id TAA17171; Tue, 20 Oct 1998 19:12:30 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma017064; Tue, 20 Oct 98 19:12:00 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id TAA24806; + Tue, 20 Oct 1998 19:09:37 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id TAA06212; + Tue, 20 Oct 1998 19:12:01 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zVcwS-000B5AC; Tue, 20 Oct 98 16:39 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zVfUW-000EBPC; Tue, 20 Oct 98 19:22 MET DST +Message-Id: <m0zVfUW-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Tue, 20 Oct 1998 19:22:40 +0200 (MET DST) +Cc: jwieck@debis.com, Inoue@tpf.co.jp, hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810201702.NAA08286@candle.pha.pa.us> from "Bruce Momjian" at Oct 20, 98 01:02:58 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Status: RO + +> +> > > +> > > I agree. Another good thing is that the LIMIT thing will not require a +> > > dump/reload, so it is a good candidate for a minor release. +> > +> > That's wrong, sorry. +> > +> > The limit thing as I implemented it adds 2 new variables to +> > the Query structure. Rewrite rules are stored as querytrees +> > and in the existing pg_rewrite entries that would be missing. +> +> Oh, sorry. I forgot. That could be tough. + + But it wouldn't hurt to add them now to have them in + place. The required out-, read- and copyfuncs are in + my patch too. This would prevent dump/load when we + later add the real LIMIT functionality. And it does + not change anything now. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + +From owner-pgsql-hackers@hub.org Tue Oct 20 14:57:36 1998 +Received: from hub.org (majordom@hub.org [209.47.148.200]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id OAA11449 + for <maillist@candle.pha.pa.us>; Tue, 20 Oct 1998 14:57:34 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id NAA03547; + Tue, 20 Oct 1998 13:10:38 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Tue, 20 Oct 1998 13:10:23 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id NAA03488 + for pgsql-hackers-outgoing; Tue, 20 Oct 1998 13:10:21 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.8.8/8.8.8) with ESMTP id NAA03455 + for <hackers@postgreSQL.org>; Tue, 20 Oct 1998 13:10:10 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id TAA17171; Tue, 20 Oct 1998 19:12:30 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma017064; Tue, 20 Oct 98 19:12:00 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id TAA24806; + Tue, 20 Oct 1998 19:09:37 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id TAA06212; + Tue, 20 Oct 1998 19:12:01 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zVcwS-000B5AC; Tue, 20 Oct 98 16:39 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zVfUW-000EBPC; Tue, 20 Oct 98 19:22 MET DST +Message-Id: <m0zVfUW-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Tue, 20 Oct 1998 19:22:40 +0200 (MET DST) +Cc: jwieck@debis.com, Inoue@tpf.co.jp, hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810201702.NAA08286@candle.pha.pa.us> from "Bruce Momjian" at Oct 20, 98 01:02:58 pm +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: ROr + +> +> > > +> > > I agree. Another good thing is that the LIMIT thing will not require a +> > > dump/reload, so it is a good candidate for a minor release. +> > +> > That's wrong, sorry. +> > +> > The limit thing as I implemented it adds 2 new variables to +> > the Query structure. Rewrite rules are stored as querytrees +> > and in the existing pg_rewrite entries that would be missing. +> +> Oh, sorry. I forgot. That could be tough. + + But it wouldn't hurt to add them now to have them in + place. The required out-, read- and copyfuncs are in + my patch too. This would prevent dump/load when we + later add the real LIMIT functionality. And it does + not change anything now. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + + +From owner-pgsql-hackers@hub.org Wed Oct 21 02:35:54 1998 +Received: from hub.org (majordom@hub.org [209.47.148.200]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id CAA29494 + for <maillist@candle.pha.pa.us>; Wed, 21 Oct 1998 02:35:53 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.8.8/8.8.8) with SMTP id CAA13326; + Wed, 21 Oct 1998 02:10:42 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 21 Oct 1998 02:09:35 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.8.8/8.8.8) id CAA12900 + for pgsql-hackers-outgoing; Wed, 21 Oct 1998 02:09:33 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from candle.pha.pa.us (maillist@s5-03.ppp.op.net [209.152.195.67]) + by hub.org (8.8.8/8.8.8) with ESMTP id CAA12871 + for <hackers@postgreSQL.org>; Wed, 21 Oct 1998 02:09:26 -0400 (EDT) + (envelope-from maillist@candle.pha.pa.us) +Received: (from maillist@localhost) + by candle.pha.pa.us (8.9.0/8.9.0) id CAA27774; + Wed, 21 Oct 1998 02:09:27 -0400 (EDT) +From: Bruce Momjian <maillist@candle.pha.pa.us> +Message-Id: <199810210609.CAA27774@candle.pha.pa.us> +Subject: Re: [HACKERS] What about LIMIT in SELECT ? +In-Reply-To: <m0zVfUW-000EBPC@orion.SAPserv.Hamburg.dsh.de> from Jan Wieck at "Oct 20, 1998 7:22:40 pm" +To: jwieck@debis.com +Date: Wed, 21 Oct 1998 02:09:26 -0400 (EDT) +Cc: jwieck@debis.com, Inoue@tpf.co.jp, hackers@postgreSQL.org +X-Mailer: ELM [version 2.4ME+ PL47 (25)] +MIME-Version: 1.0 +Content-Type: text/plain; charset=US-ASCII +Content-Transfer-Encoding: 7bit +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +> > +> > > > +> > > > I agree. Another good thing is that the LIMIT thing will not require a +> > > > dump/reload, so it is a good candidate for a minor release. +> > > +> > > That's wrong, sorry. +> > > +> > > The limit thing as I implemented it adds 2 new variables to +> > > the Query structure. Rewrite rules are stored as querytrees +> > > and in the existing pg_rewrite entries that would be missing. +> > +> > Oh, sorry. I forgot. That could be tough. +> +> But it wouldn't hurt to add them now to have them in +> place. The required out-, read- and copyfuncs are in +> my patch too. This would prevent dump/load when we +> later add the real LIMIT functionality. And it does +> not change anything now. +> + +Jan, we found that I am having to require an initdb for the INET/CIDR +type, so if you want stuff to change the views/rules for the limit +addition post 6.4, please send them in and I will apply them. + +You clearly have the syntax down, so I think you should go ahead. + + +-- + Bruce Momjian | http://www.op.net/~candle + maillist@candle.pha.pa.us | (610) 853-3000 + + If your life is a hard drive, | 830 Blythe Avenue + + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 + + +From wieck@sapserv.debis.de Thu Oct 22 10:20:58 1998 +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id KAA20566 + for <maillist@candle.pha.pa.us>; Thu, 22 Oct 1998 10:20:54 -0400 (EDT) +Received: by dsh.de; id QAA09067; Thu, 22 Oct 1998 16:23:14 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma008719; Thu, 22 Oct 98 16:22:40 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id QAA01558; + Thu, 22 Oct 1998 16:19:55 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id QAA18978; + Thu, 22 Oct 1998 16:22:20 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zWJG2-000B5AC; Thu, 22 Oct 98 13:50 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zWLoE-000EBPC; Thu, 22 Oct 98 16:33 MET DST +Message-Id: <m0zWLoE-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] psql's help (the LIMIT stuff) +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Thu, 22 Oct 1998 16:33:50 +0200 (MET DST) +Cc: jwieck@debis.com, jose@sferacarta.com, pgsql-hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810221351.JAA19663@candle.pha.pa.us> from "Bruce Momjian" at Oct 22, 98 09:51:19 am +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Status: ROr + +> > +> > I hope the QUERY_LIMIT too. +> +> I still have that cnfify() possible fix to review for KQSO. Are you +> still thinking limit for 6.4 final, or a minor release after that? + + I posted the part that is the minimum applied to 6.4 to make + adding LIMIT later non-initdb earlier. Anyway, here it's + again. + + My LIMIT implementation that does it like the SET in the + toplevel executor (but via parsetree values) is ready for + production. I only held it back because it's feature, not + bugfix. + + Do you want it in 6.4 final? + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + +diff -cr src.orig/backend/nodes/copyfuncs.c src/backend/nodes/copyfuncs.c +*** src.orig/backend/nodes/copyfuncs.c Fri Oct 16 11:53:40 1998 +--- src/backend/nodes/copyfuncs.c Fri Oct 16 13:32:35 1998 +*************** +*** 1578,1583 **** +--- 1578,1586 ---- + newnode->unionClause = temp_list; + } + ++ Node_Copy(from, newnode, limitOffset); ++ Node_Copy(from, newnode, limitCount); ++ + return newnode; + } + +diff -cr src.orig/backend/nodes/outfuncs.c src/backend/nodes/outfuncs.c +*** src.orig/backend/nodes/outfuncs.c Fri Oct 16 11:53:40 1998 +--- src/backend/nodes/outfuncs.c Fri Oct 16 13:30:50 1998 +*************** +*** 259,264 **** +--- 259,268 ---- + appendStringInfo(str, (node->hasSubLinks ? "true" : "false")); + appendStringInfo(str, " :unionClause "); + _outNode(str, node->unionClause); ++ appendStringInfo(str, " :limitOffset "); ++ _outNode(str, node->limitOffset); ++ appendStringInfo(str, " :limitCount "); ++ _outNode(str, node->limitCount); + } + + static void +diff -cr src.orig/backend/nodes/readfuncs.c src/backend/nodes/readfuncs.c +*** src.orig/backend/nodes/readfuncs.c Fri Oct 16 11:53:40 1998 +--- src/backend/nodes/readfuncs.c Fri Oct 16 13:31:43 1998 +*************** +*** 163,168 **** +--- 163,174 ---- + token = lsptok(NULL, &length); /* skip :unionClause */ + local_node->unionClause = nodeRead(true); + ++ token = lsptok(NULL, &length); /* skip :limitOffset */ ++ local_node->limitOffset = nodeRead(true); ++ ++ token = lsptok(NULL, &length); /* skip :limitCount */ ++ local_node->limitCount = nodeRead(true); ++ + return local_node; + } + +diff -cr src.orig/include/nodes/parsenodes.h src/include/nodes/parsenodes.h +*** src.orig/include/nodes/parsenodes.h Fri Oct 16 11:53:58 1998 +--- src/include/nodes/parsenodes.h Fri Oct 16 13:35:32 1998 +*************** +*** 60,65 **** +--- 60,67 ---- + + List *unionClause; /* unions are linked under the previous + * query */ ++ Node *limitOffset; /* # of result tuples to skip */ ++ Node *limitCount; /* # of result tuples to return */ + + /* internal to planner */ + List *base_rel_list; /* base relation list */ +*************** +*** 639,644 **** +--- 641,648 ---- + char *portalname; /* the portal (cursor) to create */ + bool binary; /* a binary (internal) portal? */ + bool unionall; /* union without unique sort */ ++ Node *limitOffset; /* # of result tuples to skip */ ++ Node *limitCount; /* # of result tuples to return */ + } SelectStmt; + + + +From owner-pgsql-hackers@hub.org Thu Oct 22 11:33:41 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id LAA01724 + for <maillist@candle.pha.pa.us>; Thu, 22 Oct 1998 11:33:31 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id LAA12702 for <maillist@candle.pha.pa.us>; Thu, 22 Oct 1998 11:25:02 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.9.1/8.8.8) with SMTP id KAA11023; + Thu, 22 Oct 1998 10:22:13 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Thu, 22 Oct 1998 10:21:07 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.9.1/8.8.8) id KAA10873 + for pgsql-hackers-outgoing; Thu, 22 Oct 1998 10:21:05 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.9.1/8.8.8) with ESMTP id KAA10847 + for <pgsql-hackers@postgreSQL.org>; Thu, 22 Oct 1998 10:21:00 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id QAA09067; Thu, 22 Oct 1998 16:23:14 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma008719; Thu, 22 Oct 98 16:22:40 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id QAA01558; + Thu, 22 Oct 1998 16:19:55 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id QAA18978; + Thu, 22 Oct 1998 16:22:20 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zWJG2-000B5AC; Thu, 22 Oct 98 13:50 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zWLoE-000EBPC; Thu, 22 Oct 98 16:33 MET DST +Message-Id: <m0zWLoE-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] psql's help (the LIMIT stuff) +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Thu, 22 Oct 1998 16:33:50 +0200 (MET DST) +Cc: jwieck@debis.com, jose@sferacarta.com, pgsql-hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810221351.JAA19663@candle.pha.pa.us> from "Bruce Momjian" at Oct 22, 98 09:51:19 am +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: RO + +> > +> > I hope the QUERY_LIMIT too. +> +> I still have that cnfify() possible fix to review for KQSO. Are you +> still thinking limit for 6.4 final, or a minor release after that? + + I posted the part that is the minimum applied to 6.4 to make + adding LIMIT later non-initdb earlier. Anyway, here it's + again. + + My LIMIT implementation that does it like the SET in the + toplevel executor (but via parsetree values) is ready for + production. I only held it back because it's feature, not + bugfix. + + Do you want it in 6.4 final? + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + +diff -cr src.orig/backend/nodes/copyfuncs.c src/backend/nodes/copyfuncs.c +*** src.orig/backend/nodes/copyfuncs.c Fri Oct 16 11:53:40 1998 +--- src/backend/nodes/copyfuncs.c Fri Oct 16 13:32:35 1998 +*************** +*** 1578,1583 **** +--- 1578,1586 ---- + newnode->unionClause = temp_list; + } + ++ Node_Copy(from, newnode, limitOffset); ++ Node_Copy(from, newnode, limitCount); ++ + return newnode; + } + +diff -cr src.orig/backend/nodes/outfuncs.c src/backend/nodes/outfuncs.c +*** src.orig/backend/nodes/outfuncs.c Fri Oct 16 11:53:40 1998 +--- src/backend/nodes/outfuncs.c Fri Oct 16 13:30:50 1998 +*************** +*** 259,264 **** +--- 259,268 ---- + appendStringInfo(str, (node->hasSubLinks ? "true" : "false")); + appendStringInfo(str, " :unionClause "); + _outNode(str, node->unionClause); ++ appendStringInfo(str, " :limitOffset "); ++ _outNode(str, node->limitOffset); ++ appendStringInfo(str, " :limitCount "); ++ _outNode(str, node->limitCount); + } + + static void +diff -cr src.orig/backend/nodes/readfuncs.c src/backend/nodes/readfuncs.c +*** src.orig/backend/nodes/readfuncs.c Fri Oct 16 11:53:40 1998 +--- src/backend/nodes/readfuncs.c Fri Oct 16 13:31:43 1998 +*************** +*** 163,168 **** +--- 163,174 ---- + token = lsptok(NULL, &length); /* skip :unionClause */ + local_node->unionClause = nodeRead(true); + ++ token = lsptok(NULL, &length); /* skip :limitOffset */ ++ local_node->limitOffset = nodeRead(true); ++ ++ token = lsptok(NULL, &length); /* skip :limitCount */ ++ local_node->limitCount = nodeRead(true); ++ + return local_node; + } + +diff -cr src.orig/include/nodes/parsenodes.h src/include/nodes/parsenodes.h +*** src.orig/include/nodes/parsenodes.h Fri Oct 16 11:53:58 1998 +--- src/include/nodes/parsenodes.h Fri Oct 16 13:35:32 1998 +*************** +*** 60,65 **** +--- 60,67 ---- + + List *unionClause; /* unions are linked under the previous + * query */ ++ Node *limitOffset; /* # of result tuples to skip */ ++ Node *limitCount; /* # of result tuples to return */ + + /* internal to planner */ + List *base_rel_list; /* base relation list */ +*************** +*** 639,644 **** +--- 641,648 ---- + char *portalname; /* the portal (cursor) to create */ + bool binary; /* a binary (internal) portal? */ + bool unionall; /* union without unique sort */ ++ Node *limitOffset; /* # of result tuples to skip */ ++ Node *limitCount; /* # of result tuples to return */ + } SelectStmt; + + + + +From wieck@sapserv.debis.de Thu Oct 22 11:01:05 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id LAA21185 + for <maillist@candle.pha.pa.us>; Thu, 22 Oct 1998 11:01:00 -0400 (EDT) +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id KAA09646 for <maillist@candle.pha.pa.us>; Thu, 22 Oct 1998 10:44:36 -0400 (EDT) +Received: by dsh.de; id QAA19394; Thu, 22 Oct 1998 16:43:42 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma017268; Thu, 22 Oct 98 16:39:44 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id QAA02988; + Thu, 22 Oct 1998 16:36:46 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id QAA19155; + Thu, 22 Oct 1998 16:39:10 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zWJWL-000B5DC; Thu, 22 Oct 98 14:07 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zWM4W-000EBPC; Thu, 22 Oct 98 16:50 MET DST +Message-Id: <m0zWM4W-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] psql's help (the LIMIT stuff) +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Thu, 22 Oct 1998 16:50:40 +0200 (MET DST) +Cc: jwieck@debis.com, jose@sferacarta.com, pgsql-hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810221424.KAA20601@candle.pha.pa.us> from "Bruce Momjian" at Oct 22, 98 10:24:08 am +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Status: RO + +> +> > > > +> > > > I hope the QUERY_LIMIT too. +> > > +> > > I still have that cnfify() possible fix to review for KQSO. Are you +> > > still thinking limit for 6.4 final, or a minor release after that? +> > +> > I posted the part that is the minimum applied to 6.4 to make +> > adding LIMIT later non-initdb earlier. Anyway, here it's +> > again. +> +> Already applied. I assume it is the same as the one I applied. + + Seen, thanks. Your 'Applied' just arrived after I packed it + again. It's the same. + +> We are close to final, and can easily put it in 6.4.1, which I am sure +> we will need, and if we split CVS trees, you'll have lots of minor +> versions to pick from. :-) +> +> Seems like it would be a nice minor release item, but the problem is +> that minor releases aren't tested as much as major ones. How confident +> are you in the code? What do others thing? + + I regression tested it, and did additional tests in the + SPI/PL area. It works. It only touches the parser and the + executor. Rules, planner/optimizer just bypass the values in + the parsetree. The parser and the executor are parts of + Postgres I feel very familiar with (not so in the optimizer). + I trust in the code and would use it in a production + environment. + + It's below. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + +diff -cr src.orig/backend/commands/command.c src/backend/commands/command.c +*** src.orig/backend/commands/command.c Fri Oct 16 11:53:38 1998 +--- src/backend/commands/command.c Fri Oct 16 12:56:44 1998 +*************** +*** 39,44 **** +--- 39,45 ---- + #include "utils/mcxt.h" + #include "utils/portal.h" + #include "utils/syscache.h" ++ #include "string.h" + + /* ---------------- + * PortalExecutorHeapMemory stuff +*************** +*** 101,106 **** +--- 102,108 ---- + int feature; + QueryDesc *queryDesc; + MemoryContext context; ++ Const limcount; + + /* ---------------- + * sanity checks +*************** +*** 113,118 **** +--- 115,134 ---- + } + + /* ---------------- ++ * Create a const node from the given count value ++ * ---------------- ++ */ ++ memset(&limcount, 0, sizeof(limcount)); ++ limcount.type = T_Const; ++ limcount.consttype = INT4OID; ++ limcount.constlen = sizeof(int4); ++ limcount.constvalue = (Datum)count; ++ limcount.constisnull = FALSE; ++ limcount.constbyval = TRUE; ++ limcount.constisset = FALSE; ++ limcount.constiscast = FALSE; ++ ++ /* ---------------- + * get the portal from the portal name + * ---------------- + */ +*************** +*** 176,182 **** + PortalExecutorHeapMemory = (MemoryContext) + PortalGetHeapMemory(portal); + +! ExecutorRun(queryDesc, PortalGetState(portal), feature, count); + + if (dest == None) /* MOVE */ + pfree(queryDesc); +--- 192,198 ---- + PortalExecutorHeapMemory = (MemoryContext) + PortalGetHeapMemory(portal); + +! ExecutorRun(queryDesc, PortalGetState(portal), feature, (Node *)NULL, (Node *)&limcount); + + if (dest == None) /* MOVE */ + pfree(queryDesc); +diff -cr src.orig/backend/executor/execMain.c src/backend/executor/execMain.c +*** src.orig/backend/executor/execMain.c Fri Oct 16 11:53:38 1998 +--- src/backend/executor/execMain.c Fri Oct 16 20:05:19 1998 +*************** +*** 64,69 **** +--- 64,70 ---- + static void EndPlan(Plan *plan, EState *estate); + static TupleTableSlot *ExecutePlan(EState *estate, Plan *plan, + Query *parseTree, CmdType operation, ++ int offsetTuples, + int numberTuples, ScanDirection direction, + void (*printfunc) ()); + static void ExecRetrieve(TupleTableSlot *slot, void (*printfunc) (), +*************** +*** 163,169 **** + * ---------------------------------------------------------------- + */ + TupleTableSlot * +! ExecutorRun(QueryDesc *queryDesc, EState *estate, int feature, int count) + { + CmdType operation; + Query *parseTree; +--- 164,170 ---- + * ---------------------------------------------------------------- + */ + TupleTableSlot * +! ExecutorRun(QueryDesc *queryDesc, EState *estate, int feature, Node *limoffset, Node *limcount) + { + CmdType operation; + Query *parseTree; +*************** +*** 171,176 **** +--- 172,179 ---- + TupleTableSlot *result; + CommandDest dest; + void (*destination) (); ++ int offset = 0; ++ int count = 0; + + /****************** + * sanity checks +*************** +*** 191,196 **** +--- 194,289 ---- + estate->es_processed = 0; + estate->es_lastoid = InvalidOid; + ++ /****************** ++ * if given get the offset of the LIMIT clause ++ ****************** ++ */ ++ if (limoffset != NULL) ++ { ++ Const *coffset; ++ Param *poffset; ++ ParamListInfo paramLI; ++ int i; ++ ++ switch (nodeTag(limoffset)) ++ { ++ case T_Const: ++ coffset = (Const *)limoffset; ++ offset = (int)(coffset->constvalue); ++ break; ++ ++ case T_Param: ++ poffset = (Param *)limoffset; ++ paramLI = estate->es_param_list_info; ++ ++ if (paramLI == NULL) ++ elog(ERROR, "parameter for limit offset not in executor state"); ++ for (i = 0; paramLI[i].kind != PARAM_INVALID; i++) ++ { ++ if (paramLI[i].kind == PARAM_NUM && paramLI[i].id == poffset->paramid) ++ break; ++ } ++ if (paramLI[i].kind == PARAM_INVALID) ++ elog(ERROR, "parameter for limit offset not in executor state"); ++ if (paramLI[i].isnull) ++ elog(ERROR, "limit offset cannot be NULL value"); ++ offset = (int)(paramLI[i].value); ++ ++ break; ++ ++ default: ++ elog(ERROR, "unexpected node type %d as limit offset", nodeTag(limoffset)); ++ } ++ ++ if (offset < 0) ++ elog(ERROR, "limit offset cannot be negative"); ++ } ++ ++ /****************** ++ * if given get the count of the LIMIT clause ++ ****************** ++ */ ++ if (limcount != NULL) ++ { ++ Const *ccount; ++ Param *pcount; ++ ParamListInfo paramLI; ++ int i; ++ ++ switch (nodeTag(limcount)) ++ { ++ case T_Const: ++ ccount = (Const *)limcount; ++ count = (int)(ccount->constvalue); ++ break; ++ ++ case T_Param: ++ pcount = (Param *)limcount; ++ paramLI = estate->es_param_list_info; ++ ++ if (paramLI == NULL) ++ elog(ERROR, "parameter for limit count not in executor state"); ++ for (i = 0; paramLI[i].kind != PARAM_INVALID; i++) ++ { ++ if (paramLI[i].kind == PARAM_NUM && paramLI[i].id == pcount->paramid) ++ break; ++ } ++ if (paramLI[i].kind == PARAM_INVALID) ++ elog(ERROR, "parameter for limit count not in executor state"); ++ if (paramLI[i].isnull) ++ elog(ERROR, "limit count cannot be NULL value"); ++ count = (int)(paramLI[i].value); ++ ++ break; ++ ++ default: ++ elog(ERROR, "unexpected node type %d as limit count", nodeTag(limcount)); ++ } ++ ++ if (count < 0) ++ elog(ERROR, "limit count cannot be negative"); ++ } ++ + switch (feature) + { + +*************** +*** 199,205 **** + plan, + parseTree, + operation, +! ALL_TUPLES, + ForwardScanDirection, + destination); + break; +--- 292,299 ---- + plan, + parseTree, + operation, +! offset, +! count, + ForwardScanDirection, + destination); + break; +*************** +*** 208,213 **** +--- 302,308 ---- + plan, + parseTree, + operation, ++ offset, + count, + ForwardScanDirection, + destination); +*************** +*** 222,227 **** +--- 317,323 ---- + plan, + parseTree, + operation, ++ offset, + count, + BackwardScanDirection, + destination); +*************** +*** 237,242 **** +--- 333,339 ---- + plan, + parseTree, + operation, ++ 0, + ONE_TUPLE, + ForwardScanDirection, + destination); +*************** +*** 691,696 **** +--- 788,794 ---- + Plan *plan, + Query *parseTree, + CmdType operation, ++ int offsetTuples, + int numberTuples, + ScanDirection direction, + void (*printfunc) ()) +*************** +*** 742,747 **** +--- 840,859 ---- + { + result = NULL; + break; ++ } ++ ++ /****************** ++ * For now we completely execute the plan and skip ++ * result tuples if requested by LIMIT offset. ++ * Finally we should try to do it in deeper levels ++ * if possible (during index scan) ++ * - Jan ++ ****************** ++ */ ++ if (offsetTuples > 0) ++ { ++ --offsetTuples; ++ continue; + } + + /****************** +diff -cr src.orig/backend/executor/functions.c src/backend/executor/functions.c +*** src.orig/backend/executor/functions.c Fri Oct 16 11:53:38 1998 +--- src/backend/executor/functions.c Fri Oct 16 19:01:02 1998 +*************** +*** 130,135 **** +--- 130,138 ---- + None); + estate = CreateExecutorState(); + ++ if (queryTree->limitOffset != NULL || queryTree->limitCount != NULL) ++ elog(ERROR, "LIMIT clause from SQL functions not yet implemented"); ++ + if (nargs > 0) + { + int i; +*************** +*** 200,206 **** + + feature = (LAST_POSTQUEL_COMMAND(es)) ? EXEC_RETONE : EXEC_RUN; + +! return ExecutorRun(es->qd, es->estate, feature, 0); + } + + static void +--- 203,209 ---- + + feature = (LAST_POSTQUEL_COMMAND(es)) ? EXEC_RETONE : EXEC_RUN; + +! return ExecutorRun(es->qd, es->estate, feature, (Node *)NULL, (Node *)NULL); + } + + static void +diff -cr src.orig/backend/executor/spi.c src/backend/executor/spi.c +*** src.orig/backend/executor/spi.c Fri Oct 16 11:53:39 1998 +--- src/backend/executor/spi.c Fri Oct 16 19:25:33 1998 +*************** +*** 791,796 **** +--- 791,798 ---- + bool isRetrieveIntoRelation = false; + char *intoName = NULL; + int res; ++ Const tcount_const; ++ Node *count = NULL; + + switch (operation) + { +*************** +*** 825,830 **** +--- 827,865 ---- + return SPI_ERROR_OPUNKNOWN; + } + ++ /* ---------------- ++ * Get the query LIMIT tuple count ++ * ---------------- ++ */ ++ if (parseTree->limitCount != NULL) ++ { ++ /* ---------------- ++ * A limit clause in the parsetree overrides the ++ * tcount parameter ++ * ---------------- ++ */ ++ count = parseTree->limitCount; ++ } ++ else ++ { ++ /* ---------------- ++ * No LIMIT clause in parsetree. Use a local Const node ++ * to put tcount into it ++ * ---------------- ++ */ ++ memset(&tcount_const, 0, sizeof(tcount_const)); ++ tcount_const.type = T_Const; ++ tcount_const.consttype = INT4OID; ++ tcount_const.constlen = sizeof(int4); ++ tcount_const.constvalue = (Datum)tcount; ++ tcount_const.constisnull = FALSE; ++ tcount_const.constbyval = TRUE; ++ tcount_const.constisset = FALSE; ++ tcount_const.constiscast = FALSE; ++ ++ count = (Node *)&tcount_const; ++ } ++ + if (state == NULL) /* plan preparation */ + return res; + #ifdef SPI_EXECUTOR_STATS +*************** +*** 845,851 **** + return SPI_OK_CURSOR; + } + +! ExecutorRun(queryDesc, state, EXEC_FOR, tcount); + + _SPI_current->processed = state->es_processed; + if (operation == CMD_SELECT && queryDesc->dest == SPI) +--- 880,886 ---- + return SPI_OK_CURSOR; + } + +! ExecutorRun(queryDesc, state, EXEC_FOR, parseTree->limitOffset, count); + + _SPI_current->processed = state->es_processed; + if (operation == CMD_SELECT && queryDesc->dest == SPI) +diff -cr src.orig/backend/parser/analyze.c src/backend/parser/analyze.c +*** src.orig/backend/parser/analyze.c Fri Oct 16 11:53:41 1998 +--- src/backend/parser/analyze.c Fri Oct 16 13:29:27 1998 +*************** +*** 180,186 **** +--- 180,190 ---- + + case T_SelectStmt: + if (!((SelectStmt *) parseTree)->portalname) ++ { + result = transformSelectStmt(pstate, (SelectStmt *) parseTree); ++ result->limitOffset = ((SelectStmt *)parseTree)->limitOffset; ++ result->limitCount = ((SelectStmt *)parseTree)->limitCount; ++ } + else + result = transformCursorStmt(pstate, (SelectStmt *) parseTree); + break; +diff -cr src.orig/backend/parser/gram.y src/backend/parser/gram.y +*** src.orig/backend/parser/gram.y Fri Oct 16 11:53:42 1998 +--- src/backend/parser/gram.y Sun Oct 18 22:20:36 1998 +*************** +*** 45,50 **** +--- 45,51 ---- + #include "catalog/catname.h" + #include "utils/elog.h" + #include "access/xact.h" ++ #include "catalog/pg_type.h" + + #ifdef MULTIBYTE + #include "mb/pg_wchar.h" +*************** +*** 163,169 **** + sort_clause, sortby_list, index_params, index_list, name_list, + from_clause, from_list, opt_array_bounds, nest_array_bounds, + expr_list, attrs, res_target_list, res_target_list2, +! def_list, opt_indirection, group_clause, TriggerFuncArgs + + %type <node> func_return + %type <boolean> set_opt +--- 164,171 ---- + sort_clause, sortby_list, index_params, index_list, name_list, + from_clause, from_list, opt_array_bounds, nest_array_bounds, + expr_list, attrs, res_target_list, res_target_list2, +! def_list, opt_indirection, group_clause, TriggerFuncArgs, +! opt_select_limit + + %type <node> func_return + %type <boolean> set_opt +*************** +*** 192,197 **** +--- 194,201 ---- + + %type <ival> fetch_how_many + ++ %type <node> select_limit_value select_offset_value ++ + %type <list> OptSeqList + %type <defelt> OptSeqElem + +*************** +*** 267,273 **** + FALSE_P, FETCH, FLOAT, FOR, FOREIGN, FROM, FULL, + GRANT, GROUP, HAVING, HOUR_P, + IN, INNER_P, INSENSITIVE, INSERT, INTERVAL, INTO, IS, +! JOIN, KEY, LANGUAGE, LEADING, LEFT, LIKE, LOCAL, + MATCH, MINUTE_P, MONTH_P, NAMES, + NATIONAL, NATURAL, NCHAR, NEXT, NO, NOT, NULL_P, NUMERIC, + OF, ON, ONLY, OPTION, OR, ORDER, OUTER_P, +--- 271,277 ---- + FALSE_P, FETCH, FLOAT, FOR, FOREIGN, FROM, FULL, + GRANT, GROUP, HAVING, HOUR_P, + IN, INNER_P, INSENSITIVE, INSERT, INTERVAL, INTO, IS, +! JOIN, KEY, LANGUAGE, LEADING, LEFT, LIKE, LIMIT, LOCAL, + MATCH, MINUTE_P, MONTH_P, NAMES, + NATIONAL, NATURAL, NCHAR, NEXT, NO, NOT, NULL_P, NUMERIC, + OF, ON, ONLY, OPTION, OR, ORDER, OUTER_P, +*************** +*** 299,305 **** + INCREMENT, INDEX, INHERITS, INSTEAD, ISNULL, + LANCOMPILER, LISTEN, LOAD, LOCATION, LOCK_P, MAXVALUE, MINVALUE, MOVE, + NEW, NOCREATEDB, NOCREATEUSER, NONE, NOTHING, NOTIFY, NOTNULL, +! OIDS, OPERATOR, PASSWORD, PROCEDURAL, + RECIPE, RENAME, RESET, RETURNS, ROW, RULE, + SEQUENCE, SERIAL, SETOF, SHOW, START, STATEMENT, STDIN, STDOUT, TRUSTED, + UNLISTEN, UNTIL, VACUUM, VALID, VERBOSE, VERSION +--- 303,309 ---- + INCREMENT, INDEX, INHERITS, INSTEAD, ISNULL, + LANCOMPILER, LISTEN, LOAD, LOCATION, LOCK_P, MAXVALUE, MINVALUE, MOVE, + NEW, NOCREATEDB, NOCREATEUSER, NONE, NOTHING, NOTIFY, NOTNULL, +! OFFSET, OIDS, OPERATOR, PASSWORD, PROCEDURAL, + RECIPE, RENAME, RESET, RETURNS, ROW, RULE, + SEQUENCE, SERIAL, SETOF, SHOW, START, STATEMENT, STDIN, STDOUT, TRUSTED, + UNLISTEN, UNTIL, VACUUM, VALID, VERBOSE, VERSION +*************** +*** 2591,2596 **** +--- 2595,2601 ---- + result from_clause where_clause + group_clause having_clause + union_clause sort_clause ++ opt_select_limit + { + SelectStmt *n = makeNode(SelectStmt); + n->unique = $2; +*************** +*** 2602,2607 **** +--- 2607,2622 ---- + n->havingClause = $8; + n->unionClause = $9; + n->sortClause = $10; ++ if ($11 != NIL) ++ { ++ n->limitOffset = nth(0, $11); ++ n->limitCount = nth(1, $11); ++ } ++ else ++ { ++ n->limitOffset = NULL; ++ n->limitCount = NULL; ++ } + $$ = (Node *)n; + } + ; +*************** +*** 2699,2704 **** +--- 2714,2794 ---- + | ASC { $$ = "<"; } + | DESC { $$ = ">"; } + | /*EMPTY*/ { $$ = "<"; /*default*/ } ++ ; ++ ++ opt_select_limit: LIMIT select_offset_value ',' select_limit_value ++ { $$ = lappend(lappend(NIL, $2), $4); } ++ | LIMIT select_limit_value OFFSET select_offset_value ++ { $$ = lappend(lappend(NIL, $4), $2); } ++ | LIMIT select_limit_value ++ { $$ = lappend(lappend(NIL, NULL), $2); } ++ | OFFSET select_offset_value LIMIT select_limit_value ++ { $$ = lappend(lappend(NIL, $2), $4); } ++ | OFFSET select_offset_value ++ { $$ = lappend(lappend(NIL, $2), NULL); } ++ | /* EMPTY */ ++ { $$ = NIL; } ++ ; ++ ++ select_limit_value: Iconst ++ { ++ Const *n = makeNode(Const); ++ ++ if ($1 < 1) ++ elog(ERROR, "selection limit must be ALL or a positive integer value > 0"); ++ ++ n->consttype = INT4OID; ++ n->constlen = sizeof(int4); ++ n->constvalue = (Datum)$1; ++ n->constisnull = FALSE; ++ n->constbyval = TRUE; ++ n->constisset = FALSE; ++ n->constiscast = FALSE; ++ $$ = (Node *)n; ++ } ++ | ALL ++ { ++ Const *n = makeNode(Const); ++ n->consttype = INT4OID; ++ n->constlen = sizeof(int4); ++ n->constvalue = (Datum)0; ++ n->constisnull = FALSE; ++ n->constbyval = TRUE; ++ n->constisset = FALSE; ++ n->constiscast = FALSE; ++ $$ = (Node *)n; ++ } ++ | PARAM ++ { ++ Param *n = makeNode(Param); ++ n->paramkind = PARAM_NUM; ++ n->paramid = $1; ++ n->paramtype = INT4OID; ++ $$ = (Node *)n; ++ } ++ ; ++ ++ select_offset_value: Iconst ++ { ++ Const *n = makeNode(Const); ++ ++ n->consttype = INT4OID; ++ n->constlen = sizeof(int4); ++ n->constvalue = (Datum)$1; ++ n->constisnull = FALSE; ++ n->constbyval = TRUE; ++ n->constisset = FALSE; ++ n->constiscast = FALSE; ++ $$ = (Node *)n; ++ } ++ | PARAM ++ { ++ Param *n = makeNode(Param); ++ n->paramkind = PARAM_NUM; ++ n->paramid = $1; ++ n->paramtype = INT4OID; ++ $$ = (Node *)n; ++ } + ; + + /* +diff -cr src.orig/backend/parser/keywords.c src/backend/parser/keywords.c +*** src.orig/backend/parser/keywords.c Fri Oct 16 11:53:42 1998 +--- src/backend/parser/keywords.c Sun Oct 18 22:13:29 1998 +*************** +*** 128,133 **** +--- 128,134 ---- + {"leading", LEADING}, + {"left", LEFT}, + {"like", LIKE}, ++ {"limit", LIMIT}, + {"listen", LISTEN}, + {"load", LOAD}, + {"local", LOCAL}, +*************** +*** 156,161 **** +--- 157,163 ---- + {"null", NULL_P}, + {"numeric", NUMERIC}, + {"of", OF}, ++ {"offset", OFFSET}, + {"oids", OIDS}, + {"old", CURRENT}, + {"on", ON}, +diff -cr src.orig/backend/rewrite/rewriteDefine.c src/backend/rewrite/rewriteDefine.c +*** src.orig/backend/rewrite/rewriteDefine.c Fri Oct 16 11:53:46 1998 +--- src/backend/rewrite/rewriteDefine.c Fri Oct 16 13:48:55 1998 +*************** +*** 312,317 **** +--- 312,323 ---- + heap_close(event_relation); + + /* ++ * LIMIT in view is not supported ++ */ ++ if (query->limitOffset != NULL || query->limitCount != NULL) ++ elog(ERROR, "LIMIT clause not supported in views"); ++ ++ /* + * ... and finally the rule must be named _RETviewname. + */ + sprintf(expected_name, "_RET%s", event_obj->relname); +diff -cr src.orig/backend/tcop/pquery.c src/backend/tcop/pquery.c +*** src.orig/backend/tcop/pquery.c Fri Oct 16 11:53:47 1998 +--- src/backend/tcop/pquery.c Fri Oct 16 14:02:36 1998 +*************** +*** 40,46 **** + #include "commands/command.h" + + static char *CreateOperationTag(int operationType); +! static void ProcessQueryDesc(QueryDesc *queryDesc); + + + /* ---------------------------------------------------------------- +--- 40,46 ---- + #include "commands/command.h" + + static char *CreateOperationTag(int operationType); +! static void ProcessQueryDesc(QueryDesc *queryDesc, Node *limoffset, Node *limcount); + + + /* ---------------------------------------------------------------- +*************** +*** 205,211 **** + * ---------------------------------------------------------------- + */ + static void +! ProcessQueryDesc(QueryDesc *queryDesc) + { + Query *parseTree; + Plan *plan; +--- 205,211 ---- + * ---------------------------------------------------------------- + */ + static void +! ProcessQueryDesc(QueryDesc *queryDesc, Node *limoffset, Node *limcount) + { + Query *parseTree; + Plan *plan; +*************** +*** 330,336 **** + * actually run the plan.. + * ---------------- + */ +! ExecutorRun(queryDesc, state, EXEC_RUN, 0); + + /* save infos for EndCommand */ + UpdateCommandInfo(operation, state->es_lastoid, state->es_processed); +--- 330,336 ---- + * actually run the plan.. + * ---------------- + */ +! ExecutorRun(queryDesc, state, EXEC_RUN, limoffset, limcount); + + /* save infos for EndCommand */ + UpdateCommandInfo(operation, state->es_lastoid, state->es_processed); +*************** +*** 373,377 **** + print_plan(plan, parsetree); + } + else +! ProcessQueryDesc(queryDesc); + } +--- 373,377 ---- + print_plan(plan, parsetree); + } + else +! ProcessQueryDesc(queryDesc, parsetree->limitOffset, parsetree->limitCount); + } +diff -cr src.orig/include/executor/executor.h src/include/executor/executor.h +*** src.orig/include/executor/executor.h Fri Oct 16 11:53:56 1998 +--- src/include/executor/executor.h Fri Oct 16 12:04:17 1998 +*************** +*** 83,89 **** + * prototypes from functions in execMain.c + */ + extern TupleDesc ExecutorStart(QueryDesc *queryDesc, EState *estate); +! extern TupleTableSlot *ExecutorRun(QueryDesc *queryDesc, EState *estate, int feature, int count); + extern void ExecutorEnd(QueryDesc *queryDesc, EState *estate); + extern HeapTuple ExecConstraints(char *caller, Relation rel, HeapTuple tuple); + #ifdef QUERY_LIMIT +--- 83,89 ---- + * prototypes from functions in execMain.c + */ + extern TupleDesc ExecutorStart(QueryDesc *queryDesc, EState *estate); +! extern TupleTableSlot *ExecutorRun(QueryDesc *queryDesc, EState *estate, int feature, Node *limoffset, Node *limcount); + extern void ExecutorEnd(QueryDesc *queryDesc, EState *estate); + extern HeapTuple ExecConstraints(char *caller, Relation rel, HeapTuple tuple); + #ifdef QUERY_LIMIT + +From owner-pgsql-hackers@hub.org Thu Oct 22 13:12:34 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA01350 + for <maillist@candle.pha.pa.us>; Thu, 22 Oct 1998 13:12:29 -0400 (EDT) +Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id MAA17808 for <maillist@candle.pha.pa.us>; Thu, 22 Oct 1998 12:35:22 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.9.1/8.8.8) with SMTP id KAA14887; + Thu, 22 Oct 1998 10:49:09 -0400 (EDT) + (envelope-from owner-pgsql-hackers@hub.org) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Thu, 22 Oct 1998 10:44:59 +0000 (EDT) +Received: (from majordom@localhost) + by hub.org (8.9.1/8.8.8) id KAA14445 + for pgsql-hackers-outgoing; Thu, 22 Oct 1998 10:44:57 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-hackers@postgreSQL.org using -f +Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) + by hub.org (8.9.1/8.8.8) with ESMTP id KAA14431 + for <pgsql-hackers@postgreSQL.org>; Thu, 22 Oct 1998 10:44:47 -0400 (EDT) + (envelope-from wieck@sapserv.debis.de) +Received: by dsh.de; id QAA19394; Thu, 22 Oct 1998 16:43:42 +0200 (MET DST) +Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) + id xma017268; Thu, 22 Oct 98 16:39:44 +0200 +Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) + by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id QAA02988; + Thu, 22 Oct 1998 16:36:46 +0200 (MET DST) +Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) + by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id QAA19155; + Thu, 22 Oct 1998 16:39:10 +0200 +Received: from orion.SAPserv.Hamburg.dsh.de + by mars.SAPserv.Hamburg.dsh.de with smtp + for <<maillist@candle.pha.pa.us>> + id m0zWJWL-000B5DC; Thu, 22 Oct 98 14:07 MET DST +Received: by orion.SAPserv.Hamburg.dsh.de + for maillist@candle.pha.pa.us + id m0zWM4W-000EBPC; Thu, 22 Oct 98 16:50 MET DST +Message-Id: <m0zWM4W-000EBPC@orion.SAPserv.Hamburg.dsh.de> +From: jwieck@debis.com (Jan Wieck) +Subject: Re: [HACKERS] psql's help (the LIMIT stuff) +To: maillist@candle.pha.pa.us (Bruce Momjian) +Date: Thu, 22 Oct 1998 16:50:40 +0200 (MET DST) +Cc: jwieck@debis.com, jose@sferacarta.com, pgsql-hackers@postgreSQL.org +Reply-To: jwieck@debis.com (Jan Wieck) +In-Reply-To: <199810221424.KAA20601@candle.pha.pa.us> from "Bruce Momjian" at Oct 22, 98 10:24:08 am +X-Mailer: ELM [version 2.4 PL25] +Content-Type: text +Sender: owner-pgsql-hackers@postgreSQL.org +Precedence: bulk +Status: ROr + +> +> > > > +> > > > I hope the QUERY_LIMIT too. +> > > +> > > I still have that cnfify() possible fix to review for KQSO. Are you +> > > still thinking limit for 6.4 final, or a minor release after that? +> > +> > I posted the part that is the minimum applied to 6.4 to make +> > adding LIMIT later non-initdb earlier. Anyway, here it's +> > again. +> +> Already applied. I assume it is the same as the one I applied. + + Seen, thanks. Your 'Applied' just arrived after I packed it + again. It's the same. + +> We are close to final, and can easily put it in 6.4.1, which I am sure +> we will need, and if we split CVS trees, you'll have lots of minor +> versions to pick from. :-) +> +> Seems like it would be a nice minor release item, but the problem is +> that minor releases aren't tested as much as major ones. How confident +> are you in the code? What do others thing? + + I regression tested it, and did additional tests in the + SPI/PL area. It works. It only touches the parser and the + executor. Rules, planner/optimizer just bypass the values in + the parsetree. The parser and the executor are parts of + Postgres I feel very familiar with (not so in the optimizer). + I trust in the code and would use it in a production + environment. + + It's below. + + +Jan + +-- + +#======================================================================# +# It's easier to get forgiveness for being wrong than for being right. # +# Let's break this rule - forgive me. # +#======================================== jwieck@debis.com (Jan Wieck) # + + +diff -cr src.orig/backend/commands/command.c src/backend/commands/command.c +*** src.orig/backend/commands/command.c Fri Oct 16 11:53:38 1998 +--- src/backend/commands/command.c Fri Oct 16 12:56:44 1998 +*************** +*** 39,44 **** +--- 39,45 ---- + #include "utils/mcxt.h" + #include "utils/portal.h" + #include "utils/syscache.h" ++ #include "string.h" + + /* ---------------- + * PortalExecutorHeapMemory stuff +*************** +*** 101,106 **** +--- 102,108 ---- + int feature; + QueryDesc *queryDesc; + MemoryContext context; ++ Const limcount; + + /* ---------------- + * sanity checks +*************** +*** 113,118 **** +--- 115,134 ---- + } + + /* ---------------- ++ * Create a const node from the given count value ++ * ---------------- ++ */ ++ memset(&limcount, 0, sizeof(limcount)); ++ limcount.type = T_Const; ++ limcount.consttype = INT4OID; ++ limcount.constlen = sizeof(int4); ++ limcount.constvalue = (Datum)count; ++ limcount.constisnull = FALSE; ++ limcount.constbyval = TRUE; ++ limcount.constisset = FALSE; ++ limcount.constiscast = FALSE; ++ ++ /* ---------------- + * get the portal from the portal name + * ---------------- + */ +*************** +*** 176,182 **** + PortalExecutorHeapMemory = (MemoryContext) + PortalGetHeapMemory(portal); + +! ExecutorRun(queryDesc, PortalGetState(portal), feature, count); + + if (dest == None) /* MOVE */ + pfree(queryDesc); +--- 192,198 ---- + PortalExecutorHeapMemory = (MemoryContext) + PortalGetHeapMemory(portal); + +! ExecutorRun(queryDesc, PortalGetState(portal), feature, (Node *)NULL, (Node *)&limcount); + + if (dest == None) /* MOVE */ + pfree(queryDesc); +diff -cr src.orig/backend/executor/execMain.c src/backend/executor/execMain.c +*** src.orig/backend/executor/execMain.c Fri Oct 16 11:53:38 1998 +--- src/backend/executor/execMain.c Fri Oct 16 20:05:19 1998 +*************** +*** 64,69 **** +--- 64,70 ---- + static void EndPlan(Plan *plan, EState *estate); + static TupleTableSlot *ExecutePlan(EState *estate, Plan *plan, + Query *parseTree, CmdType operation, ++ int offsetTuples, + int numberTuples, ScanDirection direction, + void (*printfunc) ()); + static void ExecRetrieve(TupleTableSlot *slot, void (*printfunc) (), +*************** +*** 163,169 **** + * ---------------------------------------------------------------- + */ + TupleTableSlot * +! ExecutorRun(QueryDesc *queryDesc, EState *estate, int feature, int count) + { + CmdType operation; + Query *parseTree; +--- 164,170 ---- + * ---------------------------------------------------------------- + */ + TupleTableSlot * +! ExecutorRun(QueryDesc *queryDesc, EState *estate, int feature, Node *limoffset, Node *limcount) + { + CmdType operation; + Query *parseTree; +*************** +*** 171,176 **** +--- 172,179 ---- + TupleTableSlot *result; + CommandDest dest; + void (*destination) (); ++ int offset = 0; ++ int count = 0; + + /****************** + * sanity checks +*************** +*** 191,196 **** +--- 194,289 ---- + estate->es_processed = 0; + estate->es_lastoid = InvalidOid; + ++ /****************** ++ * if given get the offset of the LIMIT clause ++ ****************** ++ */ ++ if (limoffset != NULL) ++ { ++ Const *coffset; ++ Param *poffset; ++ ParamListInfo paramLI; ++ int i; ++ ++ switch (nodeTag(limoffset)) ++ { ++ case T_Const: ++ coffset = (Const *)limoffset; ++ offset = (int)(coffset->constvalue); ++ break; ++ ++ case T_Param: ++ poffset = (Param *)limoffset; ++ paramLI = estate->es_param_list_info; ++ ++ if (paramLI == NULL) ++ elog(ERROR, "parameter for limit offset not in executor state"); ++ for (i = 0; paramLI[i].kind != PARAM_INVALID; i++) ++ { ++ if (paramLI[i].kind == PARAM_NUM && paramLI[i].id == poffset->paramid) ++ break; ++ } ++ if (paramLI[i].kind == PARAM_INVALID) ++ elog(ERROR, "parameter for limit offset not in executor state"); ++ if (paramLI[i].isnull) ++ elog(ERROR, "limit offset cannot be NULL value"); ++ offset = (int)(paramLI[i].value); ++ ++ break; ++ ++ default: ++ elog(ERROR, "unexpected node type %d as limit offset", nodeTag(limoffset)); ++ } ++ ++ if (offset < 0) ++ elog(ERROR, "limit offset cannot be negative"); ++ } ++ ++ /****************** ++ * if given get the count of the LIMIT clause ++ ****************** ++ */ ++ if (limcount != NULL) ++ { ++ Const *ccount; ++ Param *pcount; ++ ParamListInfo paramLI; ++ int i; ++ ++ switch (nodeTag(limcount)) ++ { ++ case T_Const: ++ ccount = (Const *)limcount; ++ count = (int)(ccount->constvalue); ++ break; ++ ++ case T_Param: ++ pcount = (Param *)limcount; ++ paramLI = estate->es_param_list_info; ++ ++ if (paramLI == NULL) ++ elog(ERROR, "parameter for limit count not in executor state"); ++ for (i = 0; paramLI[i].kind != PARAM_INVALID; i++) ++ { ++ if (paramLI[i].kind == PARAM_NUM && paramLI[i].id == pcount->paramid) ++ break; ++ } ++ if (paramLI[i].kind == PARAM_INVALID) ++ elog(ERROR, "parameter for limit count not in executor state"); ++ if (paramLI[i].isnull) ++ elog(ERROR, "limit count cannot be NULL value"); ++ count = (int)(paramLI[i].value); ++ ++ break; ++ ++ default: ++ elog(ERROR, "unexpected node type %d as limit count", nodeTag(limcount)); ++ } ++ ++ if (count < 0) ++ elog(ERROR, "limit count cannot be negative"); ++ } ++ + switch (feature) + { + +*************** +*** 199,205 **** + plan, + parseTree, + operation, +! ALL_TUPLES, + ForwardScanDirection, + destination); + break; +--- 292,299 ---- + plan, + parseTree, + operation, +! offset, +! count, + ForwardScanDirection, + destination); + break; +*************** +*** 208,213 **** +--- 302,308 ---- + plan, + parseTree, + operation, ++ offset, + count, + ForwardScanDirection, + destination); +*************** +*** 222,227 **** +--- 317,323 ---- + plan, + parseTree, + operation, ++ offset, + count, + BackwardScanDirection, + destination); +*************** +*** 237,242 **** +--- 333,339 ---- + plan, + parseTree, + operation, ++ 0, + ONE_TUPLE, + ForwardScanDirection, + destination); +*************** +*** 691,696 **** +--- 788,794 ---- + Plan *plan, + Query *parseTree, + CmdType operation, ++ int offsetTuples, + int numberTuples, + ScanDirection direction, + void (*printfunc) ()) +*************** +*** 742,747 **** +--- 840,859 ---- + { + result = NULL; + break; ++ } ++ ++ /****************** ++ * For now we completely execute the plan and skip ++ * result tuples if requested by LIMIT offset. ++ * Finally we should try to do it in deeper levels ++ * if possible (during index scan) ++ * - Jan ++ ****************** ++ */ ++ if (offsetTuples > 0) ++ { ++ --offsetTuples; ++ continue; + } + + /****************** +diff -cr src.orig/backend/executor/functions.c src/backend/executor/functions.c +*** src.orig/backend/executor/functions.c Fri Oct 16 11:53:38 1998 +--- src/backend/executor/functions.c Fri Oct 16 19:01:02 1998 +*************** +*** 130,135 **** +--- 130,138 ---- + None); + estate = CreateExecutorState(); + ++ if (queryTree->limitOffset != NULL || queryTree->limitCount != NULL) ++ elog(ERROR, "LIMIT clause from SQL functions not yet implemented"); ++ + if (nargs > 0) + { + int i; +*************** +*** 200,206 **** + + feature = (LAST_POSTQUEL_COMMAND(es)) ? EXEC_RETONE : EXEC_RUN; + +! return ExecutorRun(es->qd, es->estate, feature, 0); + } + + static void +--- 203,209 ---- + + feature = (LAST_POSTQUEL_COMMAND(es)) ? EXEC_RETONE : EXEC_RUN; + +! return ExecutorRun(es->qd, es->estate, feature, (Node *)NULL, (Node *)NULL); + } + + static void +diff -cr src.orig/backend/executor/spi.c src/backend/executor/spi.c +*** src.orig/backend/executor/spi.c Fri Oct 16 11:53:39 1998 +--- src/backend/executor/spi.c Fri Oct 16 19:25:33 1998 +*************** +*** 791,796 **** +--- 791,798 ---- + bool isRetrieveIntoRelation = false; + char *intoName = NULL; + int res; ++ Const tcount_const; ++ Node *count = NULL; + + switch (operation) + { +*************** +*** 825,830 **** +--- 827,865 ---- + return SPI_ERROR_OPUNKNOWN; + } + ++ /* ---------------- ++ * Get the query LIMIT tuple count ++ * ---------------- ++ */ ++ if (parseTree->limitCount != NULL) ++ { ++ /* ---------------- ++ * A limit clause in the parsetree overrides the ++ * tcount parameter ++ * ---------------- ++ */ ++ count = parseTree->limitCount; ++ } ++ else ++ { ++ /* ---------------- ++ * No LIMIT clause in parsetree. Use a local Const node ++ * to put tcount into it ++ * ---------------- ++ */ ++ memset(&tcount_const, 0, sizeof(tcount_const)); ++ tcount_const.type = T_Const; ++ tcount_const.consttype = INT4OID; ++ tcount_const.constlen = sizeof(int4); ++ tcount_const.constvalue = (Datum)tcount; ++ tcount_const.constisnull = FALSE; ++ tcount_const.constbyval = TRUE; ++ tcount_const.constisset = FALSE; ++ tcount_const.constiscast = FALSE; ++ ++ count = (Node *)&tcount_const; ++ } ++ + if (state == NULL) /* plan preparation */ + return res; + #ifdef SPI_EXECUTOR_STATS +*************** +*** 845,851 **** + return SPI_OK_CURSOR; + } + +! ExecutorRun(queryDesc, state, EXEC_FOR, tcount); + + _SPI_current->processed = state->es_processed; + if (operation == CMD_SELECT && queryDesc->dest == SPI) +--- 880,886 ---- + return SPI_OK_CURSOR; + } + +! ExecutorRun(queryDesc, state, EXEC_FOR, parseTree->limitOffset, count); + + _SPI_current->processed = state->es_processed; + if (operation == CMD_SELECT && queryDesc->dest == SPI) +diff -cr src.orig/backend/parser/analyze.c src/backend/parser/analyze.c +*** src.orig/backend/parser/analyze.c Fri Oct 16 11:53:41 1998 +--- src/backend/parser/analyze.c Fri Oct 16 13:29:27 1998 +*************** +*** 180,186 **** +--- 180,190 ---- + + case T_SelectStmt: + if (!((SelectStmt *) parseTree)->portalname) ++ { + result = transformSelectStmt(pstate, (SelectStmt *) parseTree); ++ result->limitOffset = ((SelectStmt *)parseTree)->limitOffset; ++ result->limitCount = ((SelectStmt *)parseTree)->limitCount; ++ } + else + result = transformCursorStmt(pstate, (SelectStmt *) parseTree); + break; +diff -cr src.orig/backend/parser/gram.y src/backend/parser/gram.y +*** src.orig/backend/parser/gram.y Fri Oct 16 11:53:42 1998 +--- src/backend/parser/gram.y Sun Oct 18 22:20:36 1998 +*************** +*** 45,50 **** +--- 45,51 ---- + #include "catalog/catname.h" + #include "utils/elog.h" + #include "access/xact.h" ++ #include "catalog/pg_type.h" + + #ifdef MULTIBYTE + #include "mb/pg_wchar.h" +*************** +*** 163,169 **** + sort_clause, sortby_list, index_params, index_list, name_list, + from_clause, from_list, opt_array_bounds, nest_array_bounds, + expr_list, attrs, res_target_list, res_target_list2, +! def_list, opt_indirection, group_clause, TriggerFuncArgs + + %type <node> func_return + %type <boolean> set_opt +--- 164,171 ---- + sort_clause, sortby_list, index_params, index_list, name_list, + from_clause, from_list, opt_array_bounds, nest_array_bounds, + expr_list, attrs, res_target_list, res_target_list2, +! def_list, opt_indirection, group_clause, TriggerFuncArgs, +! opt_select_limit + + %type <node> func_return + %type <boolean> set_opt +*************** +*** 192,197 **** +--- 194,201 ---- + + %type <ival> fetch_how_many + ++ %type <node> select_limit_value select_offset_value ++ + %type <list> OptSeqList + %type <defelt> OptSeqElem + +*************** +*** 267,273 **** + FALSE_P, FETCH, FLOAT, FOR, FOREIGN, FROM, FULL, + GRANT, GROUP, HAVING, HOUR_P, + IN, INNER_P, INSENSITIVE, INSERT, INTERVAL, INTO, IS, +! JOIN, KEY, LANGUAGE, LEADING, LEFT, LIKE, LOCAL, + MATCH, MINUTE_P, MONTH_P, NAMES, + NATIONAL, NATURAL, NCHAR, NEXT, NO, NOT, NULL_P, NUMERIC, + OF, ON, ONLY, OPTION, OR, ORDER, OUTER_P, +--- 271,277 ---- + FALSE_P, FETCH, FLOAT, FOR, FOREIGN, FROM, FULL, + GRANT, GROUP, HAVING, HOUR_P, + IN, INNER_P, INSENSITIVE, INSERT, INTERVAL, INTO, IS, +! JOIN, KEY, LANGUAGE, LEADING, LEFT, LIKE, LIMIT, LOCAL, + MATCH, MINUTE_P, MONTH_P, NAMES, + NATIONAL, NATURAL, NCHAR, NEXT, NO, NOT, NULL_P, NUMERIC, + OF, ON, ONLY, OPTION, OR, ORDER, OUTER_P, +*************** +*** 299,305 **** + INCREMENT, INDEX, INHERITS, INSTEAD, ISNULL, + LANCOMPILER, LISTEN, LOAD, LOCATION, LOCK_P, MAXVALUE, MINVALUE, MOVE, + NEW, NOCREATEDB, NOCREATEUSER, NONE, NOTHING, NOTIFY, NOTNULL, +! OIDS, OPERATOR, PASSWORD, PROCEDURAL, + RECIPE, RENAME, RESET, RETURNS, ROW, RULE, + SEQUENCE, SERIAL, SETOF, SHOW, START, STATEMENT, STDIN, STDOUT, TRUSTED, + UNLISTEN, UNTIL, VACUUM, VALID, VERBOSE, VERSION +--- 303,309 ---- + INCREMENT, INDEX, INHERITS, INSTEAD, ISNULL, + LANCOMPILER, LISTEN, LOAD, LOCATION, LOCK_P, MAXVALUE, MINVALUE, MOVE, + NEW, NOCREATEDB, NOCREATEUSER, NONE, NOTHING, NOTIFY, NOTNULL, +! OFFSET, OIDS, OPERATOR, PASSWORD, PROCEDURAL, + RECIPE, RENAME, RESET, RETURNS, ROW, RULE, + SEQUENCE, SERIAL, SETOF, SHOW, START, STATEMENT, STDIN, STDOUT, TRUSTED, + UNLISTEN, UNTIL, VACUUM, VALID, VERBOSE, VERSION +*************** +*** 2591,2596 **** +--- 2595,2601 ---- + result from_clause where_clause + group_clause having_clause + union_clause sort_clause ++ opt_select_limit + { + SelectStmt *n = makeNode(SelectStmt); + n->unique = $2; +*************** +*** 2602,2607 **** +--- 2607,2622 ---- + n->havingClause = $8; + n->unionClause = $9; + n->sortClause = $10; ++ if ($11 != NIL) ++ { ++ n->limitOffset = nth(0, $11); ++ n->limitCount = nth(1, $11); ++ } ++ else ++ { ++ n->limitOffset = NULL; ++ n->limitCount = NULL; ++ } + $$ = (Node *)n; + } + ; +*************** +*** 2699,2704 **** +--- 2714,2794 ---- + | ASC { $$ = "<"; } + | DESC { $$ = ">"; } + | /*EMPTY*/ { $$ = "<"; /*default*/ } ++ ; ++ ++ opt_select_limit: LIMIT select_offset_value ',' select_limit_value ++ { $$ = lappend(lappend(NIL, $2), $4); } ++ | LIMIT select_limit_value OFFSET select_offset_value ++ { $$ = lappend(lappend(NIL, $4), $2); } ++ | LIMIT select_limit_value ++ { $$ = lappend(lappend(NIL, NULL), $2); } ++ | OFFSET select_offset_value LIMIT select_limit_value ++ { $$ = lappend(lappend(NIL, $2), $4); } ++ | OFFSET select_offset_value ++ { $$ = lappend(lappend(NIL, $2), NULL); } ++ | /* EMPTY */ ++ { $$ = NIL; } ++ ; ++ ++ select_limit_value: Iconst ++ { ++ Const *n = makeNode(Const); ++ ++ if ($1 < 1) ++ elog(ERROR, "selection limit must be ALL or a positive integer value > 0"); ++ ++ n->consttype = INT4OID; ++ n->constlen = sizeof(int4); ++ n->constvalue = (Datum)$1; ++ n->constisnull = FALSE; ++ n->constbyval = TRUE; ++ n->constisset = FALSE; ++ n->constiscast = FALSE; ++ $$ = (Node *)n; ++ } ++ | ALL ++ { ++ Const *n = makeNode(Const); ++ n->consttype = INT4OID; ++ n->constlen = sizeof(int4); ++ n->constvalue = (Datum)0; ++ n->constisnull = FALSE; ++ n->constbyval = TRUE; ++ n->constisset = FALSE; ++ n->constiscast = FALSE; ++ $$ = (Node *)n; ++ } ++ | PARAM ++ { ++ Param *n = makeNode(Param); ++ n->paramkind = PARAM_NUM; ++ n->paramid = $1; ++ n->paramtype = INT4OID; ++ $$ = (Node *)n; ++ } ++ ; ++ ++ select_offset_value: Iconst ++ { ++ Const *n = makeNode(Const); ++ ++ n->consttype = INT4OID; ++ n->constlen = sizeof(int4); ++ n->constvalue = (Datum)$1; ++ n->constisnull = FALSE; ++ n->constbyval = TRUE; ++ n->constisset = FALSE; ++ n->constiscast = FALSE; ++ $$ = (Node *)n; ++ } ++ | PARAM ++ { ++ Param *n = makeNode(Param); ++ n->paramkind = PARAM_NUM; ++ n->paramid = $1; ++ n->paramtype = INT4OID; ++ $$ = (Node *)n; ++ } + ; + + /* +diff -cr src.orig/backend/parser/keywords.c src/backend/parser/keywords.c +*** src.orig/backend/parser/keywords.c Fri Oct 16 11:53:42 1998 +--- src/backend/parser/keywords.c Sun Oct 18 22:13:29 1998 +*************** +*** 128,133 **** +--- 128,134 ---- + {"leading", LEADING}, + {"left", LEFT}, + {"like", LIKE}, ++ {"limit", LIMIT}, + {"listen", LISTEN}, + {"load", LOAD}, + {"local", LOCAL}, +*************** +*** 156,161 **** +--- 157,163 ---- + {"null", NULL_P}, + {"numeric", NUMERIC}, + {"of", OF}, ++ {"offset", OFFSET}, + {"oids", OIDS}, + {"old", CURRENT}, + {"on", ON}, +diff -cr src.orig/backend/rewrite/rewriteDefine.c src/backend/rewrite/rewriteDefine.c +*** src.orig/backend/rewrite/rewriteDefine.c Fri Oct 16 11:53:46 1998 +--- src/backend/rewrite/rewriteDefine.c Fri Oct 16 13:48:55 1998 +*************** +*** 312,317 **** +--- 312,323 ---- + heap_close(event_relation); + + /* ++ * LIMIT in view is not supported ++ */ ++ if (query->limitOffset != NULL || query->limitCount != NULL) ++ elog(ERROR, "LIMIT clause not supported in views"); ++ ++ /* + * ... and finally the rule must be named _RETviewname. + */ + sprintf(expected_name, "_RET%s", event_obj->relname); +diff -cr src.orig/backend/tcop/pquery.c src/backend/tcop/pquery.c +*** src.orig/backend/tcop/pquery.c Fri Oct 16 11:53:47 1998 +--- src/backend/tcop/pquery.c Fri Oct 16 14:02:36 1998 +*************** +*** 40,46 **** + #include "commands/command.h" + + static char *CreateOperationTag(int operationType); +! static void ProcessQueryDesc(QueryDesc *queryDesc); + + + /* ---------------------------------------------------------------- +--- 40,46 ---- + #include "commands/command.h" + + static char *CreateOperationTag(int operationType); +! static void ProcessQueryDesc(QueryDesc *queryDesc, Node *limoffset, Node *limcount); + + + /* ---------------------------------------------------------------- +*************** +*** 205,211 **** + * ---------------------------------------------------------------- + */ + static void +! ProcessQueryDesc(QueryDesc *queryDesc) + { + Query *parseTree; + Plan *plan; +--- 205,211 ---- + * ---------------------------------------------------------------- + */ + static void +! ProcessQueryDesc(QueryDesc *queryDesc, Node *limoffset, Node *limcount) + { + Query *parseTree; + Plan *plan; +*************** +*** 330,336 **** + * actually run the plan.. + * ---------------- + */ +! ExecutorRun(queryDesc, state, EXEC_RUN, 0); + + /* save infos for EndCommand */ + UpdateCommandInfo(operation, state->es_lastoid, state->es_processed); +--- 330,336 ---- + * actually run the plan.. + * ---------------- + */ +! ExecutorRun(queryDesc, state, EXEC_RUN, limoffset, limcount); + + /* save infos for EndCommand */ + UpdateCommandInfo(operation, state->es_lastoid, state->es_processed); +*************** +*** 373,377 **** + print_plan(plan, parsetree); + } + else +! ProcessQueryDesc(queryDesc); + } +--- 373,377 ---- + print_plan(plan, parsetree); + } + else +! ProcessQueryDesc(queryDesc, parsetree->limitOffset, parsetree->limitCount); + } +diff -cr src.orig/include/executor/executor.h src/include/executor/executor.h +*** src.orig/include/executor/executor.h Fri Oct 16 11:53:56 1998 +--- src/include/executor/executor.h Fri Oct 16 12:04:17 1998 +*************** +*** 83,89 **** + * prototypes from functions in execMain.c + */ + extern TupleDesc ExecutorStart(QueryDesc *queryDesc, EState *estate); +! extern TupleTableSlot *ExecutorRun(QueryDesc *queryDesc, EState *estate, int feature, int count); + extern void ExecutorEnd(QueryDesc *queryDesc, EState *estate); + extern HeapTuple ExecConstraints(char *caller, Relation rel, HeapTuple tuple); + #ifdef QUERY_LIMIT +--- 83,89 ---- + * prototypes from functions in execMain.c + */ + extern TupleDesc ExecutorStart(QueryDesc *queryDesc, EState *estate); +! extern TupleTableSlot *ExecutorRun(QueryDesc *queryDesc, EState *estate, int feature, Node *limoffset, Node *limcount); + extern void ExecutorEnd(QueryDesc *queryDesc, EState *estate); + extern HeapTuple ExecConstraints(char *caller, Relation rel, HeapTuple tuple); + #ifdef QUERY_LIMIT + + |