As discussed offline with @RaduBerinde:

When we create a partitioned unique index, we need to validate that we don't have duplicates on the unique column(s). If we write down a validation query for that today, it would likely end up being a hash aggregation on the full table, which is not acceptable. Instead, we would want to separate the scans between the regions so that each scan is ordered, and union them (keeping order) into a streaming aggregation.

To achieve this, we may be able to extend SplitScanIntoUnionScans (as described in #55156). Additionally, we currently only apply the rule when there's a limit, but we could add a corresponding rule for aggregations.

Epic CRDB-2528