【详述】有两个子查询结果集在1.2亿左右，然后关联之后，查询超时，超时时间调到3000s也无法显示
【背景】自己搭了一套1fe+2be节点，be配置信息是64G/16C
【业务影响】暂无业务影响
【StarRocks版本】例如：2.3.0-rc03
【集群规模】例如：1fe+2be
【机器信息】be配置信息是64G/16C,fe配置是32G/8C
【附件】
- fe.warn.log/be.warn.log/相应截图
- 慢查询：
- Profile信息 查询失败没有profile信息
- 并行度：8
- cbo是否开启：是
- be节点cpu和内存使用率截图
如下是cpu截图：
建表语句如下：
use fdm;
CREATE TABLE f05_retention_incomerate_cust_label (
l_shipdate DATE NOT NULL,
l_orderkey int NOT NULL,
agencyno int NOT NULL COMMENT “l_partkey”,
port_code int not null COMMENT “l_suppkey”,
l_linenumber int not null,
l_quantity decimal(15, 2) NOT NULL,
l_extendedprice decimal(15, 2) NOT NULL,
l_discount decimal(15, 2) NOT NULL,
l_tax decimal(15, 2) NOT NULL,
l_returnflag VARCHAR(1) NOT NULL,
l_linestatus VARCHAR(1) NOT NULL,
l_commitdate DATE NOT NULL,
l_receiptdate DATE NOT NULL,
l_shipinstruct VARCHAR(25) NOT NULL,
l_shipmode VARCHAR(10) NOT NULL,
l_comment VARCHAR(44) NOT NULL,
syscustomerid VARCHAR(44) NOT NULL COMMENT “客户号”,
cdate DATE NOT NULL COMMENT “确认日”,
totalcost decimal(38, 18) NOT NULL COMMENT “总成本”,
totalincome decimal(38, 18) NOT NULL COMMENT “总收益”,
asset decimal(38, 18) NOT NULL COMMENT “规模”
)ENGINE=OLAP
DUPLICATE KEY(l_shipdate, l_orderkey)
COMMENT “OLAP”
DISTRIBUTED BY HASH(l_orderkey) BUCKETS 96
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“colocate_with” = “tpch2”
);
CREATE TABLE v_fundinfo (
port_code int NOT NULL COMMENT “s_suppkey”,
s_name VARCHAR(25) NOT NULL,
s_address VARCHAR(40) NOT NULL,
s_nationkey int NOT NULL,
s_phone VARCHAR(15) NOT NULL,
s_acctbal decimal(15, 2) NOT NULL,
s_comment VARCHAR(101) NOT NULL,
fathercodename VARCHAR(25) NOT NULL COMMENT “主基金名称”
)ENGINE=OLAP
DUPLICATE KEY(port_code)
COMMENT “OLAP”
DISTRIBUTED BY HASH(port_code) BUCKETS 12
PROPERTIES (“replication_num” = “1”,“in_memory” = “false”,“storage_format” = “DEFAULT”
);
查询sql如下：
SELECT CASE
WHEN tmp2.Xtemp2_output < 0 THEN ‘<0’
WHEN tmp2.Xtemp2_output >= 0 THEN ‘>=0’
ELSE CAST(NULL AS VARCHAR(1))
END AS “Calculation_1065664272975536129”,
SUM(tmp3.规模) AS “TEMP_____________”,
COUNT(DISTINCT tmp3.客户号) AS “ctd_____ok”
FROM (
SELECT t.cdate AS 确认日,
t.syscustomerid AS 客户号,
t.totalcost AS 总成本,
t.totalincome AS 总收益,
t.asset AS 规模,
a.fathercodename AS 主基金名称,
ag.agencyname AS 渠道
FROM fdm.f05_retention_incomerate_cust_label t
INNER JOIN fdm.v_agencyinfo ag ON t.agencyno = ag.agencyno
INNER JOIN fdm.v_fundinfo a ON t.port_code = a.port_code
) tmp3
INNER JOIN (
SELECT tmp1.主基金名称 AS “X____”,
tmp1.客户号 AS “X__”,
tmp1.渠道 AS “X_”,
CASE
WHEN SUM(tmp1.总成本) = 0 THEN CAST(NULL AS FLOAT)
ELSE SUM(tmp1.总收益) / SUM(tmp1.总成本)
END AS “Xtemp2_output”
FROM (
SELECT t.cdate AS 确认日,
t.syscustomerid AS 客户号,
a.fathercodename AS 主基金名称,
ag.agencyname AS 渠道,
t.totalcost AS 总成本,
t.totalincome AS 总收益
FROM fdm.f05_retention_incomerate_cust_label t
INNER JOIN fdm.v_agencyinfo ag ON t.agencyno = ag.agencyno
INNER JOIN fdm.v_fundinfo a ON t.port_code = a.port_code
) tmp1
WHERE CAST(tmp1.确认日 AS VARCHAR(10)) = ‘2022-01-31’
GROUP BY tmp1.主基金名称,
tmp1.客户号,
tmp1.渠道
) tmp2
ON tmp3.主基金名称 = tmp2.X____
and tmp3.客户号 = tmp2.X__
and tmp3.渠道 = tmp2.X_
WHERE CAST(tmp3.确认日 AS VARCHAR(10)) = ‘2022-01-31’
GROUP BY CASE
WHEN tmp2.Xtemp2_output < 0 THEN ‘<0’
WHEN tmp2.Xtemp2_output >= 0 THEN ‘>=0’
ELSE CAST(NULL AS VARCHAR(1))
END
LIMIT 500