Re: Monotonic WindowFunc support for ntile(), percent_rank() and cume_dist() - Mailing list pgsql-hackers

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