Used: 438105027512, Limit: 438103947386. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit目前我设置比较这个限制的值大，还是报错超出限制，

💬 StarRocks 用户问答 功能使用相关
#282

好的 麻烦您了 凑字

#283

还是报hash join问题，这个我们还要加

#284

发一下explain costs 谢谢

#285

Explain String
PLAN FRAGMENT 0(F22)
Output Exprs:60: count
Input Partition: UNPARTITIONED
RESULT SINK

33:AGGREGATE (merge finalize)
| aggregate: count[([60: count, BIGINT, false]); args: ; result: BIGINT; args nullable: true; result nullable: false]
| cardinality: 1
| column statistics:
| * count–>[0.0, 1.0, 0.0, 8.0, 1.0] ESTIMATE
|
32:EXCHANGE
cardinality: 1

PLAN FRAGMENT 1(F21)

Input Partition: HASH_PARTITIONED: 53: n_name, 57: year
OutPut Partition: UNPARTITIONED
OutPut Exchange Id: 32

31:AGGREGATE (update serialize)
| aggregate: count[(*); args: ; result: BIGINT; args nullable: false; result nullable: false]
| cardinality: 1
| column statistics:
| * count–>[0.0, 98.4375, 0.0, 8.0, 1.0] ESTIMATE
|
30:Project
| output columns:
| 57 <-> [57: year, SMALLINT, true]
| cardinality: 98
| column statistics:
| * year–>[1992.0, 1998.0, 0.0, 2.0, 7.0] ESTIMATE
|
29:AGGREGATE (merge finalize)
| group by: [53: n_name, VARCHAR, true], [57: year, SMALLINT, true]
| cardinality: 98
| column statistics:
| * n_name–>[-Infinity, Infinity, 0.0, 7.08, 25.0] ESTIMATE
| * year–>[1992.0, 1998.0, 0.0, 2.0, 7.0] ESTIMATE
|
28:EXCHANGE
cardinality: 98

PLAN FRAGMENT 2(F20)

Input Partition: HASH_PARTITIONED: 47: s_nationkey
OutPut Partition: HASH_PARTITIONED: 53: n_name, 57: year
OutPut Exchange Id: 28

27:AGGREGATE (update serialize)
| STREAMING
| group by: [53: n_name, VARCHAR, true], [57: year, SMALLINT, true]
| cardinality: 98
| column statistics:
| * n_name–>[-Infinity, Infinity, 0.0, 7.08, 25.0] ESTIMATE
| * year–>[1992.0, 1998.0, 0.0, 2.0, 7.0] ESTIMATE
|
26:Project
| output columns:
| 53 <-> [53: n_name, CHAR, true]
| 57 <-> year[([22: o_orderdate, DATE, true]); args: DATE; result: SMALLINT; args nullable: true; result nullable: true]
| cardinality: 1766162758
| column statistics:
| * n_name–>[-Infinity, Infinity, 0.0, 7.08, 25.0] ESTIMATE
| * year–>[1992.0, 1998.0, 0.0, 2.0, 7.0] ESTIMATE
|
26:Project
| output columns:
| 53 <-> [53: n_name, CHAR, true]
| 57 <-> year[([22: o_orderdate, DATE, true]); args: DATE; result: SMALLINT; args nullable: true; result nullable: true]
| cardinality: 1766162758
| column statistics:
| * n_name–>[-Infinity, Infinity, 0.0, 7.08, 25.0] ESTIMATE
| * year–>[1992.0, 1998.0, 0.0, 2.0, 7.0] ESTIMATE
|
25:HASH JOIN
| join op: INNER JOIN (PARTITIONED)
| equal join conjunct: [47: s_nationkey, INT, true] = [52: n_nationkey, INT, true]
| build runtime filters:
| - filter_id = 0, build_expr = (52: n_nationkey), remote = true
| output columns: 22, 53
| cardinality: 1766162758
| column statistics:
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 357.0731707317073] ESTIMATE
| * s_nationkey–>[0.0, 24.0, 0.0, 4.0, 25.0] ESTIMATE
| * n_nationkey–>[0.0, 24.0, 0.0, 4.0, 25.0] ESTIMATE
| * n_name–>[-Infinity, Infinity, 0.0, 7.08, 25.0] ESTIMATE
| * year–>[1992.0, 1998.0, 0.0, 2.0, 7.0] ESTIMATE
|
|----24:EXCHANGE
| cardinality: 25
|
22:EXCHANGE
cardinality: 1766162758

PLAN FRAGMENT 3(F18)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 52: n_nationkey
OutPut Exchange Id: 24

23:OlapScanNode
table: nation, rollup: nation
preAggregation: on
partitionsRatio=1/1, tabletsRatio=1/1
tabletList=31555
actualRows=25, avgRowSize=11.08
cardinality: 25
column statistics:
* n_nationkey–>[0.0, 24.0, 0.0, 4.0, 25.0] ESTIMATE
* n_name–>[-Infinity, Infinity, 0.0, 7.08, 25.0] ESTIMATE

PLAN FRAGMENT 4(F16)

Input Partition: HASH_PARTITIONED: 3: l_suppkey
OutPut Partition: HASH_PARTITIONED: 47: s_nationkey
OutPut Exchange Id: 22

21:Project
| output columns:
| 22 <-> [22: o_orderdate, DATE, true]
| 47 <-> [47: s_nationkey, INT, true]
| cardinality: 1766162758
| column statistics:
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 357.0731707317073] ESTIMATE
| * s_nationkey–>[0.0, 24.0, 0.0, 4.0, 25.0] ESTIMATE
|
20:HASH JOIN
| join op: INNER JOIN (PARTITIONED)
| equal join conjunct: [3: l_suppkey, INT, true] = [44: s_suppkey, INT, true]
| output columns: 22, 47
| cardinality: 1766162758
| column statistics:
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 357.0731707317073] ESTIMATE
| * s_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
| * s_nationkey–>[0.0, 24.0, 0.0, 4.0, 25.0] ESTIMATE
|
|----19:EXCHANGE
| cardinality: 100000000
|
17:EXCHANGE
cardinality: 1795948458

PLAN FRAGMENT 5(F14)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 44: s_suppkey
OutPut Exchange Id: 19

18:OlapScanNode
table: supplier, rollup: supplier
preAggregation: on
partitionsRatio=1/1, tabletsRatio=20/20
tabletList=28028010,28028012,28028014,28028016,28028018,28028020,28028022,28028024,28028026,28028028 …
actualRows=100000000, avgRowSize=8.0
cardinality: 100000000
probe runtime filters:
- filter_id = 0, probe_expr = (47: s_nationkey)
column statistics:
* s_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
* s_nationkey–>[0.0, 24.0, 0.0, 4.0, 25.0] ESTIMATE

PLAN FRAGMENT 6(F12)

Input Partition: HASH_PARTITIONED: 2: l_partkey
OutPut Partition: HASH_PARTITIONED: 3: l_suppkey
OutPut Exchange Id: 17

16:Project
| output columns:
| 3 <-> [3: l_suppkey, INT, true]
| 22 <-> [22: o_orderdate, DATE, true]
| cardinality: 1795948458
| column statistics:
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 357.0731707317073] ESTIMATE
|
15:HASH JOIN
| join op: INNER JOIN (PARTITIONED)
| equal join conjunct: [2: l_partkey, INT, true] = [34: p_partkey, INT, true]
| output columns: 3, 22
| cardinality: 1795948458
| column statistics:
| * l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 5.0E8] ESTIMATE
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 357.0731707317073] ESTIMATE
| * p_partkey–>[1.0, 2.0E9, 0.0, 4.0, 5.0E8] ESTIMATE
|
|----14:EXCHANGE
| cardinality: 500000000
|
11:EXCHANGE
cardinality: 3415249657

PLAN FRAGMENT 7(F10)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 34: p_partkey
OutPut Exchange Id: 14

13:Project
| output columns:
| 34 <-> [34: p_partkey, INT, true]
| cardinality: 500000000
| column statistics:
| * p_partkey–>[1.0, 2.0E9, 0.0, 4.0, 5.0E8] ESTIMATE
|
12:OlapScanNode
table: part, rollup: part
preAggregation: on
Predicates: 35: p_name LIKE ‘%green%’
partitionsRatio=1/1, tabletsRatio=360/360
tabletList=27728,27730,27732,27734,27736,27738,27740,27742,27744,27746 …
actualRows=2000000000, avgRowSize=36.750008
cardinality: 500000000
column statistics:
* p_partkey–>[1.0, 2.0E9, 0.0, 4.0, 5.0E8] ESTIMATE
* p_name–>[-Infinity, Infinity, 0.0, 32.750009028, 5.0E8] ESTIMATE

PLAN FRAGMENT 8(F08)

Input Partition: HASH_PARTITIONED: 61: cast, 2: l_partkey
OutPut Partition: HASH_PARTITIONED: 2: l_partkey
OutPut Exchange Id: 11

10:Project
| output columns:
| 2 <-> [2: l_partkey, INT, true]
| 3 <-> [3: l_suppkey, INT, true]
| 22 <-> [22: o_orderdate, DATE, true]
| cardinality: 3415249657
| column statistics:
| * l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 9.508206215300832E8] ESTIMATE
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 357.0731707317073] ESTIMATE
|
9:HASH JOIN
| join op: INNER JOIN (PARTITIONED)
| equal join conjunct: [61: cast, VARCHAR(1048576), true] = [29: ps_suppkey, CHAR, true]
| equal join conjunct: [2: l_partkey, INT, true] = [28: ps_partkey, INT, true]
| output columns: 2, 3, 22
| cardinality: 3415249657
| column statistics:
| * l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 9.508206215300832E8] ESTIMATE
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 357.0731707317073] ESTIMATE
| * ps_partkey–>[1.0, 2.0E9, 0.0, 4.0, 9.508206215300832E8] ESTIMATE
| * ps_suppkey–>[-Infinity, Infinity, 0.0, 7.889455872193878, 9.9797008E7] ESTIMATE
| * cast–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
|
|----8:EXCHANGE
| cardinality: 7840000000
|
6:EXCHANGE
cardinality: 950820622

PLAN FRAGMENT 9(F06)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 29: ps_suppkey, 28: ps_partkey
OutPut Exchange Id: 08

7:OlapScanNode
table: partsupp, rollup: partsupp
preAggregation: on
partitionsRatio=1/1, tabletsRatio=360/360
tabletList=27005,27007,27009,27011,27013,27015,27017,27019,27021,27023 …
actualRows=7840000000, avgRowSize=11.889456
cardinality: 7840000000
column statistics:
* ps_partkey–>[1.0, 2.0E9, 0.0, 4.0, 1.964421632E9] ESTIMATE
* ps_suppkey–>[-Infinity, Infinity, 0.0, 7.889455872193878, 9.9797008E7] ESTIMATE

PLAN FRAGMENT 10(F04)

Input Partition: HASH_PARTITIONED: 1: l_orderkey
OutPut Partition: HASH_PARTITIONED: 61: cast, 2: l_partkey
OutPut Exchange Id: 06

5:Project
| output columns:
| 2 <-> [2: l_partkey, INT, true]
| 3 <-> [3: l_suppkey, INT, true]
| 22 <-> [22: o_orderdate, DATE, true]
| 61 <-> cast([3: l_suppkey, INT, true] as VARCHAR(1048576))
| cardinality: 950820622
| column statistics:
| * l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 9.508206215300832E8] ESTIMATE
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 357.0731707317073] ESTIMATE
| * cast–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
|
4:HASH JOIN
| join op: INNER JOIN (PARTITIONED)
| equal join conjunct: [1: l_orderkey, INT, true] = [18: o_orderkey, INT, true]
| output columns: 2, 3, 22
| cardinality: 950820622
| column statistics:
| * l_orderkey–>[1.200000001E9, 2.147483623E9, 0.0, 4.0, 2.35325328E8] ESTIMATE
| * l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 9.508206215300832E8] ESTIMATE
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
| * o_orderkey–>[1.200000001E9, 2.147483623E9, 0.0, 4.0, 2.35325328E8] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 357.0731707317073] ESTIMATE
| * cast–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE
|
|----3:EXCHANGE
| cardinality: 15000000000
|
1:EXCHANGE
cardinality: 60000033942

PLAN FRAGMENT 11(F02)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 18: o_orderkey
OutPut Exchange Id: 03

2:OlapScanNode
table: orders, rollup: orders
preAggregation: on
partitionsRatio=80/82, tabletsRatio=56000/56000
tabletList=27958822,27958824,27958826,27958828,27958830,27958832,27958834,27958836,27958838,27958840 …
actualRows=15000000000, avgRowSize=8.0
cardinality: 15000000000
column statistics:
* o_orderkey–>[1.200000001E9, 2.147483623E9, 0.9842086059333334, 4.0, 2.35325328E8] ESTIMATE
* o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 357.0731707317073] ESTIMATE

PLAN FRAGMENT 12(F00)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 1: l_orderkey
OutPut Exchange Id: 01

0:OlapScanNode
table: lineitem, rollup: lineitem
preAggregation: on
partitionsRatio=84/84, tabletsRatio=58800/58800
tabletList=28126052,28126054,28126056,28126058,28126060,28126062,28126064,28126066,28126068,28126070 …
actualRows=60000033942, avgRowSize=12.0
cardinality: 60000033942
column statistics:
* l_orderkey–>[1.0, 2.147483623E9, 0.9642086391305061, 4.0, 5.34986528E8] ESTIMATE
* l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 1.885091968E9] ESTIMATE
* l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.01686464E8] ESTIMATE

#286

手动analyze之后需要手动explain一次 再执行SQL

#287

ANALYZE FULL TABLE lineitem(l_partkey,l_suppkey,l_orderkey);
ANALYZE FULL TABLE orders(o_orderkey);
ANALYZE FULL TABLE partsupp(ps_suppkey,ps_partkey);
ANALYZE FULL TABLE part(p_partkey); 执行这些，然后在执行explain 在执行sql吗

#288

是的 凑字凑字凑字

#289

还是不行，是不是没有更好的办法了

#290

能安排腾讯会议吗？我们来几位同学一起排查以下。

#291

可以的，我共享桌面对吧

#292

是的 请稍等 我们开个腾讯会议 请问您现在方便吗？

#293

可以的，我现在开始吧

#294

#腾讯会议：662-143-420
https://meeting.tencent.com/dm/9XOXiETs27tz

#295

我们已经到了，请您进腾讯会议，谢谢！

#296

麻烦稍等一下，突然发现这个电脑上没有腾讯会议，我在按照 稍等3-5分

#297

好叻 凑字凑字凑字

#298

能不能让咱们大佬们看一下tcpds10t的建表语句吗

#299

您太客气了，可以的，请把附件发一下，看看有没有优化的地方

#300

10tds第三版本.txt (26.7 KB)

#301

sql21的explain coststpch 10t explain costssql21 .txt (38.7 KB)

京ICP备2022026421号-1