Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 建议官方加入一个新功能。智能索引推荐。
|
username: tidb狂热爱好者
[SQL Tuning Guide—Intelligent Index Recommendation-
Run Index Recommendation
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);
Returns recommendation information and recommendation effectiveness. Does TiDB have a corresponding intelligent recommendation feature like PostgreSQL?
IMPROVE_VALUE: 2465.3% # Estimated overall cost improvement percentage
IMPROVE_CPU: 59377.4% # Estimated CPU improvement percentage
IMPROVE_MEM: 0.4% # Estimated memory improvement percentage
IMPROVE_IO: 3024.7% # Estimated disk I/O improvement percentage
IMPROVE_NET: 2011.1% # Estimated network transmission improvement percentage
BEFORE_VALUE: 4.711359845E8 # Overall cost value before adding the index
BEFORE_CPU: 1.19405577E7 # Estimated CPU value before adding the index
BEFORE_MEM: 426811.2 # Estimated memory consumption before adding the index
BEFORE_IO: 44339 # Estimated disk I/O value before adding the index
BEFORE_NET: 47.5 # Estimated network transmission value before adding the index
AFTER_VALUE: 1.83655008E7 # Overall cost value after adding the index
AFTER_CPU: 20075.8 # Estimated CPU value after adding the index
AFTER_MEM: 425016 # Estimated memory consumption after adding the index
AFTER_IO: 1419 # Estimated disk I/O value after adding the index
AFTER_NET: 2.2 # Estimated network transmission value after adding the index
ADVISE_INDEX: ALTER TABLE lineitem ADD INDEX __advise_index_lineiteml_partkey(l_partkey);
/ The content in ADVISE_INDEX is the recommended index creation statement /
NEW_PLAN: # Expected execution plan after adding the index
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 # Other information
|
username: 
|
username: 
|
username: 