Re: FETCH FIRST clause PERCENT option

On Tue, Jan 26, 2021 at 07:29:11PM +0300, Surafel Temesgen wrote: > On Mon, Jan 25, 2021 at 2:39 PM Kyotaro Horiguchi > wrote: > > > Sorry for the dealy. I started to look this. > > > > > Hi kyotaro, > Thanks for looking into this but did we agree to proceed > on this approach? I fear that it wil

Re: FETCH FIRST clause PERCENT option

On Thu, Mar 25, 2021 at 11:15:10AM -0400, David Steele wrote: > On 1/26/21 11:29 AM, Surafel Temesgen wrote: > > > > On Mon, Jan 25, 2021 at 2:39 PM Kyotaro Horiguchi > > mailto:horikyota@gmail.com>> wrote: > > > > Sorry for the dealy. I started to look this. > > > > Hi kyotaro, > > Than

Re: FETCH FIRST clause PERCENT option

On 1/26/21 11:29 AM, Surafel Temesgen wrote: On Mon, Jan 25, 2021 at 2:39 PM Kyotaro Horiguchi mailto:horikyota@gmail.com>> wrote: Sorry for the dealy. I started to look this. Hi kyotaro, Thanks for looking into this but did we agree to proceed on this approach? I fear that it will b

Re: FETCH FIRST clause PERCENT option

On Mon, Jan 25, 2021 at 2:39 PM Kyotaro Horiguchi wrote: > Sorry for the dealy. I started to look this. > > Hi kyotaro, Thanks for looking into this but did we agree to proceed on this approach? I fear that it will be the west of effort if Andrew comes up with the patch for his approach. Andrew G

Re: FETCH FIRST clause PERCENT option

On Mon, Jan 25, 2021 at 6:39 AM Kyotaro Horiguchi wrote: > > Sorry for the dealy. I started to look this. > > At Fri, 25 Sep 2020 12:25:24 +0300, Surafel Temesgen > wrote in > > Hi Michael > > On Thu, Sep 24, 2020 at 6:58 AM Michael Paquier wrote: > > > > > On Mon, Aug 10, 2020 at 01:23:44PM +0

Re: FETCH FIRST clause PERCENT option

Sorry for the dealy. I started to look this. At Fri, 25 Sep 2020 12:25:24 +0300, Surafel Temesgen wrote in > Hi Michael > On Thu, Sep 24, 2020 at 6:58 AM Michael Paquier wrote: > > > On Mon, Aug 10, 2020 at 01:23:44PM +0300, Surafel Temesgen wrote: > > > I also Implement PERCENT WITH TIES opt

Re: FETCH FIRST clause PERCENT option

Hi Michael On Thu, Sep 24, 2020 at 6:58 AM Michael Paquier wrote: > On Mon, Aug 10, 2020 at 01:23:44PM +0300, Surafel Temesgen wrote: > > I also Implement PERCENT WITH TIES option. patch is attached > > i don't start a new tread because the patches share common code > > This fails to apply per th

Re: FETCH FIRST clause PERCENT option

On Mon, Aug 10, 2020 at 01:23:44PM +0300, Surafel Temesgen wrote: > I also Implement PERCENT WITH TIES option. patch is attached > i don't start a new tread because the patches share common code This fails to apply per the CF bot. Could you send a rebase? -- Michael signature.asc Description: P

Re: FETCH FIRST clause PERCENT option

Hi > PERCENT and WITH TIES can play together, per spec. > I also Implement PERCENT WITH TIES option. patch is attached i don't start a new tread because the patches share common code regards Surafel diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 9fc

Re: FETCH FIRST clause PERCENT option

On 17/01/2020 07:20, Kyotaro Horiguchi wrote: > Thank you for the notification, Tomas. > > At Thu, 16 Jan 2020 22:33:58 +0100, Tomas Vondra > wrote in >> This patch is marked as RFC since September. Since then there was no >> discussion on this thread, but Andrew proposed an alternative approach

Re: FETCH FIRST clause PERCENT option

Thank you for the notification, Tomas. At Thu, 16 Jan 2020 22:33:58 +0100, Tomas Vondra wrote in > This patch is marked as RFC since September. Since then there was no > discussion on this thread, but Andrew proposed an alternative approach > based on window functions in a separate thread [1] (

Re: FETCH FIRST clause PERCENT option

Hi, This patch is marked as RFC since September. Since then there was no discussion on this thread, but Andrew proposed an alternative approach based on window functions in a separate thread [1] (both for this and for the WITH TIES case). I'll set this patch back to "needs review" - at this poin

Re: FETCH FIRST clause PERCENT option

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:not tested The latest patch applies cleanly and passes all tests. I believe all

Re: FETCH FIRST clause PERCENT option

At Thu, 26 Sep 2019 15:13:42 -0600, Ryan Lambert wrote in > On Thu, Sep 19, 2019 at 6:52 AM Surafel Temesgen > wrote: > > > Hi Tom, > > In the attached patch i include the comments given > > > > regards > > Surafel > > > > Patch v9 applies and passes make installcheck-world. > > > From: Tom

Re: FETCH FIRST clause PERCENT option

On Thu, Sep 19, 2019 at 6:52 AM Surafel Temesgen wrote: > Hi Tom, > In the attached patch i include the comments given > > regards > Surafel > Patch v9 applies and passes make installcheck-world. > From: Tom Lane > Date: 2019-09-05 22:26:29 > * I didn't really study the changes in nodeLimit.c

Re: FETCH FIRST clause PERCENT option

Hi Tom, In the attached patch i include the comments given regards Surafel diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 82d8140ba2..692d6492bd 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -3035,7 +3035

Re: FETCH FIRST clause PERCENT option

Hi Tom, On Fri, Sep 6, 2019 at 1:26 AM Tom Lane wrote: > Surafel Temesgen writes: > > [ percent-incremental-v8.patch ] > > I took a quick look through this. > > * Why is this adding new functionality in tuplestore.c? Especially > functionality to get out a different tuple representation than wh

Re: FETCH FIRST clause PERCENT option

Surafel Temesgen writes: > [ percent-incremental-v8.patch ] I took a quick look through this. * Why is this adding new functionality in tuplestore.c? Especially functionality to get out a different tuple representation than what was put in? I can't see a reason why nodeLimit should need that,

Re: FETCH FIRST clause PERCENT option

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: tested, passed Documentation:tested, passed Hi everyone, The v8 patch [1] applies cleanly and passes tes

Re: FETCH FIRST clause PERCENT option

On Tue, Aug 20, 2019 at 9:10 AM Kyotaro Horiguchi wrote: > Hi, > > At Wed, 7 Aug 2019 10:20:09 +0300, Surafel Temesgen > wrote in < > calay4q98xbvhtz4yj9dccmg2-s1_jurr7fyanfw+bkmr22o...@mail.gmail.com> > > Hi > > On Wed, Aug 7, 2019 at 6:11 AM Kyotaro Horiguchi < > horikyota@gmail.com> > > w

Re: FETCH FIRST clause PERCENT option

Hi, At Wed, 7 Aug 2019 10:20:09 +0300, Surafel Temesgen wrote in > Hi > On Wed, Aug 7, 2019 at 6:11 AM Kyotaro Horiguchi > wrote: > > > > > I have some comments. > > > > This patch uses distinct parameters for exact number and > > percentage. On the othe hand planner has a notion of > > tuple

Re: FETCH FIRST clause PERCENT option

On Mon, Aug 19, 2019 at 1:55 PM Erik Rijkers wrote: > Another little thing, not sure it's a bug: > > limit interprets its argument by rounding up or down as one would > expect: > > table onek limit 10.4; --> gives 10 rows > table onek limit 10.6; --> gives 11 rows > > but FETCH count PERCENT

Re: FETCH FIRST clause PERCENT option

On 2019-08-19 11:18, Surafel Temesgen wrote: [..] [percent-incremental-v7.patch] Thanks. Another little thing, not sure it's a bug: limit interprets its argument by rounding up or down as one would expect: table onek limit 10.4; --> gives 10 rows table onek limit 10.6; --> gives 11 ro

Re: FETCH FIRST clause PERCENT option

Hi Erik On Mon, Aug 19, 2019 at 10:47 AM Erik Rijkers wrote: > Hi, > > (running with those two patches applied) > >select * from onek where thousand < 5 order by thousand fetch first -1 > percent rows only > is correctly caught (with "ERROR: PERCENT must not be negative") but: > >select

Re: FETCH FIRST clause PERCENT option

On 2019-08-19 01:33, Ryan Lambert wrote: The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:tested, passed The latest patch [1]

Re: FETCH FIRST clause PERCENT option

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:tested, passed The latest patch [1] and the cleanup patch [2] apply cleanly to t

Re: FETCH FIRST clause PERCENT option

Hi On Wed, Aug 7, 2019 at 6:11 AM Kyotaro Horiguchi wrote: > > I have some comments. > > This patch uses distinct parameters for exact number and > percentage. On the othe hand planner has a notion of > tuple_fraction covering the both. The same handling is also used > for tuple number estimation

Re: FETCH FIRST clause PERCENT option

Hello. At Thu, 1 Aug 2019 15:54:11 +0300, Surafel Temesgen wrote in > > Other than that, we can rip the clause if it is 100% > > > > > > You mean if PERCENT=100 it should short circuit and run the query > > normally? I like that. > > > > The patch did not did it automatically. Its query write

Re: FETCH FIRST clause PERCENT option

Surafel, The patch did not did it automatically. Its query writer obligation to do > that currently Ok. Your latest patch [1] passes make installcheck-world, I didn't test the actual functionality this round. plan = (Plan *) make_limit(plan, > subparse->limitOffset, > - subparse->limi

Re: FETCH FIRST clause PERCENT option

Hi Ryan, sorry for not be fast to replay > I was suggesting a warning in the documentation so users aren't caught > unaware about the performance characteristics. My first version was very > rough, how about adding this in doc/src/sgml/ref/select.sgml? > > "Using PERCENT is best suited to return

Re: FETCH FIRST clause PERCENT option

Surafel, On Wed, Jul 17, 2019 at 3:45 AM Surafel Temesgen wrote: > > Hi Ryan, > On Tue, Jul 9, 2019 at 4:13 PM Ryan Lambert > wrote: > >> >> "It is possible for FETCH FIRST N PERCENT to create poorly performing >> query plans when the N supplied exceeds 50 percent. In these cases query >> exec

Re: FETCH FIRST clause PERCENT option

Hi Ryan, On Tue, Jul 9, 2019 at 4:13 PM Ryan Lambert wrote: > > "It is possible for FETCH FIRST N PERCENT to create poorly performing > query plans when the N supplied exceeds 50 percent. In these cases query > execution can take an order of magnitude longer to execute than simply > returning th

Re: FETCH FIRST clause PERCENT option

Hi Alvaro, On Wed, Jul 10, 2019 at 6:44 PM Alvaro Herrera wrote: > What's with the new tuplestore function for getting heap tuples? That > looks really odd. > Previously I create new TTSOpsMinimalTuple type slots for every tuple returned in order to fetch it from tuplestore because tuplestore

Re: FETCH FIRST clause PERCENT option

On 2019-Jul-08, Surafel Temesgen wrote: > Hi Thomas, > Thank you for informing me > > Hi Surafel, > > > > There's a call to adjust_limit_rows_costs() hiding under > > contrib/postgres_fdw, so this fails check-world. > > > > Fixed . I also include the review given by Ryan in attached patch What'

Re: FETCH FIRST clause PERCENT option

Hello. At Wed, 10 Jul 2019 15:02:57 +0900 (Tokyo Standard Time), Kyotaro Horiguchi wrote in <20190710.150257.260806103.horikyota@gmail.com> > It is seen by a simpler test. > > create table t as select a from generate_series(0, 9) a; > analyze t; > explain analyze select * from t order

Re: FETCH FIRST clause PERCENT option

Hello. At Tue, 9 Jul 2019 21:56:32 -0600, Ryan Lambert wrote in > I did some more testing. I initialized a database with 1 million rows with > indexes and joins to test against and ran pgbench with a few different > settings for % to return. I started with a base query not utilizing the > new

Re: FETCH FIRST clause PERCENT option

I did some more testing. I initialized a database with 1 million rows with indexes and joins to test against and ran pgbench with a few different settings for % to return. I started with a base query not utilizing the new functionality. The queries used are similar to my prior examples, code at [

Re: FETCH FIRST clause PERCENT option

Surafel, > The cost of FITCH FIRST N PERCENT execution in current implementation is the cost of pulling the full table plus the cost of storing and fetching the tuple from tuplestore so it can > not perform better than pulling the full table in any case . This is because we can't determined the nu

Re: FETCH FIRST clause PERCENT option

Hi Ryan, On Tue, Jul 9, 2019 at 1:27 AM Ryan Lambert wrote: > Hi Surafel, > > The v5 patch [1] applies cleanly and passes make installcheck-world. > > My concern with this is its performance. As a user I would expect using > this feature to enable queries to run faster than they would simply pul

Re: FETCH FIRST clause PERCENT option

Hi Surafel, The v5 patch [1] applies cleanly and passes make installcheck-world. My concern with this is its performance. As a user I would expect using this feature to enable queries to run faster than they would simply pulling the full table. I tested on tables ranging from 10k rows up to 10

Re: FETCH FIRST clause PERCENT option

Hi Thomas, Thank you for informing me Hi Surafel, > > There's a call to adjust_limit_rows_costs() hiding under > contrib/postgres_fdw, so this fails check-world. > Fixed . I also include the review given by Ryan in attached patch regards Surafel diff --git a/contrib/postgres_fdw/postgres_fdw.c b

Re: FETCH FIRST clause PERCENT option

The following review has been posted through the commitfest application: make installcheck-world: not tested Implements feature: tested, passed Spec compliant: not tested Documentation:not tested The basic functionality works as I expect. In the following example I wo

Re: FETCH FIRST clause PERCENT option

On Thu, Jun 27, 2019 at 9:06 PM Surafel Temesgen wrote: > The attached patch include the fix for all the comment given Hi Surafel, There's a call to adjust_limit_rows_costs() hiding under contrib/postgres_fdw, so this fails check-world. -- Thomas Munro https://enterprisedb.com

Re: FETCH FIRST clause PERCENT option

Hello, The attached patch include the fix for all the comment given regards Surafel On Mon, Apr 1, 2019 at 12:34 AM Andres Freund wrote: > Hi, > > On 2019-03-29 12:04:50 +0300, Surafel Temesgen wrote: > > > + if (node->limitOption == PERCENTAGE) > > +

Re: FETCH FIRST clause PERCENT option

On Fri, Apr 05, 2019 at 03:14:56PM +0300, Surafel Temesgen wrote: On Tue, Mar 26, 2019 at 5:46 PM Tomas Vondra wrote: On Tue, Mar 26, 2019 at 03:06:52PM +0300, Surafel Temesgen wrote: >On Thu, Feb 28, 2019 at 11:16 PM Tomas Vondra < tomas.von...@2ndquadrant.com> >wrote: > >> >> To give you a (

Re: FETCH FIRST clause PERCENT option

On Tue, Mar 26, 2019 at 5:46 PM Tomas Vondra wrote: > On Tue, Mar 26, 2019 at 03:06:52PM +0300, Surafel Temesgen wrote: > >On Thu, Feb 28, 2019 at 11:16 PM Tomas Vondra < > tomas.von...@2ndquadrant.com> > >wrote: > > > >> > >> To give you a (admittedly, somewhat contrived and artificial example):

Re: FETCH FIRST clause PERCENT option

Thank you for looking at it On Mon, Apr 1, 2019 at 12:34 AM Andres Freund wrote: > Hi, > > On 2019-03-29 12:04:50 +0300, Surafel Temesgen wrote: > > > + if (node->limitOption == PERCENTAGE) > > + { > > + w

Re: FETCH FIRST clause PERCENT option

On Tue, Mar 26, 2019 at 5:46 PM Tomas Vondra wrote: > On Tue, Mar 26, 2019 at 03:06:52PM +0300, Surafel Temesgen wrote: > > As for the OFFSET, I don't see why that would be incompatible with PERCENT > clause. I'm not sure if it should compute the percentage from the total > number of rows (includ

Re: FETCH FIRST clause PERCENT option

Andres Freund writes: >> offset_clause: >> @@ -15435,6 +15442,7 @@ reserved_keyword: >> | ONLY >> | OR >> | ORDER >> +| PERCENT >> | PLACING >> | PRIMARY >>

Re: FETCH FIRST clause PERCENT option

Hi, On 2019-03-29 12:04:50 +0300, Surafel Temesgen wrote: > + if (node->limitOption == PERCENTAGE) > + { > + while (node->position - node->offset < > node->count) > + {

Re: FETCH FIRST clause PERCENT option

Hi, On Thu, Feb 28, 2019 at 2:50 PM Kyotaro HORIGUCHI < horiguchi.kyot...@lab.ntt.co.jp> wrote: > > -* previous time we got a different result. > +* previous time we got a different result.In PERCENTAGE option > there are > +* no bound on the number of output tuples */

Re: FETCH FIRST clause PERCENT option

On Tue, Mar 26, 2019 at 03:06:52PM +0300, Surafel Temesgen wrote: On Thu, Feb 28, 2019 at 11:16 PM Tomas Vondra wrote: To give you a (admittedly, somewhat contrived and artificial example): SELECT * FROM t1 WHERE id IN ( SELECT id FROM t2 ORDER BY x FETCH FIRST 10 PERCENT ROWS ONLY

Re: FETCH FIRST clause PERCENT option

On Thu, Feb 28, 2019 at 11:16 PM Tomas Vondra wrote: > > To give you a (admittedly, somewhat contrived and artificial example): > > SELECT * FROM t1 WHERE id IN ( > SELECT id FROM t2 ORDER BY x FETCH FIRST 10 PERCENT ROWS ONLY > ); > > Maybe this example is bogus and/or does not rea

Re: Re: FETCH FIRST clause PERCENT option

Hi Surafel, On 3/1/19 8:18 PM, Tomas Vondra wrote: I'm not sure hat E-Rows and A-Rows are? I suppose that's estimated and actual - but as I explained above, that's not what I propose the incremental approach to use. Marked as Waiting for Author since it appears Tomas is waiting for a respo

Re: FETCH FIRST clause PERCENT option

On 3/1/19 2:31 AM, Kyotaro HORIGUCHI wrote: > Hello. > > At Thu, 28 Feb 2019 21:16:25 +0100, Tomas Vondra > wrote in > >>> One biggest issue seems to be we don't know the total number of > > # One *of* the biggest *issues*? > >>> outer tuples before actually reading a null tuple. I doubt o

Re: FETCH FIRST clause PERCENT option

On Fri, Mar 1, 2019 at 4:33 AM Kyotaro HORIGUCHI < horiguchi.kyot...@lab.ntt.co.jp> wrote: > Hello. > > At Thu, 28 Feb 2019 21:16:25 +0100, Tomas Vondra < > tomas.von...@2ndquadrant.com> wrote in < > fbd08ad3-5dd8-3169-6cba-38d610d7b...@2ndquadrant.com> > > > One biggest issue seems to be we don't

Re: FETCH FIRST clause PERCENT option

Hello. At Thu, 28 Feb 2019 21:16:25 +0100, Tomas Vondra wrote in > > One biggest issue seems to be we don't know the total number of # One *of* the biggest *issues*? > > outer tuples before actually reading a null tuple. I doubt of > > general shortcut for that. It also seems preventing limit

Re: FETCH FIRST clause PERCENT option

On 2/28/19 12:26 PM, Kyotaro HORIGUCHI wrote: > Hello. > > At Sat, 23 Feb 2019 22:27:44 +0100, Tomas Vondra > wrote in > <81a5c0e9-c17d-28f3-4647-8a4659cdf...@2ndquadrant.com> >> >> >> On 2/23/19 8:53 AM, Surafel Temesgen wrote: >>> >>> >>> On Sun, Feb 10, 2019 at 2:22 AM Tomas Vondra >>> mailt

Re: FETCH FIRST clause PERCENT option

At Thu, 28 Feb 2019 13:32:17 +0300, Surafel Temesgen wrote in > On Sun, Feb 24, 2019 at 12:27 AM Tomas Vondra > wrote: > > > > > I'm sorry, I still don't understand what the supposed problem is. I > > don't think it's all that different from what nodeMaterial.c does, for > > example. > > > > >

Re: FETCH FIRST clause PERCENT option

Hello. At Sat, 23 Feb 2019 22:27:44 +0100, Tomas Vondra wrote in <81a5c0e9-c17d-28f3-4647-8a4659cdf...@2ndquadrant.com> > > > On 2/23/19 8:53 AM, Surafel Temesgen wrote: > > > > > > On Sun, Feb 10, 2019 at 2:22 AM Tomas Vondra > > mailto:tomas.von...@2ndquadrant.com>> wrote: > >   > > > >

Re: FETCH FIRST clause PERCENT option

On Sun, Feb 24, 2019 at 12:27 AM Tomas Vondra wrote: > > I'm sorry, I still don't understand what the supposed problem is. I > don't think it's all that different from what nodeMaterial.c does, for > example. > > sorry for the noise .Attache is complete patch for incremental approach regards Sur

Re: FETCH FIRST clause PERCENT option

On 2/23/19 8:53 AM, Surafel Temesgen wrote: > > > On Sun, Feb 10, 2019 at 2:22 AM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: >   > > > I'm not sure I understand - are you saying every time the user does a > FETCH, we have to run the outer plan from scratch? I don't s

Re: FETCH FIRST clause PERCENT option

On Sun, Feb 10, 2019 at 2:22 AM Tomas Vondra wrote: > > I'm not sure I understand - are you saying every time the user does a > FETCH, we have to run the outer plan from scratch? I don't see why would > that be necessary? And if it is, how come there's no noticeable > performance difference? > >

Re: FETCH FIRST clause PERCENT option

On 2/1/19 12:10 PM, Surafel Temesgen wrote: > here is a rebased version of  previous  patch with planner > comment included > It's really hard to say which comment is that .. FWIW I find the changes in nodeLimit lacking sufficient comments. The comments present are somewhat obvious - what we nee

Re: FETCH FIRST clause PERCENT option

On 1/30/19 7:07 AM, Surafel Temesgen wrote: > > > On Mon, Jan 28, 2019 at 1:28 AM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > > OK. Does that mean you agree the incremental approach is reasonable? > > > there are no noticeable performance difference but i love pr

Re: FETCH FIRST clause PERCENT option

here is a rebased version of previous patch with planner comment included regards Surafel On Wed, Jan 30, 2019 at 9:07 AM Surafel Temesgen wrote: > > > On Mon, Jan 28, 2019 at 1:28 AM Tomas Vondra > wrote: > >> >> >> OK. Does that mean you agree the incremental approach is reasonable? >> >

Re: FETCH FIRST clause PERCENT option

On Mon, Jan 28, 2019 at 1:28 AM Tomas Vondra wrote: > > > OK. Does that mean you agree the incremental approach is reasonable? > there are no noticeable performance difference but i love previous approach more regarding cursor operation it fetch tuple forward and backward from tuplestore only bu

Re: FETCH FIRST clause PERCENT option

On 1/24/19 10:57 AM, Surafel Temesgen wrote: > > > On Wed, Jan 9, 2019 at 8:18 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: >   > > > See the attached patch, which recomputes the count regularly. I don't > claim the patch is committable or that it has no other bugs,

Re: FETCH FIRST clause PERCENT option

On Wed, Jan 9, 2019 at 8:18 PM Tomas Vondra wrote: > > See the attached patch, which recomputes the count regularly. I don't > claim the patch is committable or that it has no other bugs, but > hopefully it shows what I meant. > > I got only one issue it is not work well with cursor postgres=#

Re: FETCH FIRST clause PERCENT option

On 1/9/19 4:43 PM, Surafel Temesgen wrote: > > > On Wed, Jan 9, 2019 at 5:38 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > It's hard to say what exactly are you doing, because you haven't shared > any code nor examples. > > > okay i attach in progress patch >

Re: FETCH FIRST clause PERCENT option

On Wed, Jan 9, 2019 at 5:38 PM Tomas Vondra wrote: > > It's hard to say what exactly are you doing, because you haven't shared > any code nor examples. okay i attach in progress patch regards Surafel diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 4db8142afa..849

Re: FETCH FIRST clause PERCENT option

On 1/9/19 7:09 AM, Surafel Temesgen wrote: > > > On Sun, Jan 6, 2019 at 5:51 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > On 1/6/19 12:40 PM, Surafel Temesgen wrote: > > > > > > On Fri, Jan 4, 2019 at 5:27 PM Tomas Vondra > >

Re: FETCH FIRST clause PERCENT option

On Sun, Jan 6, 2019 at 5:51 PM Tomas Vondra wrote: > > On 1/6/19 12:40 PM, Surafel Temesgen wrote: > > > > > > On Fri, Jan 4, 2019 at 5:27 PM Tomas Vondra > > mailto:tomas.von...@2ndquadrant.com>> > wrote: > > > > > > What formula? All the math remains exactly the same, you just need to > >

Re: FETCH FIRST clause PERCENT option

On 1/6/19 12:40 PM, Surafel Temesgen wrote: > > > On Fri, Jan 4, 2019 at 5:27 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > What formula? All the math remains exactly the same, you just need to > update the number of rows to return and track how many rows are al

Re: FETCH FIRST clause PERCENT option

On Fri, Jan 4, 2019 at 5:27 PM Tomas Vondra wrote: > > What formula? All the math remains exactly the same, you just need to > update the number of rows to return and track how many rows are already > returned. > > I haven't tried doing that, but AFAICS you'd need to tweak how/when > node->count

Re: FETCH FIRST clause PERCENT option

On 1/4/19 8:08 AM, Surafel Temesgen wrote: > > > > On Tue, Jan 1, 2019 at 10:08 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > The execution part of the patch seems to be working correctly, but I > think there's an improvement - we don't need to execute the outer pla

Re: FETCH FIRST clause PERCENT option

On 1/4/19 7:40 AM, Surafel Temesgen wrote: > > > On Thu, Jan 3, 2019 at 4:51 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > On 1/3/19 1:00 PM, Surafel Temesgen wrote: > > Hi > > > > On Tue, Jan 1, 2019 at 10:08 PM Tomas Vondra > >

Re: FETCH FIRST clause PERCENT option

On Tue, Jan 1, 2019 at 10:08 PM Tomas Vondra wrote: > The execution part of the patch seems to be working correctly, but I > think there's an improvement - we don't need to execute the outer plan > to completion before emitting the first row. For example, let's say the > outer plan produces 1

Re: FETCH FIRST clause PERCENT option

On Thu, Jan 3, 2019 at 4:51 PM Tomas Vondra wrote: > > On 1/3/19 1:00 PM, Surafel Temesgen wrote: > > Hi > > > > On Tue, Jan 1, 2019 at 10:08 PM Tomas Vondra > > mailto:tomas.von...@2ndquadrant.com>> > wrote: > > > The execution part of the patch seems to be working correctly, but I > > t

Re: FETCH FIRST clause PERCENT option

On 1/3/19 1:00 PM, Surafel Temesgen wrote: > Hi > > On Tue, Jan 1, 2019 at 10:08 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > ... > > Firstly, the estimated number of rows should be about the same (10k) in > both cases, but the "percent" case incorrectly uses

Re: FETCH FIRST clause PERCENT option

Hi On Tue, Jan 1, 2019 at 10:08 PM Tomas Vondra wrote: > Hi, > > I've been looking at this patch today, and I think there's a couple of > issues with the costing and execution. Consider a simple table with 1M > rows: > > create table t as select i from generate_series(1,100) s(i); > > and

Re: FETCH FIRST clause PERCENT option

Hi, I've been looking at this patch today, and I think there's a couple of issues with the costing and execution. Consider a simple table with 1M rows: create table t as select i from generate_series(1,100) s(i); and these two queries, that both select 1% of rows select * from t fetch f

Re: FETCH FIRST clause PERCENT option

On Fri, Nov 30, 2018 at 10:23 AM Surafel Temesgen wrote: > Attach is a patch that include a fix for it > > By mistake the patch coerce OFFSET clause to float8 too which is not necessary . The attached patch correct it regards Surafel diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/s

Re: FETCH FIRST clause PERCENT option

On Sun, Nov 25, 2018 at 3:05 PM Vik Fearing wrote: > > I assume you meant 200 rows there, but the correct number of rows to > return is 203 for that query. Please fix it. > Attach is a patch that include a fix for it regards Surafel diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/s

Re: FETCH FIRST clause PERCENT option

Tomas Vondra writes: > On 11/25/18 1:05 PM, Vik Fearing wrote: >> I don't see how this behavior is justified by reading the SQL standard. >> Obviously only an integer number of rows is going to be returned, but >> the percentage should be calculated correctly. > Right. My draft of SQL standard sa

Re: FETCH FIRST clause PERCENT option

On 11/25/18 1:05 PM, Vik Fearing wrote: > On 25/11/2018 12:49, Surafel Temesgen wrote: >> >> >> On Sun, Nov 25, 2018 at 1:24 PM Vik Fearing > > wrote: >> >> >> Also, this query returns 210 rows instead of the expected 208: >> >>     select * >>    

Re: FETCH FIRST clause PERCENT option

> "Surafel" == Surafel Temesgen writes: Surafel> this is because fetch first values work with integer and it Surafel> change fractional number to nearest integer number like select Surafel> * from generate_series(1, 1000) fetch first 20.3 rows only; is Surafel> not an error rather it retu

Re: FETCH FIRST clause PERCENT option

On 25/11/2018 12:49, Surafel Temesgen wrote: > > > On Sun, Nov 25, 2018 at 1:24 PM Vik Fearing > wrote: > > > Also, this query returns 210 rows instead of the expected 208: > >     select * >     from generate_series(1, 1000) >     fetch fir

Re: FETCH FIRST clause PERCENT option

On Sun, Nov 25, 2018 at 1:24 PM Vik Fearing wrote: > > Also, this query returns 210 rows instead of the expected 208: > > select * > from generate_series(1, 1000) > fetch first 20.8 percent rows only > > this is because fetch first values work with integer and it change fractional nu

Re: FETCH FIRST clause PERCENT option

On 25/11/2018 10:00, Surafel Temesgen wrote: > > > > > I messed around with your changes to the grammar and it seems you don't > need to add PERCENT as a reserved keyword.  Moving this to the > unreserved > keyword section does not cause any shift/reduce errors, and the > re

Re: FETCH FIRST clause PERCENT option

> I messed around with your changes to the grammar and it seems you don't > need to add PERCENT as a reserved keyword. Moving this to the unreserved > keyword section does not cause any shift/reduce errors, and the regression > tests still pass. Relative to your patch v4, these changes help: > >

Re: FETCH FIRST clause PERCENT option

> On Sep 25, 2018, at 8:08 AM, Mark Dilger wrote: > > > >> On Sep 25, 2018, at 5:07 AM, Surafel Temesgen wrote: >> >> hey >> >> On 9/21/18, Mark Dilger wrote: >> >>> Surafel, there are no regression tests that I can see in your patch. It >>> would help if you added some, as then I coul

Re: FETCH FIRST clause PERCENT option

> On Sep 25, 2018, at 5:07 AM, Surafel Temesgen wrote: > > hey > > On 9/21/18, Mark Dilger wrote: > >> Surafel, there are no regression tests that I can see in your patch. It >> would help if you added some, as then I could precisely what behavior you >> are expecting. > thank you for look

Re: FETCH FIRST clause PERCENT option

hey On 9/21/18, Mark Dilger wrote: > Surafel, there are no regression tests that I can see in your patch. It > would help if you added some, as then I could precisely what behavior you > are expecting. thank you for looking at it .the attach patch add regression tests diff --git a/doc/src/sgml/

Re: FETCH FIRST clause PERCENT option

> On Sep 20, 2018, at 5:29 PM, Andres Freund wrote: > > Hi, > > On 2018-09-20 17:06:36 -0700, Mark Dilger wrote: >> I should think that spilling anything to a tuplestore would only be needed >> if the query contains an ORDER BY expression. If you query >> >> FETCH FIRST 50 PERCENT * FR

Re: FETCH FIRST clause PERCENT option

Hi, On 2018-09-20 17:06:36 -0700, Mark Dilger wrote: > I should think that spilling anything to a tuplestore would only be needed > if the query contains an ORDER BY expression. If you query > > FETCH FIRST 50 PERCENT * FROM foo; > > you should just return every other row, discarding the

Re: FETCH FIRST clause PERCENT option

> On Aug 16, 2018, at 7:34 AM, Andres Freund wrote: > > Hi, > > On 2018-08-16 17:27:45 +0300, Surafel Temesgen wrote: >> FETCH FIRST with PERCENT option is SQL standard that state limit count to >> be specified in a percentage in addition to specify it in exact count and >> listed as one of M

Re: FETCH FIRST clause PERCENT option

On Fri, Aug 31, 2018 at 11:42 PM Surafel Temesgen wrote: > On Tue, Aug 28, 2018 at 7:33 PM Erik Rijkers wrote: >> >> ; >> >> TRAP: FailedAssertion("!(slot != ((void *)0))", File: "execTuples.c", >> Line: 42 > > The attache patch include a fix for the crash .can you check it again? FYI this fails

Re: FETCH FIRST clause PERCENT option

On Tue, Aug 28, 2018 at 7:33 PM Erik Rijkers wrote: > ; > > TRAP: FailedAssertion("!(slot != ((void *)0))", File: "execTuples.c", > Line: 42 The attache patch include a fix for the crash .can you check it again? Regards Surafel diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.

Re: FETCH FIRST clause PERCENT option

On 2018-08-28 14:14, Surafel Temesgen wrote: On Tue, Aug 21, 2018 at 3:50 PM Andres Freund wrote: Imagine volatile functions being used. That can be problematic because of repeated side-effects, but also will lead to plainly wrong results. Consider what happens with your approach where someb

  1   2   >