Add support for `pdb.agg()` window functions to join scan

What feature are you requesting?

Joinscan should support window functions of the form pdb.agg() OVER (), with the same limitations aggregate scan currently imposes.

This extends what was done by solving #5637.

Why are you requesting this feature?

In general, we would like to support all use-cases which we currently support atop single tables (with the basescan) atop joins as well: the use-case hasn't changed; just the data layout.

Currently, when a query utilizes a pdb.agg() window function over a join, the custom scans decline the query and fall back to PostgreSQL's standard WindowAgg executor. At execution, it then encounters and error saying pdb.agg() can only by run by paradedb.

What is your proposed implementation for this feature?

The best approach here is to duplicate the plan and join, then join the results:

• One side does the top-k and produces K rows.
• One side does the aggregations and produces a very small number of rows
• Those then get joined to produce the final set, keeping the same semantics as a bare window function.

The agg side can use the aggregate scan lowering approach with minimal modification or glue.

Downside is the join runs twice, but this is somewhat offset by allowing each side to aggressively optimize for it’s specific job.

Ensure the agg fragment is isolated enough so that later we can swap in a fanout node easily.

join ──> TopK ──> rows
join ──> agg  ──> JSON        (join built a second time)

to

join ──> fan-out ──> TopK ──> rows
                 └─> agg  ──> JSON

Full Name:

RJ Barman

Affiliation:

ParadeDB