Discussion: Use ORDER BY as part of MV's distribution key

[xxchan]

context: https://risingwave-labs.slack.com/archives/C034TRPKN1F/p1731041466238309?thread_ts=1731032250.800469&cid=C034TRPKN1F

• related Discussion: use stream_key as MV's distribution key #12824
  • Also mentioned: "would like to introduce a distribued by <column_list> syntax as well, allowing users to specify the distribution column manually"
• partly related with Support using index in backfilling #19298

Some nodes:

• dist_key just need to be subset of pk, not prefix.
• MV pk is order_by | stream_key
• before feat(optimizer): change stream join mv distribution key #13022, MV dist_key is upstream_dist_key (subset of stream_key). And we changed to whole stream_key.
• For Index, we support distributed by. Since index is essentially the same as MV, there seem to be no reason not to also support it for MV

[hzxa21]

An example to show what the dist key looks like now on main:

create table test_dist (v1 int, v2 int, v3 int, primary key (v1, v2));
create table test_dist2 (v1 int, v2 int, v3 int, primary key (v1, v2));
create table test_dist3 (v1 int, v2 int, v3 int, primary key (v1, v2));


describe test_dist;
-- primary key v1, v2
-- distribution key v1, v2

create materialized view test_dist_mv as select * from test_dist order by v1, v3;

describe test_dist_mv;
-- primary key v1, v3, v2
-- distribution key v1, v2

drop MATERIALIZED view test_join_dist;

create MATERIALIZED view test_join_dist as
select t1.v3 as a, t2.v3 as b, t3.v3 as c
from test_dist as t1
join test_dist2 as t2
on t1.v1 = t2.v1
join test_dist3 as t3
on t2.v1 = t3.v1;

describe test_join_dist;
-- primary key test_dist.v1, test_dist.v2, test_dist2.v2, test_dist3.v2, test_dist2.v1
-- distribution test_dist.v1, test_dist.v2, test_dist2.v2, test_dist3.v2, test_dist2.v1


create MATERIALIZED view test_join_dist2 as
select t1.v3 as a, t2.v3 as b, t3.v3 as c
from test_dist as t1
join test_dist2 as t2
on t1.v1 = t2.v1
join test_dist3 as t3
on t2.v1 = t3.v1
order by t1.v3, t1.v2;

describe test_join_dist2;
-- primary key test_dist.v3, test_dist.v2, test_dist.v1, test_dist2.v2, test_dist3.v2, test_dist2.v1
-- distribution key test_dist2.v1

ideally the dist key for test_dist and dist_join_dist2 can be:

describe test_dist_mv;
-- primary key v1, v3, v2
-- distribution key v1, v3, ...


describe test_join_dist2;
-- primary key test_dist.v3, test_dist.v2, test_dist.v1, test_dist2.v2, test_dist3.v2, test_dist2.v1
-- distribution key test_dist.v3, test_dist.v2, ...