【 TiDB 使用环境】测试
【 TiDB 版本】v6.5.2
在测试TPCH的Q2语句时候发现执行的特别慢，主要原因有2点：
1、子查询虽然解关联但是没有优化成窗口函数形式，导致对相同的表做了重复的HashJoin数据查询。
2、手工修改SQL语句优化成窗口函数形式，执行效率得到大幅提升，但是并非最优，当指定hint时性能进一步提升，但是cost成本评估存在问题。
因字数限制，这里只能贴图，具体执行计划可以看附件。
tpch_q2.txt (254.1 KB)
测试数据为sf=30，测试语句为Q2：
explain analyze format=true_card_cost
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 30
and p_type like '%STEEL'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100;
可以看到其执行时间为17秒，虽然对ps_supplycost=(select …)做了解关联成hashJoin，但是Selection_93子算子做了重复执行，我们参考：https://mp.weixin.qq.com/s?__biz=MzIxNTQ0MDQxNg==&mid=2247517225&idx=1&sn=67b17dba57200d4219cb6c3d3836e55b&chksm=979a8966a0ed00701700091e68656d30d3c1c59801ddfefdf91fb5e77acefbd793e8586a2fa6&scene=21#wechat_redirect
进行优化手工解关联并且利用窗口函数替换Selection_93的多次HashJoin：
explain analyze format=true_card_cost
SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
s_address, s_phone, s_comment
FROM (
SELECT MIN(ps_supplycost) OVER(PARTITION BY ps_partkey) as win_min,
ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, p_partkey, p_mfgr, s_address,
s_phone, s_comment
FROM part, partsupp, supplier, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND p_size = 30
AND p_type LIKE '%STEEL'
AND r_name = 'ASIA') as derived
WHERE ps_supplycost = derived.win_min
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;
可以看到时间有17秒缩短到了7.5秒，避免了表的重复扫描后，性能有较大幅度提升。但是需要注意的是：Projection_36 的成本为：1781021984.02
这里还有一个最大的问题是partsupp 进行全表扫描然后做hashjoin，因为表较大扫描的代价较多。如果和part表关联，利用part的谓词条件p_size = 30 AND p_type LIKE '%STEEL’进行数据过滤然后走INL_JOIN获取partsupp数据，那么性能会提升较多。我们手工指定hint进行优化：inl_join(part,partsupp)，效果如下：
explain analyze format=true_card_cost
SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
s_address, s_phone, s_comment
FROM (
SELECT /*+ inl_join(part,partsupp) */ MIN(ps_supplycost) OVER(PARTITION BY ps_partkey) as win_min,
ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, p_partkey, p_mfgr, s_address,
s_phone, s_comment
FROM part, partsupp, supplier, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND p_size = 30
AND p_type LIKE '%STEEL'
AND r_name = 'ASIA') as derived
WHERE ps_supplycost = derived.win_min
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;
可以看到时间由7.5秒再次降低到1.79秒，性能较大幅度提升。那么是不是优化器成本评估问题呢？
手工加hint后的执行计划中的Projection_35的成本为：265528233.83 ，相比之前的Projection_36 的成本为：1781021984.02 还是要小很多的。说明在关联查询部分优化器评估的没有问题，但是在ShuffleReceiver_90算子部分成本评估竟然都变成0了，然后导致后面的成本都一样，然后走了错误的执行计划。
进一步再看下虽然Projection_35显示的成本比较低，但是否会选择使用成本低的执行计划呢？
查看window下的表join的执行计划：
SELECT ps_supplycost,
ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, p_partkey, p_mfgr, s_address,
s_phone, s_comment
FROM part, partsupp, supplier, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND p_size = 30
AND p_type LIKE '%STEEL'
AND r_name = 'ASIA';
+--------------------------------------------+-------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-----------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | estCost | costFormula | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------------+-------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-----------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Projection_17 | 99248.89 | 1798623275.67 | (((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)))) + (net(23821*rowsize(76.26)*tidb_kv_net_factor(3.96))))/15.00) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(5*1*tidb_cpu_factor(49.9))) + (hashmem(5*33*tidb_mem_factor(0.2))) + (hashbuild(5*tidb_cpu_factor(49.9)))) + (cpu(5*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(300000*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(300000*1*tidb_cpu_factor(49.9))) + (hashprobe(300000*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(2.4e+07*logrowsize(219)*tikv_scan_factor(40.7))) + (net(2.4e+07*rowsize(80)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(60186*1*tidb_cpu_factor(49.9))) + (hashmem(60186*215*tidb_mem_factor(0.2))) + (hashbuild(60186*tidb_cpu_factor(49.9)))) + (cpu(60186*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(2.4e+07*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(2.4e+07*1*tidb_cpu_factor(49.9))) + (hashprobe(2.4e+07*tidb_cpu_factor(49.9)))))/5.00)) + ((hashkey(23821*1*tidb_cpu_factor(49.9))) + (hashmem(23821*76.26*tidb_mem_factor(0.2))) + (hashbuild(23821*tidb_cpu_factor(49.9)))) + (cpu(23821*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(4.81488e+06*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(4.81488e+06*1*tidb_cpu_factor(49.9))) + (hashprobe(4.81488e+06*tidb_cpu_factor(49.9)))))/5.00)) + ((cpu(19133*filters(0.09999999999999999)*tidb_cpu_factor(49.9)))/5.00)) + ((cpu(19133*filters(0.10999999999999999)*tidb_cpu_factor(49.9)))/5.00) | 19133 | root | | time:7.32s, loops:22, Concurrency:5 | tpch.partsupp.ps_supplycost, tpch.partsupp.ps_partkey, tpch.partsupp.ps_supplycost, tpch.supplier.s_acctbal, tpch.nation.n_name, tpch.supplier.s_name, tpch.part.p_partkey, tpch.part.p_mfgr, tpch.supplier.s_address, tpch.supplier.s_phone, tpch.supplier.s_comment | 3.45 MB | N/A |
| └─Projection_18 | 99248.89 | 1798602271.47 | ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)))) + (net(23821*rowsize(76.26)*tidb_kv_net_factor(3.96))))/15.00) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(5*1*tidb_cpu_factor(49.9))) + (hashmem(5*33*tidb_mem_factor(0.2))) + (hashbuild(5*tidb_cpu_factor(49.9)))) + (cpu(5*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(300000*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(300000*1*tidb_cpu_factor(49.9))) + (hashprobe(300000*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(2.4e+07*logrowsize(219)*tikv_scan_factor(40.7))) + (net(2.4e+07*rowsize(80)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(60186*1*tidb_cpu_factor(49.9))) + (hashmem(60186*215*tidb_mem_factor(0.2))) + (hashbuild(60186*tidb_cpu_factor(49.9)))) + (cpu(60186*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(2.4e+07*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(2.4e+07*1*tidb_cpu_factor(49.9))) + (hashprobe(2.4e+07*tidb_cpu_factor(49.9)))))/5.00)) + ((hashkey(23821*1*tidb_cpu_factor(49.9))) + (hashmem(23821*76.26*tidb_mem_factor(0.2))) + (hashbuild(23821*tidb_cpu_factor(49.9)))) + (cpu(23821*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(4.81488e+06*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(4.81488e+06*1*tidb_cpu_factor(49.9))) + (hashprobe(4.81488e+06*tidb_cpu_factor(49.9)))))/5.00)) + ((cpu(19133*filters(0.09999999999999999)*tidb_cpu_factor(49.9)))/5.00) | 19133 | root | | time:7.32s, loops:22, Concurrency:5 | tpch.part.p_partkey, tpch.part.p_mfgr, tpch.partsupp.ps_partkey, tpch.partsupp.ps_supplycost, tpch.supplier.s_name, tpch.supplier.s_address, tpch.supplier.s_phone, tpch.supplier.s_acctbal, tpch.supplier.s_comment, tpch.nation.n_name | 1.98 MB | N/A |
| └─HashJoin_31 | 99248.89 | 1798583176.73 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)))) + (net(23821*rowsize(76.26)*tidb_kv_net_factor(3.96))))/15.00) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(5*1*tidb_cpu_factor(49.9))) + (hashmem(5*33*tidb_mem_factor(0.2))) + (hashbuild(5*tidb_cpu_factor(49.9)))) + (cpu(5*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(300000*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(300000*1*tidb_cpu_factor(49.9))) + (hashprobe(300000*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(2.4e+07*logrowsize(219)*tikv_scan_factor(40.7))) + (net(2.4e+07*rowsize(80)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(60186*1*tidb_cpu_factor(49.9))) + (hashmem(60186*215*tidb_mem_factor(0.2))) + (hashbuild(60186*tidb_cpu_factor(49.9)))) + (cpu(60186*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(2.4e+07*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(2.4e+07*1*tidb_cpu_factor(49.9))) + (hashprobe(2.4e+07*tidb_cpu_factor(49.9)))))/5.00)) + ((hashkey(23821*1*tidb_cpu_factor(49.9))) + (hashmem(23821*76.26*tidb_mem_factor(0.2))) + (hashbuild(23821*tidb_cpu_factor(49.9)))) + (cpu(23821*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(4.81488e+06*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(4.81488e+06*1*tidb_cpu_factor(49.9))) + (hashprobe(4.81488e+06*tidb_cpu_factor(49.9)))))/5.00) | 19133 | root | | time:7.31s, loops:22, build_hash_table:{total:1.2s, fetch:1.19s, build:5.85ms}, probe:{concurrency:5, total:36.6s, max:7.32s, probe:2.05s, fetch:34.5s} | inner join, equal:[eq(tpch.partsupp.ps_partkey, tpch.part.p_partkey)] | 3.28 MB | 0 Bytes |
| ├─TableReader_61(Build) | 99248.89 | 169343893.62 | (((cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)))) + (net(23821*rowsize(76.26)*tidb_kv_net_factor(3.96))))/15.00 | 23821 | root | | time:1.2s, loops:25, cop_task: {num: 55, max: 598.7ms, min: 29.7ms, avg: 222.2ms, p95: 570.1ms, max_proc_keys: 270304, p95_proc_keys: 263136, tot_proc: 10.6s, tot_wait: 1.02s, rpc_num: 55, rpc_time: 12.2s, copr_cache: disabled, distsql_concurrency: 15} | data:Selection_60 | 160.8 KB | N/A |
| │ └─Selection_60 | 99248.89 | 2532964710.07 | (cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7))) | 23821 | cop[tikv] | | tikv_task:{proc max:578ms, min:31ms, avg: 186.6ms, p80:314ms, p95:511ms, iters:6090, tasks:55}, scan_detail: {total_process_keys: 6000000, total_process_keys_size: 980543025, total_keys: 6000055, get_snapshot_time: 109.3ms, rocksdb: {key_skipped_count: 6000000, block: {cache_hit_count: 519, read_count: 16157, read_byte: 308.5 MB, read_time: 172.5ms}}} | eq(tpch.part.p_size, 30), like(tpch.part.p_type, "%STEEL", 92) | N/A | N/A |
| │ └─TableFullScan_59 | 6000000.00 | 1934164710.07 | scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)) | 6000000 | cop[tikv] | table:part | tikv_task:{proc max:568ms, min:31ms, avg: 178.6ms, p80:309ms, p95:504ms, iters:6090, tasks:55} | keep order:false | N/A | N/A |
| └─HashJoin_34(Probe) | 3536129.03 | 1530392127.62 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(5*1*tidb_cpu_factor(49.9))) + (hashmem(5*33*tidb_mem_factor(0.2))) + (hashbuild(5*tidb_cpu_factor(49.9)))) + (cpu(5*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(300000*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(300000*1*tidb_cpu_factor(49.9))) + (hashprobe(300000*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(2.4e+07*logrowsize(219)*tikv_scan_factor(40.7))) + (net(2.4e+07*rowsize(80)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(60186*1*tidb_cpu_factor(49.9))) + (hashmem(60186*215*tidb_mem_factor(0.2))) + (hashbuild(60186*tidb_cpu_factor(49.9)))) + (cpu(60186*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(2.4e+07*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(2.4e+07*1*tidb_cpu_factor(49.9))) + (hashprobe(2.4e+07*tidb_cpu_factor(49.9)))))/5.00) | 4814880 | root | | time:7.23s, loops:4706, build_hash_table:{total:1.22s, fetch:1.17s, build:42.2ms}, probe:{concurrency:5, total:36.6s, max:7.32s, probe:19.5s, fetch:17.1s} | inner join, equal:[eq(tpch.supplier.s_suppkey, tpch.partsupp.ps_suppkey)] | 17.3 MB | 0 Bytes |
| ├─HashJoin_36(Build) | 43848.00 | 29581936.50 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(5*1*tidb_cpu_factor(49.9))) + (hashmem(5*33*tidb_mem_factor(0.2))) + (hashbuild(5*tidb_cpu_factor(49.9)))) + (cpu(5*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(300000*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(300000*1*tidb_cpu_factor(49.9))) + (hashprobe(300000*tidb_cpu_factor(49.9)))))/5.00) | 60186 | root | | time:1.21s, loops:63, build_hash_table:{total:46ms, fetch:46ms, build:5.46µs}, probe:{concurrency:5, total:6.08s, max:1.22s, probe:178.7ms, fetch:5.9s} | inner join, equal:[eq(tpch.nation.n_nationkey, tpch.supplier.s_nationkey)] | 2.21 KB | 0 Bytes |
| │ ├─HashJoin_49(Build) | 5.00 | 3005.46 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00) | 5 | root | | time:46ms, loops:2, build_hash_table:{total:45.8ms, fetch:45.8ms, build:6.89µs}, probe:{concurrency:5, total:229.3ms, max:45.9ms, probe:16.5µs, fetch:229.3ms} | inner join, equal:[eq(tpch.region.r_regionkey, tpch.nation.n_regionkey)] | 31.7 KB | 0 Bytes |
| │ │ ├─TableReader_54(Build) | 1.00 | 119.34 | (((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00 | 1 | root | | time:45.8ms, loops:2, cop_task: {num: 1, max: 47.1ms, proc_keys: 5, rpc_num: 1, rpc_time: 47.1ms, copr_cache: disabled, distsql_concurrency: 15} | data:Selection_53 | 274 Bytes | N/A |
| │ │ │ └─Selection_53 | 1.00 | 1675.92 | (cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7))) | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 559, total_keys: 6, get_snapshot_time: 14.6ms, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 8}}} | eq(tpch.region.r_name, "ASIA") | N/A | N/A |
| │ │ │ └─TableFullScan_52 | 5.00 | 1426.42 | scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)) | 5 | cop[tikv] | table:region | tikv_task:{time:0s, loops:1} | keep order:false | N/A | N/A |
| │ │ └─TableReader_51(Probe) | 25.00 | 784.55 | ((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00 | 25 | root | | time:1.51ms, loops:2, cop_task: {num: 1, max: 2.65ms, proc_keys: 25, rpc_num: 1, rpc_time: 2.63ms, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_50 | 1.00 KB | N/A |
| │ │ └─TableFullScan_50 | 25.00 | 7306.27 | scan(25*logrowsize(145.07)*tikv_scan_factor(40.7)) | 25 | cop[tikv] | table:nation | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 156.5µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 8}}} | keep order:false | N/A | N/A |
| │ └─TableReader_56(Probe) | 300000.00 | 23588902.04 | ((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00 | 300000 | root | | time:1.18s, loops:295, cop_task: {num: 14, max: 299.3ms, min: 4.04ms, avg: 86.7ms, p95: 299.3ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 580ms, tot_wait: 276ms, rpc_num: 14, rpc_time: 1.21s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_55 | 20.0 MB | N/A |
| │ └─TableFullScan_55 | 300000.00 | 94849530.61 | scan(300000*logrowsize(218)*tikv_scan_factor(40.7)) | 300000 | cop[tikv] | table:supplier | tikv_task:{proc max:104ms, min:0s, avg: 21.6ms, p80:46ms, p95:104ms, iters:348, tasks:14}, scan_detail: {total_process_keys: 300000, total_process_keys_size: 54436630, total_keys: 300014, get_snapshot_time: 2.86ms, rocksdb: {key_skipped_count: 300000, block: {cache_hit_count: 1006}}} | keep order:false | N/A | N/A |
| └─TableReader_58(Probe) | 24000000.00 | 1013174133.32 | ((scan(2.4e+07*logrowsize(219)*tikv_scan_factor(40.7))) + (net(2.4e+07*rowsize(80)*tidb_kv_net_factor(3.96))))/15.00 | 24000000 | root | | time:2.15s, loops:23505, cop_task: {num: 884, max: 362.2ms, min: 1.03ms, avg: 82.9ms, p95: 207.8ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 37.6s, tot_wait: 3.86s, rpc_num: 884, rpc_time: 1m13.2s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_57 | 37.5 MB | N/A |
| └─TableFullScan_57 | 24000000.00 | 7594411999.82 | scan(2.4e+07*logrowsize(219)*tikv_scan_factor(40.7)) | 24000000 | cop[tikv] | table:partsupp | tikv_task:{proc max:252ms, min:0s, avg: 37.7ms, p80:65ms, p95:121ms, iters:26934, tasks:884}, scan_detail: {total_process_keys: 24000000, total_process_keys_size: 4451443909, total_keys: 24000884, get_snapshot_time: 462.4ms, rocksdb: {key_skipped_count: 24000000, block: {cache_hit_count: 24961, read_count: 52662, read_byte: 873.7 MB, read_time: 625.3ms}}} | keep order:false | N/A | N/A |
+--------------------------------------------+-------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-----------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
18 rows in set, 3 warnings (7.32 sec)
HashJoin_31部分的成本为：1798583176.73
添加inl_join(part,partsupp)后观察执行计划：
explain analyze format=true_card_cost
SELECT /*+ inl_join(part,partsupp) */ ps_supplycost,
ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, p_partkey, p_mfgr, s_address,
s_phone, s_comment
FROM part, partsupp, supplier, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND p_size = 30
AND p_type LIKE '%STEEL'
AND r_name = 'ASIA';
+------------------------------------------+------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | estCost | costFormula | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------+------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Projection_16 | 58367.05 | 222627852.77 | (((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(5*1*tidb_cpu_factor(49.9))) + (hashmem(5*33*tidb_mem_factor(0.2))) + (hashbuild(5*tidb_cpu_factor(49.9)))) + (cpu(5*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(300000*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(300000*1*tidb_cpu_factor(49.9))) + (hashprobe(300000*tidb_cpu_factor(49.9)))))/5.00)) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)))) + (net(23821*rowsize(76.26)*tidb_kv_net_factor(3.96))))/15.00) + (cpu(23821*filters(0)*tidb_cpu_factor(49.9))) + (cpu(23821*10*tidb_cpu_factor(49.9))) + ((() + (((((scan(4*logrowsize(179)*tikv_scan_factor(40.7))) + (net(4*rowsize(56)*tidb_kv_net_factor(3.96))))/15.00)*23821.00)/6.00) + (cpu(95284*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(95284*0*tidb_cpu_factor(49.9))) + (hashmem(95284*56*tidb_mem_factor(0.2))) + (hashbuild(95284*tidb_cpu_factor(49.9)))))/5.00)) + ((hashkey(60186*1*tidb_cpu_factor(49.9))) + (hashmem(60186*215*tidb_mem_factor(0.2))) + (hashbuild(60186*tidb_cpu_factor(49.9)))) + (cpu(60186*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(95284*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(95284*1*tidb_cpu_factor(49.9))) + (hashprobe(95284*tidb_cpu_factor(49.9)))))/5.00)) + ((cpu(19133*filters(0.09999999999999999)*tidb_cpu_factor(49.9)))/5.00)) + ((cpu(19133*filters(0.10999999999999999)*tidb_cpu_factor(49.9)))/5.00) | 19133 | root | | time:1.78s, loops:22, Concurrency:5 | tpch.partsupp.ps_supplycost, tpch.partsupp.ps_partkey, tpch.partsupp.ps_supplycost, tpch.supplier.s_acctbal, tpch.nation.n_name, tpch.supplier.s_name, tpch.part.p_partkey, tpch.part.p_mfgr, tpch.supplier.s_address, tpch.supplier.s_phone, tpch.supplier.s_comment | 3.45 MB | N/A |
| └─Projection_17 | 58367.05 | 222606848.57 | ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(5*1*tidb_cpu_factor(49.9))) + (hashmem(5*33*tidb_mem_factor(0.2))) + (hashbuild(5*tidb_cpu_factor(49.9)))) + (cpu(5*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(300000*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(300000*1*tidb_cpu_factor(49.9))) + (hashprobe(300000*tidb_cpu_factor(49.9)))))/5.00)) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)))) + (net(23821*rowsize(76.26)*tidb_kv_net_factor(3.96))))/15.00) + (cpu(23821*filters(0)*tidb_cpu_factor(49.9))) + (cpu(23821*10*tidb_cpu_factor(49.9))) + ((() + (((((scan(4*logrowsize(179)*tikv_scan_factor(40.7))) + (net(4*rowsize(56)*tidb_kv_net_factor(3.96))))/15.00)*23821.00)/6.00) + (cpu(95284*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(95284*0*tidb_cpu_factor(49.9))) + (hashmem(95284*56*tidb_mem_factor(0.2))) + (hashbuild(95284*tidb_cpu_factor(49.9)))))/5.00)) + ((hashkey(60186*1*tidb_cpu_factor(49.9))) + (hashmem(60186*215*tidb_mem_factor(0.2))) + (hashbuild(60186*tidb_cpu_factor(49.9)))) + (cpu(60186*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(95284*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(95284*1*tidb_cpu_factor(49.9))) + (hashprobe(95284*tidb_cpu_factor(49.9)))))/5.00)) + ((cpu(19133*filters(0.09999999999999999)*tidb_cpu_factor(49.9)))/5.00) | 19133 | root | | time:1.78s, loops:22, Concurrency:5 | tpch.part.p_partkey, tpch.part.p_mfgr, tpch.partsupp.ps_partkey, tpch.partsupp.ps_supplycost, tpch.supplier.s_name, tpch.supplier.s_address, tpch.supplier.s_phone, tpch.supplier.s_acctbal, tpch.supplier.s_comment, tpch.nation.n_name | 1.98 MB | N/A |
| └─HashJoin_19 | 58367.05 | 222587753.83 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(5*1*tidb_cpu_factor(49.9))) + (hashmem(5*33*tidb_mem_factor(0.2))) + (hashbuild(5*tidb_cpu_factor(49.9)))) + (cpu(5*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(300000*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(300000*1*tidb_cpu_factor(49.9))) + (hashprobe(300000*tidb_cpu_factor(49.9)))))/5.00)) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)))) + (net(23821*rowsize(76.26)*tidb_kv_net_factor(3.96))))/15.00) + (cpu(23821*filters(0)*tidb_cpu_factor(49.9))) + (cpu(23821*10*tidb_cpu_factor(49.9))) + ((() + (((((scan(4*logrowsize(179)*tikv_scan_factor(40.7))) + (net(4*rowsize(56)*tidb_kv_net_factor(3.96))))/15.00)*23821.00)/6.00) + (cpu(95284*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(95284*0*tidb_cpu_factor(49.9))) + (hashmem(95284*56*tidb_mem_factor(0.2))) + (hashbuild(95284*tidb_cpu_factor(49.9)))))/5.00)) + ((hashkey(60186*1*tidb_cpu_factor(49.9))) + (hashmem(60186*215*tidb_mem_factor(0.2))) + (hashbuild(60186*tidb_cpu_factor(49.9)))) + (cpu(60186*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(95284*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(95284*1*tidb_cpu_factor(49.9))) + (hashprobe(95284*tidb_cpu_factor(49.9)))))/5.00) | 19133 | root | | time:1.78s, loops:22, build_hash_table:{total:1.5s, fetch:1.44s, build:53.8ms}, probe:{concurrency:5, total:8.88s, max:1.78s, probe:47.5ms, fetch:8.83s} | inner join, equal:[eq(tpch.supplier.s_suppkey, tpch.partsupp.ps_suppkey)] | 17.1 MB | 0 Bytes |
| ├─HashJoin_21(Build) | 43848.00 | 29581936.50 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00)) + (((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(5*1*tidb_cpu_factor(49.9))) + (hashmem(5*33*tidb_mem_factor(0.2))) + (hashbuild(5*tidb_cpu_factor(49.9)))) + (cpu(5*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(300000*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(300000*1*tidb_cpu_factor(49.9))) + (hashprobe(300000*tidb_cpu_factor(49.9)))))/5.00) | 60186 | root | | time:1.44s, loops:62, build_hash_table:{total:34.3ms, fetch:34.3ms, build:11.3µs}, probe:{concurrency:5, total:7.49s, max:1.5s, probe:218ms, fetch:7.27s} | inner join, equal:[eq(tpch.nation.n_nationkey, tpch.supplier.s_nationkey)] | 2.21 KB | 0 Bytes |
| │ ├─HashJoin_34(Build) | 5.00 | 3005.46 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(1*1*tidb_cpu_factor(49.9))) + (hashmem(1*28.84*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(25*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashprobe(25*tidb_cpu_factor(49.9)))))/5.00) | 5 | root | | time:34.3ms, loops:2, build_hash_table:{total:20.9ms, fetch:20.9ms, build:8.47µs}, probe:{concurrency:5, total:170.8ms, max:34.2ms, probe:43.5µs, fetch:170.7ms} | inner join, equal:[eq(tpch.region.r_regionkey, tpch.nation.n_regionkey)] | 25.7 KB | 0 Bytes |
| │ │ ├─TableReader_39(Build) | 1.00 | 119.34 | (((cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)))) + (net(1*rowsize(28.84)*tidb_kv_net_factor(3.96))))/15.00 | 1 | root | | time:20.9ms, loops:2, cop_task: {num: 1, max: 21.5ms, proc_keys: 5, rpc_num: 1, rpc_time: 21.4ms, copr_cache: disabled, distsql_concurrency: 15} | data:Selection_38 | 269 Bytes | N/A |
| │ │ │ └─Selection_38 | 1.00 | 1675.92 | (cpu(5*filters(1)*tikv_cpu_factor(49.9))) + (scan(5*logrowsize(128.84)*tikv_scan_factor(40.7))) | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 559, total_keys: 6, get_snapshot_time: 272.7µs, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 8}}} | eq(tpch.region.r_name, "ASIA") | N/A | N/A |
| │ │ │ └─TableFullScan_37 | 5.00 | 1426.42 | scan(5*logrowsize(128.84)*tikv_scan_factor(40.7)) | 5 | cop[tikv] | table:region | tikv_task:{time:0s, loops:1} | keep order:false | N/A | N/A |
| │ │ └─TableReader_36(Probe) | 25.00 | 784.55 | ((scan(25*logrowsize(145.07)*tikv_scan_factor(40.7))) + (net(25*rowsize(45.07)*tidb_kv_net_factor(3.96))))/15.00 | 25 | root | | time:34.1ms, loops:2, cop_task: {num: 1, max: 34.6ms, proc_keys: 25, rpc_num: 1, rpc_time: 34.6ms, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_35 | 1.01 KB | N/A |
| │ │ └─TableFullScan_35 | 25.00 | 7306.27 | scan(25*logrowsize(145.07)*tikv_scan_factor(40.7)) | 25 | cop[tikv] | table:nation | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 7.96ms, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 8}}} | keep order:false | N/A | N/A |
| │ └─TableReader_41(Probe) | 300000.00 | 23588902.04 | ((scan(300000*logrowsize(218)*tikv_scan_factor(40.7))) + (net(300000*rowsize(218)*tidb_kv_net_factor(3.96))))/15.00 | 300000 | root | | time:1.47s, loops:295, cop_task: {num: 14, max: 414.1ms, min: 14ms, avg: 106.9ms, p95: 414.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 863ms, tot_wait: 286ms, rpc_num: 14, rpc_time: 1.5s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_40 | 20.0 MB | N/A |
| │ └─TableFullScan_40 | 300000.00 | 94849530.61 | scan(300000*logrowsize(218)*tikv_scan_factor(40.7)) | 300000 | cop[tikv] | table:supplier | tikv_task:{proc max:305ms, min:0s, avg: 52.9ms, p80:66ms, p95:305ms, iters:348, tasks:14}, scan_detail: {total_process_keys: 300000, total_process_keys_size: 54436630, total_keys: 300014, get_snapshot_time: 30.5ms, rocksdb: {key_skipped_count: 300000, block: {cache_hit_count: 1006}}} | keep order:false | N/A | N/A |
| └─IndexJoin_47(Probe) | 396142.02 | 182507890.90 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)))) + (net(23821*rowsize(76.26)*tidb_kv_net_factor(3.96))))/15.00) + (cpu(23821*filters(0)*tidb_cpu_factor(49.9))) + (cpu(23821*10*tidb_cpu_factor(49.9))) + ((() + (((((scan(4*logrowsize(179)*tikv_scan_factor(40.7))) + (net(4*rowsize(56)*tidb_kv_net_factor(3.96))))/15.00)*23821.00)/6.00) + (cpu(95284*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(95284*0*tidb_cpu_factor(49.9))) + (hashmem(95284*56*tidb_mem_factor(0.2))) + (hashbuild(95284*tidb_cpu_factor(49.9)))))/5.00) | 95284 | root | | time:471.4ms, loops:95, inner:{total:2.75s, concurrency:5, task:9, construct:7.15ms, fetch:2.67s, build:64.6ms}, probe:13ms | inner join, inner:TableReader_44, outer key:tpch.part.p_partkey, inner key:tpch.partsupp.ps_partkey, equal cond:eq(tpch.part.p_partkey, tpch.partsupp.ps_partkey) | 6.88 MB | N/A |
| ├─TableReader_65(Build) | 99248.89 | 169343893.62 | (((cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)))) + (net(23821*rowsize(76.26)*tidb_kv_net_factor(3.96))))/15.00 | 23821 | root | | time:1.57s, loops:29, cop_task: {num: 55, max: 939.4ms, min: 33.3ms, avg: 263.3ms, p95: 730.3ms, max_proc_keys: 270304, p95_proc_keys: 263136, tot_proc: 11.7s, tot_wait: 1.43s, rpc_num: 55, rpc_time: 14.5s, copr_cache: disabled, distsql_concurrency: 15} | data:Selection_64 | 179.2 KB | N/A |
| │ └─Selection_64 | 99248.89 | 2532964710.07 | (cpu(6e+06*filters(2)*tikv_cpu_factor(49.9))) + (scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7))) | 23821 | cop[tikv] | | tikv_task:{proc max:880ms, min:26ms, avg: 209.8ms, p80:395ms, p95:621ms, iters:6090, tasks:55}, scan_detail: {total_process_keys: 6000000, total_process_keys_size: 980543025, total_keys: 6000055, get_snapshot_time: 174.3ms, rocksdb: {key_skipped_count: 6000000, block: {cache_hit_count: 519, read_count: 16157, read_byte: 308.5 MB, read_time: 127.6ms}}} | eq(tpch.part.p_size, 30), like(tpch.part.p_type, "%STEEL", 92) | N/A | N/A |
| │ └─TableFullScan_63 | 6000000.00 | 1934164710.07 | scan(6e+06*logrowsize(242.26)*tikv_scan_factor(40.7)) | 6000000 | cop[tikv] | table:part | tikv_task:{proc max:871ms, min:26ms, avg: 202ms, p80:374ms, p95:617ms, iters:6090, tasks:55} | keep order:false | N/A | N/A |
| └─TableReader_44(Probe) | 99248.89 | 140.36 | ((scan(4*logrowsize(179)*tikv_scan_factor(40.7))) + (net(4*rowsize(56)*tidb_kv_net_factor(3.96))))/15.00 | 95284 | root | | time:2.62s, loops:106, cop_task: {num: 261, max: 439.7ms, min: 764.1µs, avg: 79.4ms, p95: 198ms, max_proc_keys: 992, p95_proc_keys: 992, tot_proc: 8.84s, tot_wait: 6.77s, rpc_num: 261, rpc_time: 20.7s, copr_cache: disabled, distsql_concurrency: 15} | data:TableRangeScan_43 | N/A | N/A |
| └─TableRangeScan_43 | 99248.89 | 1218.37 | scan(4*logrowsize(179)*tikv_scan_factor(40.7)) | 95284 | cop[tikv] | table:partsupp | tikv_task:{proc max:310ms, min:0s, avg: 33.8ms, p80:56ms, p95:126ms, iters:925, tasks:261}, scan_detail: {total_process_keys: 95284, total_process_keys_size: 17671763, total_keys: 119266, get_snapshot_time: 894.6ms, rocksdb: {key_skipped_count: 95284, block: {cache_hit_count: 178726, read_count: 13389, read_byte: 222.7 MB, read_time: 176.3ms}}} | range: decided by [eq(tpch.partsupp.ps_partkey, tpch.part.p_partkey)], keep order:false | N/A | N/A |
+------------------------------------------+------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
18 rows in set, 3 warnings (1.79 sec)
HashJoin_19的成本为：222587753.83
相比未添加hint时的HashJoin_31部分的成本：1798583176.73，小了很多。也就是虽然走inl_join(part,partsupp)优化器认为成本更低，但是并没有走该执行计划！
经过优化后，tpch的Q2语句由原来的17秒变成1.7秒，提升了10倍。
因此能否对优化器进行增强来提升此类问题的性能：
1、是否应对此类场景做关联子查询解关联进一步增强，特定场景下添加window function来消除join？
2、ShuffleReceiver_96算子的成本评估都为0，貌似存在缺陷？
3、对于上述最后一个场景，即使优化器认为成本更低但是并没有走该执行路径，是否也需要进行优化？