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

On Wed, 17 Jan 2024 at 08:51, Michał Kłeczek <michal(at)kleczek(dot)org> wrote:
> I think that’s the main issue: what (semantically) does row_number() mean in that case? You could equally well generate random numbers?

Well, not quite random as at least row_number() would ensure the
number is unique in the result set. The point I think you're trying to
make is very valid though.

To reinforce that point, here's an example how undefined the behaviour
that Maique is relying on:

create table t (a int primary key);
insert into t values(3),(2),(4),(1),(5);

select a,row_number() over() from t; -- Seq Scan
a | row_number
---+------------
3 | 1
2 | 2
4 | 3
1 | 4
5 | 5

set enable_seqscan=0;
set enable_bitmapscan=0;

select a,row_number() over() from t; -- Index Scan
a | row_number
---+------------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5

i.e the row numbers are just assigned in whichever order they're given
to the WindowAgg node.

Maique,

As far as I see your proposal, you want to allow something that is
undefined to be reversed. I don't think this is a good idea at all.
As mentioned by others, you should have ORDER BY clauses and just add
a DESC.

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;

The planner complexity is likely fairly easy to implement that. I
don't think we'd need to generate any additional Paths. We could
invent some pathkeys_contained_in_reverse() function and switch on the
Backward flag if it is.

The complexity would be in nodeWindowAgg.c... perhaps too much
complexity for it to be worthwhile and not add additional overhead to
the non-backward case.

Or, it might be easier to invent "Backward Materialize" instead and
just have the planner use on of those instead of the final sort.

David