Re: Window Function "Run Conditions"

On Tue, Apr 5, 2022 at 7:49 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 5 Apr 2022 at 19:38, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)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.
>

Yeah, this is a great idea.

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.
>
>
I just finished the planner part review and thought about the
multi activeWindows
cases, I think passthrough mode should be still needed but just for multi
activeWindow cases, In the passthrough mode, we can not discard the tuples
in the same partition. Just that PARTITION BY clause should not be the
requirement
for passthrough mode and we can do such optimization. We can discuss
more after your final decision.

And I would suggest the below fastpath for this feature.

@@ -2535,7 +2535,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo
*rel,
* if it happens to reference a window
function. If so then
* it might be useful to use for the
WindowAgg's runCondition.
*/
- if (check_and_push_window_quals(subquery,
rte, rti, clause))
+ if (!subquery->hasWindowFuncs ||
check_and_push_window_quals(subquery, rte, rti, clause))
{
/*
* It's not a suitable window run
condition qual or it is,

--
Best Regards
Andy Fan