[SQL调优指南—智能索引推荐-
运行索引推荐
EXPLAIN ADVISOR
SELECT sum(l_extendedprice) / 7.0 AS avg_yearly
FROM lineitem,
part
WHERE p_partkey = l_partkey
AND p_brand = ‘Brand#23’
AND p_container = ‘MED BOX’
AND l_quantity <
(SELECT 0.2 * avg(l_quantity)
FROM lineitem
WHERE l_partkey = p_partkey);
返回推荐信息 和推荐效能。tidb里面是否有postgressql 对应的智能推荐功能？
IMPROVE_VALUE: 2465.3% # 预计综合代价提升百分比
IMPROVE_CPU: 59377.4% # 预计CPU提升百分比
IMPROVE_MEM: 0.4% # 预计内存提升百分比
IMPROVE_IO: 3024.7% # 预计磁盘I/O提升百分比
IMPROVE_NET: 2011.1% # 预计网络传输提升百分比
BEFORE_VALUE: 4.711359845E8 # 添加索引前综合代价值
BEFORE_CPU: 1.19405577E7 # 添加索引前CPU估算值
BEFORE_MEM: 426811.2 # 添加索引前内存消耗估算值
BEFORE_IO: 44339 # 添加索引前磁盘I/O估算值
BEFORE_NET: 47.5 # 添加索引前网络传输估算值
AFTER_VALUE: 1.83655008E7 # 添加索引后综合代价值
AFTER_CPU: 20075.8 # 添加索引后CPU估算值
AFTER_MEM: 425016 # 添加索引后内存消耗估算值
AFTER_IO: 1419 # 添加索引后磁盘I/O估算值
AFTER_NET: 2.2 # 添加索引后网络传输估算值
ADVISE_INDEX: ALTER TABLE lineitem ADD INDEX __advise_index_lineiteml_partkey(l_partkey);
/ ADVISE_INDEX中的内容为推荐的索引创建语句 /
NEW_PLAN: # 添加索引后预计执行计划
Project(avg_yearly="$f0 / ?0")
HashAgg($f0=“SUM(l_extendedprice)”)
Filter(condition=“l_quantity < $16 * f17w0$o0”)
SortWindow(p_partkey=“p_partkey”, l_partkey=“l_partkey”, l_quantity=“l_quantity”, l_extendedprice=“l_extendedprice”, $16="$16", f5w0$o0=“window#0AVG($2)”, Reference Windows=“window#0=window(partition {1} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED PRECEDING aggs [AVG($2)])”)
MemSort(sort=“l_partkey ASC”)
BKAJoin(condition=“l_partkey = p_partkey”, type=“inner”)
Gather(concurrent=true)
LogicalView(tables="[0000,0001].part", shardCount=2, sql=“SELECT p_partkey FROM part AS part WHERE ((p_brand = ?) AND (p_container = ?))”)
Gather(concurrent=true)
LogicalView(tables="[0000,0001].lineitem", shardCount=2, sql=“SELECT l_partkey, l_quantity, l_extendedprice, ? AS $16 FROM lineitem AS lineitem WHERE (l_partkey IN (…))”)
INFO: LOCAL_INDEX # 其它信息
