Re: Monotonic WindowFunc support for ntile(), percent_rank() and cume_dist()

On Tue, Jan 24, 2023 at 11:01:08AM +1300, David Rowley wrote:
> 9d9c02ccd [1] added infrastructure in the query planner and executor
> so that the executor would know to stop processing a monotonic
> WindowFunc when its value went beyond what some qual in the outer
> query could possibly match in future evaluations due to the
> WindowFunc's monotonic nature.
>
> In that commit, support was added so that the optimisation would work
> for row_number(), rank(), dense_rank() and forms of count(*). On
> further inspection, it looks like the same can be done for ntile(),
> percent_rank() and cume_dist(). These WindowFuncs are always
> monotonically increasing.
>

Silly question, but was there any reason these were omitted in the first
commit?

> diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
> index af13b8e53d..b87a624fb2 100644
> --- a/src/backend/utils/adt/windowfuncs.c
> +++ b/src/backend/utils/adt/windowfuncs.c
> @@ -288,6 +288,15 @@ window_percent_rank_support(PG_FUNCTION_ARGS)
> {
> Node *rawreq = (Node *) PG_GETARG_POINTER(0);
>
> + if (IsA(rawreq, SupportRequestWFuncMonotonic))
> + {
> + SupportRequestWFuncMonotonic *req = (SupportRequestWFuncMonotonic *) rawreq;
> +
> + /* percent_rank() is monotonically increasing */
> + req->monotonic = MONOTONICFUNC_INCREASING;
> + PG_RETURN_POINTER(req);
> + }
> +
> if (IsA(rawreq, SupportRequestOptimizeWindowClause))
> {
> SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
> @@ -362,6 +371,15 @@ window_cume_dist_support(PG_FUNCTION_ARGS)
> {
> Node *rawreq = (Node *) PG_GETARG_POINTER(0);
>
> + if (IsA(rawreq, SupportRequestWFuncMonotonic))
> + {
> + SupportRequestWFuncMonotonic *req = (SupportRequestWFuncMonotonic *) rawreq;
> +
> + /* cume_dist() is monotonically increasing */
> + req->monotonic = MONOTONICFUNC_INCREASING;
> + PG_RETURN_POINTER(req);
> + }
> +
> if (IsA(rawreq, SupportRequestOptimizeWindowClause))
> {
> SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
> @@ -465,6 +483,18 @@ window_ntile_support(PG_FUNCTION_ARGS)
> {
> Node *rawreq = (Node *) PG_GETARG_POINTER(0);
>
> + if (IsA(rawreq, SupportRequestWFuncMonotonic))
> + {
> + SupportRequestWFuncMonotonic *req = (SupportRequestWFuncMonotonic *) rawreq;
> +
> + /*
> + * ntile() is monotonically increasing as the number of buckets cannot
> + * change after the first call
> + */
> + req->monotonic = MONOTONICFUNC_INCREASING;
> + PG_RETURN_POINTER(req);
> + }
> +
> if (IsA(rawreq, SupportRequestOptimizeWindowClause))
> {
> SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;

Since all three cases are exactly the same code, maybe you could
macro-ize it and add a single comment?

- Melanie