Re: Window Function "Run Conditions"

[David Rowley]

On Tue, 5 Apr 2022 at 19:38, Andy Fan <zhihui.fan1...@gmail.com> wrote:
> 1. We can do more on PASSTHROUGH, we just bypass the window function
> currently,  but IIUC we can ignore all of the following tuples in current 
> partition
> once we go into this mode.  patch 0001 shows what I mean.

Yeah, there is more performance to be had than even what you've done
there.  There's no reason really for spool_tuples() to do
tuplestore_puttupleslot() when we're not in run mode.

The attached should give slightly more performance.  I'm unsure if
there's more that can be done for window aggregates, i.e.
eval_windowaggregates()

I'll consider the idea about doing all the filtering in
nodeWindowAgg.c. For now I made find_window_run_conditions() keep the
qual so that it's still filtered in the subquery level when there is a
PARTITION BY clause. Probably the best way would be to make
nodeWindowAgg.c just loop with a for(;;) loop. I'll need to give it
more thought. I'll do that in the morning.

David

diff --git a/src/backend/executor/nodeWindowAgg.c 
b/src/backend/executor/nodeWindowAgg.c
index 869dcd74df..19fe720d9c 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -1248,6 +1248,9 @@ spool_tuples(WindowAggState *winstate, int64 pos)
        if (winstate->partition_spooled)
                return;                                 /* whole partition done 
already */
 
+       if (winstate->status == WINDOWAGG_PASSTHROUGH)
+               pos = -1;
+
        /*
         * If the tuplestore has spilled to disk, alternate reading and writing
         * becomes quite expensive due to frequent buffer flushes.  It's cheaper
@@ -1256,7 +1259,7 @@ spool_tuples(WindowAggState *winstate, int64 pos)
         * XXX this is a horrid kluge --- it'd be better to fix the performance
         * problem inside tuplestore.  FIXME
         */
-       if (!tuplestore_in_memory(winstate->buffer))
+       else if (!tuplestore_in_memory(winstate->buffer))
                pos = -1;
 
        outerPlan = outerPlanState(winstate);
@@ -1295,9 +1298,12 @@ spool_tuples(WindowAggState *winstate, int64 pos)
                        }
                }
 
-               /* Still in partition, so save it into the tuplestore */
-               tuplestore_puttupleslot(winstate->buffer, outerslot);
-               winstate->spooled_rows++;
+               if (winstate->status == WINDOWAGG_RUN)
+               {
+                       /* Still in partition, so save it into the tuplestore */
+                       tuplestore_puttupleslot(winstate->buffer, outerslot);
+                       winstate->spooled_rows++;
+               }
        }
 
        MemoryContextSwitchTo(oldcontext);