On Wed, 17 Jan 2024 at 15:28, Maiquel Grassi <gra...@hotmail.com.br> wrote: > On Wed, 17 Jan 2024 at 14:36, David Rowley <dgrowle...@gmail.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