Re: FETCH FIRST clause PERCENT option

> On Aug 16, 2018, at 7:34 AM, Andres Freund <andres(at)anarazel(dot)de> 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 Major features simply not implemented yet in recent wiki
>> page [1].
>>
>> I implemented it by executing the query plan twice. One without limit
>> filter to find the total number of row that will be feed to limit node so
>> the exact limit count can be calculated and the query plan execute again
>> with limit filter with newly calculated exact count .

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. As it is, I'm just guessing here, but here goes....

> Won't that have rather massive issues with multiple evaluations of
> clauses in the query? Besides being really expensive?
>
> I think you'd have to instead spill the query results into a tuplestore
> (like we do for FOR HOLD queries at end of xact), and then do the
> computations based on that.

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 rest, right? It's
only when an explicit ordering is given that the need to store the results
arises. Even with

FETCH FIRST 50 PERCENT name FROM foo ORDER BY name;

you can return one row for every two rows that you get back from the
sort node, reducing the maximum number you need to store at any time to
no more than 25% of all rows.

Or am I missing something?

mark