Discussion: union causes unreasonable long key

[fuyufjh]

Problems

We observed several times that incorrect usage union leads to extraordinary long stream key, usually including a jsonb column. For example,

create materialized view mv1
select * from t1
union
select * from t2
union
select * from t3

RisingWave will optimize this query into

Fragment 0
StreamMaterialize
└── StreamAppendOnlyDedup 
    └── StreamExchange Hash([0, 1, 2, 3, 4, 5, 6, 7]) from 1

Fragment 1
StreamProject
└── StreamUnion { all: true }
    ├── StreamExchange Hash([8, 9]) from 2
    ├── StreamExchange Hash([8, 9]) from 3
    └── StreamExchange Hash([8, 9]) from 4

Note the StreamExchange and StreamAppendOnlyDedup (or StreamGroupTopN for non-append-only cases) are on the full columns, so the cost is high.

Even worse, the long stream key will be used by downstream MVs, which leads to even higher cost.

Solution?

More than one approaches can somehow mitigate this problem.

1. Disallow union by default (can be worked-around by setting a session variable)
2. Disallow any aggregation on a JSONB column by default (ditto.)
3. Use upstream stream key for dedup operator instead of distinct columns

Personally I prefer 2.

[st1page]

Use upstream stream key for dedup operator instead of distinct columns

I guess it is not correct SQL semantics

Disallow any aggregation on a JSONB column by default (ditto.)

+1 for this.

[st1page]

Use upstream stream key for dedup operator instead of distinct columns

I guess it is not correct SQL semantics

a case

dev=> create table t1(v int, k int primary key);
CREATE_TABLE
dev=> create table t2(v int, k int primary key);
CREATE_TABLE
dev=> insert into t1 values (1,1);
INSERT 0 1
dev=> insert into t2 values (2,1);
INSERT 0 1
dev=> select distinct on (k) * from (select * from t1 union all select * from t2);
 v | k 
---+---
 2 | 1
(1 row)

dev=> select * from t1 union select * from t2;
 v | k 
---+---
 1 | 1
 2 | 1
(2 rows)

[fuyufjh]

Then, let's try out

2. Disallow any aggregation on a JSONB column by default (ditto.)

?

Note that the aggregation is not only limited to group by. select distinct, union or group top-N are all included.

[yuhao-su]

So in general we don't want jsonb in stream key?

[fuyufjh]

So in general we don't want jsonb in stream key?

Yes. It's mostly a mistake and will lead to very long stream key.

[xxchan]

#7981 🤔