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
> <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.

> But I think i can’t do it in this way because percent have meaning
> only with total number so LIMIT_INITIAL work can’t be avoid.
>

I'm not sure I understand what you mean by "percent would have meaning
only with the total number". The important thing is that you can track
how many tuples you're supposed to return (based on the current number
of rows from the outer plan) and how many you've produced so far. And
those numbers are only growing.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services