Re: FETCH FIRST clause PERCENT option

On Sun, Jan 6, 2019 at 5:51 PM Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

>
> On 1/6/19 12:40 PM, Surafel Temesgen wrote:
> >
> >
> > On Fri, Jan 4, 2019 at 5:27 PM Tomas Vondra
> > <tomas(dot)vondra(at)2ndquadrant(dot)com <mailto:tomas(dot)vondra(at)2ndquadrant(dot)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
> already
> > returned.
> >
> > I haven't tried doing that, but AFAICS you'd need to tweak how/when
> > node->count is computed - instead of computing it only once it needs
> to
> > be updated after fetching each row from the subplan.
> >
> > Furthermore, you'll need to stash the subplan rows somewhere (into a
> > tuplestore probably), and whenever the node->count value increments,
> > you'll need to grab a row from the tuplestore and return that (i.e.
> > tweak node->position and set node->subSlot).
> >
> > I hope that makes sense. The one thing I'm not quite sure about is
> > whether tuplestore allows adding and getting rows at the same time.
> >
> > Does that make sense
> >
> >
> >
> > In current implementation in LIMIT_INITIAL state we execute outer
> > plan to the end , store the resulting tuples in tuplestore and
> > calculate limitCount in number .We are in this state only once and
> > did not return any tuple. Once we are at LIMIT_INWINDOW state and
> > inner node execution asking for tuple it return from tuple store
> > immediately.
> >
>
> Right.
>
> > Inorder to do fast startup what I was thinking was dividing the work
> > done at LIMIT_INITIAL state in to limitCount. For example we want
> > 0.5 percent of the result which means in LIMIT_INWINDOW state we
> > execute outer node 200 times ,store the result in tuplestore and
> > return the first tuple. if we don’t get that much tuple that means we
> > reach end of the limit.
> Yes, pretty much.
>

While working on this method i found an issue that did not work will on
percent
with fractional part like 2.6 percent which means we have to emit per every
38.4615
tuple so it have to be round up to 39 or round down to 38 either way it
leads to incorrect
result .Even with integer percentage sometimes the result came out with
fractional part
and needs rounding

regards
Surafel