Re: FETCH FIRST clause WITH TIES option

On Fri, Nov 29, 2019 at 05:19:00AM +0000, Andrew Gierth wrote:
>>>>>> "Alvaro" == Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>
> Alvaro> First, I noticed that there's a significant unanswered issue
> Alvaro> from Andrew Gierth about this using a completely different
> Alvaro> mechanism, namely an implicit window function. Robert was
> Alvaro> concerned about the performance of Andrew's proposed approach,
> Alvaro> but I don't see any reply from Surafel on the whole issue.
>
> Alvaro> Andrew: Would you rather not have this feature in this form at
> Alvaro> all?
>
>I have done a proof-of-concept hack for my alternative approach, which I
>will post in another thread so as not to confuse the cfbot.
>
>The main advantage, as I see it, of a window function approach is that
>it can also work for PERCENT with only a change to the generated
>expression; the executor part of the code can handle any stopping
>condition. It can also be generalized (though the POC does not do this)
>to allow an SQL-level extension, something like "LIMIT WHEN condition"
>to indicate that it should stop just before the first row for which the
>condition is true. Whether this is a desirable feature or not is another
>question.
>

For the record, the alternative approach was posted here:

https://www.postgresql.org/message-id/flat/87o8wvz253.fsf@news-spur.riddles.org.uk

I think we need to make a decision about which road to take - shall we
continue with what Surafel originally proposed, or should we instead
adopt Andrew's patch?

Andrew's patch looks significantly simpler, although it probably will
need more work to get it committable. My main concern about using window
functions was performance, so I think we should benchmark. For example,
considering window functions are not parallel safe, would this have
negative impact on parallel queries?


regards

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