Re: New Window Function: ROW_NUMBER_DESC() OVER() ?

On Wed, 17 Jan 2024 at 15:28, Maiquel Grassi <grassi(at)hotmail(dot)com(dot)br> wrote:
> On Wed, 17 Jan 2024 at 14:36, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> > If you were looking for something to optimize in this rough area, then
> > perhaps adding some kind of "Backward WindowAgg" node (by overloading
> > the existing node) to allow queries such as the following to be
> > executed without an additional sort.
> >
> > SELECT a,row_number() over (order by a desc) from t order by a;
>
> David, considering this optimization, allowing for that, do you believe it is plausible to try advancing towards a possible Proof of Concept (PoC) implementation?

I think the largest factor which would influence the success of that
would be how much more complex nodeWindowAgg.c would become.

There's a couple of good ways to ensure such a patch fails:

1. Copy and paste all the code out of nodeWindowAgg.c and create
nodeWindowAggBackward.c and leave a huge maintenance burden. (don't do
this)
2. Make nodeWindowAgg.c much more complex and slower by adding dozens
of conditions to check if we're in backward mode.

I've not taken the time to study nodeWindowAgg.c to know how much more
complex supporting reading the tuples backwards would make it.
Certainly the use of tuplestore_trim() would have to change and
obviously way we read stored tuples back would need to be adjusted. It
might just add much more complexity than it would be worth. Part of
the work would be finding this out.

If making the changes to nodeWindowAgg.c is too complex, then
adjusting nodeMaterial.c would at least put us in a better position
than having to sort twice. You'd have to add a bool isbackward flag
to MaterialPath and then likely add a ScanDirection normal_dir to
MaterialState then set "dir" in ExecMaterial() using
ScanDirectionCombine of the two scan directions. At least some of
what's there would work as a result of that, but likely a few other
things in ExecMaterial() would need to be rejiggered. explain.c would
need to show "Backward Material", etc.

Both cases you'd need to modify planner.c's create_one_window_path()
and invent a function such as pathkeys_count_contained_in_backward()
or at least pathkeys_contained_in_backward() to detect when you need
to use the backward node type.

I'd go looking at nodeWindowAgg.c first, if you're interested.

David