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 用户问答 功能使用相关
#262

SELECT
count(*)
FROM
(
SELECT
nation,
o_year,
sum(amount) AS sum_profit
FROM
(
SELECT
n_name AS nation,
EXTRACT(YEAR
FROM
o_orderdate) AS o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
FROM
lineitem JOIN [shuffle] orders ON l_orderkey = o_orderkey
JOIN [shuffle] supplier ON l_suppkey = s_suppkey
JOIN [shuffle] partsupp ON l_suppkey = ps_suppkey AND l_partkey = ps_partkey
JOIN [shuffle] part ON l_partkey = p_partkey
JOIN [broadcast] nation ON s_nationkey = n_nationkey
WHERE
p_name LIKE ‘%green%’
) AS profit
GROUP BY
nation,
o_year
ORDER BY
nation,
o_year DESC
) AS t1;

请试一下跑这个

#263

MySQL [tcph10tmp]> SELECT
-> count(*)
-> FROM
-> (
-> SELECT
-> nation,
-> o_year,
-> sum(amount) AS sum_profit
-> FROM
-> (
-> SELECT
-> n_name AS nation,
-> EXTRACT(YEAR
-> FROM
-> o_orderdate) AS o_year,
-> l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
-> FROM
-> lineitem JOIN [shuffle] orders ON l_orderkey = o_orderkey
-> JOIN [shuffle] supplier ON l_suppkey = s_suppkey
-> JOIN [shuffle] partsupp ON l_suppkey = ps_suppkey AND l_partkey = ps_partkey
-> JOIN [shuffle] part ON l_partkey = p_partkey
-> JOIN [broadcast] nation ON s_nationkey = n_nationkey
-> WHERE
-> p_name LIKE ‘%green%’
-> ) AS profit
-> GROUP BY
-> nation,
-> o_year
-> ORDER BY
-> nation,
-> o_year DESC
-> ) AS t1;
ERROR 1064 (HY000): row count of right table in hash join > 4294967295
MySQL [tcph10tmp]> 一样的报错不行就修改一下建表语句

#264

不需要 就这个SQL 我跟研发同学反馈一下 稍等

#265

这个SQL麻烦执行explain cost 谢谢

#266

ROR 1064 (HY000): row count of right table in hash join > 4294967295
MySQL [tcph10tmp]> explain SELECT count() FROM ( SELECT nation, o_year, sum(amount) AS sum_profit FROM ( SELECT n_name AS nation, EXTRACT(YEAR FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount FROM lineitem JOIN [shuffle] orders ON l_orderkey = o_orderkey JOIN [shuffle] supplier ON l_suppkey = s_suppkey JOIN [shuffle] partsupp ON l_suppkey = ps_suppkey AND l_partkey = ps_partkey JOIN [shuffle] part ON l_partkey = p_partkey JOIN [broadcast] nation ON s_nationkey = n_nationkey WHERE p_name LIKE ‘%green%’ ) AS profit GROUP BY nation, o_year ORDER BY nation, o_year DESC ) AS t1;
±--------------------------------------------------------------------------------------------------------------+
| Explain String |
±--------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:60: count |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 31:AGGREGATE (update finalize) |
| | output: count() |
| | group by: |
| | |
| 30:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 53: n_name, 57: year |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 30 |
| UNPARTITIONED |
| |
| 29:Project |
| | <slot 57> : 57: year |
| | |
| 28:AGGREGATE (merge finalize) |
| | group by: 53: n_name, 57: year |
| | |
| 27:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 2: l_partkey |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 27 |
| HASH_PARTITIONED: 53: n_name, 57: year |
| |
| 26:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: 53: n_name, 57: year |
| | |
| 25:Project |
| | <slot 53> : 53: n_name |
| | <slot 57> : year(22: o_orderdate) |
| | |
| 24:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: |
| | equal join conjunct: 31: s_nationkey = 52: n_nationkey |
| | |
| |----23:EXCHANGE |
| | |
| 21:Project |
| | <slot 22> : 22: o_orderdate |
| | <slot 31> : 31: s_nationkey |
| | |
| 20:HASH JOIN |
| | join op: INNER JOIN (PARTITIONED) |
| | hash predicates: |
| | colocate: false, reason: |
| | equal join conjunct: 2: l_partkey = 42: p_partkey |
| | |
| |----19:EXCHANGE |
| | |
| 16:EXCHANGE |
| |
| PLAN FRAGMENT 3 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 23 |
| UNPARTITIONED |
| |
| 22:OlapScanNode |
| TABLE: nation |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: nation |
| tabletRatio=1/1 |
| tabletList=31555 |
| cardinality=25 |
| avgRowSize=11.08 |
| numNodes=0 |
| |
| PLAN FRAGMENT 4 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 19 |
| HASH_PARTITIONED: 42: p_partkey |
| |
| 18:Project |
| | <slot 42> : 42: p_partkey |
| | |
| 17:OlapScanNode |
| TABLE: part |
| PREAGGREGATION: ON |
| PREDICATES: 43: p_name LIKE ‘%green%’ |
| partitions=1/1 |
| rollup: part |
| tabletRatio=360/360 |
| tabletList=27728,27730,27732,27734,27736,27738,27740,27742,27744,27746 … |
| cardinality=500000000 |
| avgRowSize=36.750008 |
| numNodes=0 |
| |
| PLAN FRAGMENT 5 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 61: cast, 2: l_partkey |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 16 |
| HASH_PARTITIONED: 2: l_partkey |
| |
| 15:Project |
| | <slot 2> : 2: l_partkey |
| | <slot 22> : 22: o_orderdate |
| | <slot 31> : 31: s_nationkey |
| | |
| 14:HASH JOIN |
| | join op: INNER JOIN (PARTITIONED) |
| | hash predicates: |
| | colocate: false, reason: |
| | equal join conjunct: 61: cast = 37: ps_suppkey |
| | equal join conjunct: 2: l_partkey = 36: ps_partkey |
| | |
| |----13:EXCHANGE |
| | |
| 11:EXCHANGE |
| |
| PLAN FRAGMENT 6 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 13 |
| HASH_PARTITIONED: 37: ps_suppkey, 36: ps_partkey |
| |
| 12:OlapScanNode |
| TABLE: partsupp |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: partsupp |
| tabletRatio=360/360 |
| tabletList=27005,27007,27009,27011,27013,27015,27017,27019,27021,27023 … |
| cardinality=7840000000 |
| avgRowSize=11.889456 |
| numNodes=0 |
| |
| PLAN FRAGMENT 7 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 3: l_suppkey |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 11 |
| HASH_PARTITIONED: 61: cast, 2: l_partkey |
| |
| 10:Project |
| | <slot 2> : 2: l_partkey |
| | <slot 22> : 22: o_orderdate |
| | <slot 31> : 31: s_nationkey |
| | <slot 61> : CAST(3: l_suppkey AS VARCHAR(1048576)) |
| | |
| 9:HASH JOIN |
| | join op: INNER JOIN (PARTITIONED) |
| | hash predicates: |
| | colocate: false, reason: |
| | equal join conjunct: 3: l_suppkey = 28: s_suppkey |
| | |
| |----8:EXCHANGE |
| | |
| 6:EXCHANGE |
| |
| PLAN FRAGMENT 8 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 08 |
| HASH_PARTITIONED: 28: s_suppkey |
| |
| 7:OlapScanNode |
| TABLE: supplier |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: supplier |
| tabletRatio=20/20 |
| tabletList=28028010,28028012,28028014,28028016,28028018,28028020,28028022,28028024,28028026,28028028 … |
| cardinality=100000000 |
| avgRowSize=8.0 |
| numNodes=0 |
| |
| PLAN FRAGMENT 9 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 1: l_orderkey |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 06 |
| HASH_PARTITIONED: 3: l_suppkey |
| |
| 5:Project |
| | <slot 2> : 2: l_partkey |
| | <slot 3> : 3: l_suppkey |
| | <slot 22> : 22: o_orderdate |
| | |
| 4:HASH JOIN |
| | join op: INNER JOIN (PARTITIONED) |
| | hash predicates: |
| | colocate: false, reason: |
| | equal join conjunct: 1: l_orderkey = 18: o_orderkey |
| | |
| |----3:EXCHANGE |
| | |
| 1:EXCHANGE |
| |
| PLAN FRAGMENT 10 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 03 |
| HASH_PARTITIONED: 18: o_orderkey |
| |
| 2:OlapScanNode |
| TABLE: orders |
| PREAGGREGATION: ON |
| partitions=80/82 |
| rollup: orders |
| tabletRatio=56000/56000 |
| tabletList=27958822,27958824,27958826,27958828,27958830,27958832,27958834,27958836,27958838,27958840 … |
| cardinality=15000000000 |
| avgRowSize=8.0 |
| numNodes=0 |
| |
| PLAN FRAGMENT 11 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| HASH_PARTITIONED: 1: l_orderkey |
| |
| 0:OlapScanNode |
| TABLE: lineitem |
| PREAGGREGATION: ON |
| partitions=84/84 |
| rollup: lineitem |
| tabletRatio=58800/58800 |
| tabletList=28126052,28126054,28126056,28126058,28126060,28126062,28126064,28126066,28126068,28126070 … |
| cardinality=60000033942 |
| avgRowSize=12.0 |
| numNodes=0 |
±--------------------------------------------------------------------------------------------------------------+
254 rows in set (0.17 sec)

#267

因为开启了 enable_pipeline_engine=true
所以parallel_fragment_exec_instance_num=1
修改后试一下效率会不会更好

#268

SELECT
count(*)
FROM
(
SELECT
nation,
o_year,
sum(amount) AS sum_profit
FROM
(
SELECT
n_name AS nation,
EXTRACT(YEAR
FROM
o_orderdate) AS o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
FROM
lineitem JOIN [shuffle] orders ON l_orderkey = o_orderkey
JOIN [shuffle] partsupp ON l_suppkey = ps_suppkey AND l_partkey = ps_partkey
JOIN [shuffle] part ON l_partkey = p_partkey
JOIN [broadcast] supplier ON l_suppkey = s_suppkey
JOIN [broadcast] nation ON s_nationkey = n_nationkey
WHERE
p_name LIKE ‘%green%’
) AS profit
GROUP BY
nation,
o_year
ORDER BY
nation,
o_year DESC
) AS t1;

麻烦您跑一下这个

#269

好的，并行度还需要修改吗

#270

改一下 测试一下吧

#271

开启pipeline后 他会自动调节

#272

MySQL [tcph10tmp]> SELECT count(*) FROM ( SELECT nation, o_year, sum(amount) AS sum_profit FROM ( SELECT n_name AS nation, EXTRACT(YEAR FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount FROM lineitem JOIN [shuffle] orders ON l_orderkey = o_orderkey JOIN [shuffle] partsupp ON l_suppkey = ps_suppkey AND l_partkey = ps_partkey JOIN [shuffle] part ON l_partkey = p_partkey JOIN [broadcast] supplier ON l_suppkey = s_suppkey JOIN [broadcast] nation ON s_nationkey = n_nationkey WHERE p_name LIKE ‘%green%’ ) AS profit GROUP BY nation, o_year ORDER BY nation, o_year DESC ) AS t1;
ERROR 1064 (HY000): row count of right table in hash join > 4294967295

#273

explain costs + sql 拿一下执行计划 谢谢

#274

MySQL [tcph10tmp]> explain SELECT count() FROM ( SELECT nation, o_year, sum(amount) AS sum_profit FROM ( SELECT n_name AS nation, EXTRACT(YEAR FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount FROM lineitem JOIN [shuffle] orders ON l_orderkey = o_orderkey JOIN [shuffle] partsupp ON l_suppkey = ps_suppkey AND l_partkey = ps_partkey JOIN [broadcast] part ON l_partkey = p_partkey JOIN [broadcast] supplier ON l_suppkey = s_suppkey JOIN [broadcast] nation ON s_nationkey = n_nationkey WHERE p_name LIKE ‘%green%’ ) AS profit GROUP BY nation, o_year ORDER BY nation, o_year DESC ) AS t1;
±--------------------------------------------------------------------------------------------------------------+
| Explain String |
±--------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:60: count |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 29:AGGREGATE (update finalize) |
| | output: count() |
| | group by: |
| | |
| 28:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 53: n_name, 57: year |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 28 |
| UNPARTITIONED |
| |
| 27:Project |
| | <slot 57> : 57: year |
| | |
| 26:AGGREGATE (merge finalize) |
| | group by: 53: n_name, 57: year |
| | |
| 25:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 61: cast, 2: l_partkey |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 25 |
| HASH_PARTITIONED: 53: n_name, 57: year |
| |
| 24:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: 53: n_name, 57: year |
| | |
| 23:Project |
| | <slot 53> : 53: n_name |
| | <slot 57> : year(22: o_orderdate) |
| | |
| 22:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: |
| | equal join conjunct: 47: s_nationkey = 52: n_nationkey |
| | |
| |----21:EXCHANGE |
| | |
| 19:Project |
| | <slot 22> : 22: o_orderdate |
| | <slot 47> : 47: s_nationkey |
| | |
| 18:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: |
| | equal join conjunct: 3: l_suppkey = 44: s_suppkey |
| | |
| |----17:EXCHANGE |
| | |
| 15:Project |
| | <slot 3> : 3: l_suppkey |
| | <slot 22> : 22: o_orderdate |
| | |
| 14:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: |
| | equal join conjunct: 2: l_partkey = 34: p_partkey |
| | |
| |----13:EXCHANGE |
| | |
| 10:Project |
| | <slot 2> : 2: l_partkey |
| | <slot 3> : 3: l_suppkey |
| | <slot 22> : 22: o_orderdate |
| | |
| 9:HASH JOIN |
| | join op: INNER JOIN (PARTITIONED) |
| | hash predicates: |
| | colocate: false, reason: |
| | equal join conjunct: 61: cast = 29: ps_suppkey |
| | equal join conjunct: 2: l_partkey = 28: ps_partkey |
| | |
| |----8:EXCHANGE |
| | |
| 6:EXCHANGE |
| |
| PLAN FRAGMENT 3 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 21 |
| UNPARTITIONED |
| |
| 20:OlapScanNode |
| TABLE: nation |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: nation |
| tabletRatio=1/1 |
| tabletList=31555 |
| cardinality=25 |
| avgRowSize=11.08 |
| numNodes=0 |
| |
| PLAN FRAGMENT 4 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 17 |
| UNPARTITIONED |
| |
| 16:OlapScanNode |
| TABLE: supplier |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: supplier |
| tabletRatio=20/20 |
| tabletList=28028010,28028012,28028014,28028016,28028018,28028020,28028022,28028024,28028026,28028028 … |
| cardinality=100000000 |
| avgRowSize=8.0 |
| numNodes=0 |
| |
| PLAN FRAGMENT 5 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 13 |
| UNPARTITIONED |
| |
| 12:Project |
| | <slot 34> : 34: p_partkey |
| | |
| 11:OlapScanNode |
| TABLE: part |
| PREAGGREGATION: ON |
| PREDICATES: 35: p_name LIKE ‘%green%’ |
| partitions=1/1 |
| rollup: part |
| tabletRatio=360/360 |
| tabletList=27728,27730,27732,27734,27736,27738,27740,27742,27744,27746 … |
| cardinality=500000000 |
| avgRowSize=36.750008 |
| numNodes=0 |
| |
| PLAN FRAGMENT 6 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 08 |
| HASH_PARTITIONED: 29: ps_suppkey, 28: ps_partkey |
| |
| 7:OlapScanNode |
| TABLE: partsupp |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: partsupp |
| tabletRatio=360/360 |
| tabletList=27005,27007,27009,27011,27013,27015,27017,27019,27021,27023 … |
| cardinality=7840000000 |
| avgRowSize=11.889456 |
| numNodes=0 |
| |
| PLAN FRAGMENT 7 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 1: l_orderkey |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 06 |
| HASH_PARTITIONED: 61: cast, 2: l_partkey |
| |
| 5:Project |
| | <slot 2> : 2: l_partkey |
| | <slot 3> : 3: l_suppkey |
| | <slot 22> : 22: o_orderdate |
| | <slot 61> : CAST(3: l_suppkey AS VARCHAR(1048576)) |
| | |
| 4:HASH JOIN |
| | join op: INNER JOIN (PARTITIONED) |
| | hash predicates: |
| | colocate: false, reason: |
| | equal join conjunct: 1: l_orderkey = 18: o_orderkey |
| | |
| |----3:EXCHANGE |
| | |
| 1:EXCHANGE |
| |
| PLAN FRAGMENT 8 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 03 |
| HASH_PARTITIONED: 18: o_orderkey |
| |
| 2:OlapScanNode |
| TABLE: orders |
| PREAGGREGATION: ON |
| partitions=80/82 |
| rollup: orders |
| tabletRatio=56000/56000 |
| tabletList=27958822,27958824,27958826,27958828,27958830,27958832,27958834,27958836,27958838,27958840 … |
| cardinality=15000000000 |
| avgRowSize=8.0 |
| numNodes=0 |
| |
| PLAN FRAGMENT 9 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| HASH_PARTITIONED: 1: l_orderkey |
| |
| 0:OlapScanNode |
| TABLE: lineitem |
| PREAGGREGATION: ON |
| partitions=84/84 |
| rollup: lineitem |
| tabletRatio=58800/58800 |
| tabletList=28126052,28126054,28126056,28126058,28126060,28126062,28126064,28126066,28126068,28126070 … |
| cardinality=60000033942 |
| avgRowSize=12.0 |
| numNodes=0 |
±--------------------------------------------------------------------------------------------------------------+
233 rows in set (0.23 sec) 麻烦您在看一下

#275

explain costs + sql
不是 explain + sql

#276

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

32:AGGREGATE (update finalize)
| aggregate: count[(*); args: ; result: BIGINT; args nullable: false; result nullable: false]
| cardinality: 1
| column statistics:
| * count–>[0.0, 1.9869331032711797, 0.0, 8.0, 1.0] ESTIMATE
|
31:EXCHANGE
cardinality: 2

PLAN FRAGMENT 1(F21)

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

30:Project
| output columns:
| 57 <-> [57: year, SMALLINT, true]
| cardinality: 2
| column statistics:
| * year–>[1992.0, 1998.0, 0.0, 2.0, 1.0] ESTIMATE
|
29:AGGREGATE (merge finalize)
| group by: [53: n_name, VARCHAR, true], [57: year, SMALLINT, true]
| cardinality: 2
| column statistics:
| * n_name–>[-Infinity, Infinity, 0.0, 7.08, 3.5323255169265417] ESTIMATE
| * year–>[1992.0, 1998.0, 0.0, 2.0, 1.0] ESTIMATE
|
28:EXCHANGE
cardinality: 2

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: 2
| column statistics:
| * n_name–>[-Infinity, Infinity, 0.0, 7.08, 3.5323255169265417] ESTIMATE
| * year–>[1992.0, 1998.0, 0.0, 2.0, 1.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: 4
| column statistics:
| * n_name–>[-Infinity, Infinity, 0.0, 7.08, 3.5323255169265417] ESTIMATE
| * year–>[1992.0, 1998.0, 0.0, 2.0, 1.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: 4
| column statistics:
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 1.0] ESTIMATE
| * s_nationkey–>[0.0, 24.0, 0.0, 4.0, 3.5323255169265417] ESTIMATE
| * n_nationkey–>[0.0, 24.0, 0.0, 4.0, 3.5323255169265417] ESTIMATE
| * n_name–>[-Infinity, Infinity, 0.0, 7.08, 3.5323255169265417] ESTIMATE
| * year–>[1992.0, 1998.0, 0.0, 2.0, 1.0] ESTIMATE
|
|----24:EXCHANGE
| cardinality: 25
|
22:EXCHANGE
cardinality: 4

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: 4
| column statistics:
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 1.0] ESTIMATE
| * s_nationkey–>[0.0, 24.0, 0.0, 4.0, 3.5323255169265417] 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: 4
| column statistics:
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.0] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 1.0] ESTIMATE
| * s_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.0] ESTIMATE
| * s_nationkey–>[0.0, 24.0, 0.0, 4.0, 3.5323255169265417] ESTIMATE
|
|----19:EXCHANGE
| cardinality: 100000000
|
17:EXCHANGE
cardinality: 4

PLAN FRAGMENT 5(F14)
Input Partition: RANDOM
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: 4
| column statistics:
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.0] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 1.0] 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: 4
| column statistics:
| * l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 1.0] ESTIMATE
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.0] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 1.0] ESTIMATE
| * p_partkey–>[1.0, 2.0E9, 0.0, 4.0, 1.0] ESTIMATE
|
|----14:EXCHANGE
| cardinality: 500000000
|
11:EXCHANGE
cardinality: 4

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: 4
| column statistics:
| * l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 1.0] ESTIMATE
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.0] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 1.0] 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: 4
| column statistics:
| * l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 1.0] ESTIMATE
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.0] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 1.0] ESTIMATE
| * ps_partkey–>[1.0, 2.0E9, 0.0, 4.0, 1.0] ESTIMATE
| * ps_suppkey–>[-Infinity, Infinity, 0.0, 7.889455872193878, 3.9909965723692458] ESTIMATE
| * cast–>[1.0, 1.0E8, 0.0, 4.0, 1.0] ESTIMATE
|
|----8:EXCHANGE
| cardinality: 7840000000
|
6:EXCHANGE
cardinality: 1

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: 1
| column statistics:
| * l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 1.0] ESTIMATE
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.0] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 1.0] ESTIMATE
| * cast–>[1.0, 1.0E8, 0.0, 4.0, 1.0] 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: 1
| column statistics:
| * l_orderkey–>[1.0, 2.147483623E9, 0.0, 4.0, 1.0] ESTIMATE
| * l_partkey–>[1.0, 2.0E9, 0.0, 4.0, 1.0] ESTIMATE
| * l_suppkey–>[1.0, 1.0E8, 0.0, 4.0, 1.0] ESTIMATE
| * o_orderkey–>[1.0, 2.147483623E9, 0.0, 4.0, 1.0] ESTIMATE
| * o_orderdate–>[6.94224E8, 9.04608E8, 0.0, 4.0, 1.0] ESTIMATE
| * cast–>[1.0, 1.0E8, 0.0, 4.0, 1.0] 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–>[-Infinity, Infinity, 1.0, 4.0, 1.0] 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

#277

set enable_pipeline_engine=false;
set parallel_fragment_exec_instance_num=32;
麻烦您设置一下，设置后 重新跑一下。

#278

MySQL [tcph10tmp]> SELECT count(*) FROM ( SELECT nation, o_year, sum(amount) AS sum_profit FROM ( SELECT n_name AS nation, EXTRACT(YEAR FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount FROM lineitem JOIN [shuffle] orders ON l_orderkey = o_orderkey JOIN [shuffle] partsupp ON l_suppkey = ps_suppkey AND l_partkey = ps_partkey JOIN [shuffle] part ON l_partkey = p_partkey JOIN [broadcast] supplier ON l_suppkey = s_suppkey JOIN [broadcast] nation ON s_nationkey = n_nationkey WHERE p_name LIKE ‘%green%’ ) AS profit GROUP BY nation, o_year ORDER BY nation, o_year DESC ) AS t1;
ERROR 1064 (HY000): row count of right table in hash join > 4294967295

#279

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);
收集一下这些 然后跑一次 谢谢

#280

好的，我执行一下跑一下

#281

这个执行的时间有点长，执行完之后我们重新跑一下然后，在给你反馈

京ICP备2022026421号-1