# 操作指导 #### 前置条件 - 数据库运行正常。 - 反馈基数功能开启。 - 数据库查询解析正常进入计划优化阶段。 #### 使用指导 打开参数enable_adaptive_cost，其他参数使用默认设置即可开启本功能。 反馈基数功能需要先收集基数才能修正统计偏差，只有通过explain (analyze)执行或执行时间超过adaptive_cost_min_time的SQL才会收集基数信息。 当设置adaptive_cardest_strategy=auto（默认配置）时，同类型的SQL（按照sql id区分）第一次执行不会使用反馈基数。对重复执行的同类SQL，会按照过去的执行时间来判断是否可以使用反馈基数。如果出现了严重的计划劣化，即本次执行时间超过了不使用反馈基数平均执行时间的10倍再多5秒，则会将对应的SQL列入黑名单，不再使用反馈基数。例如，某sql不使用反馈基数的平均执行时间为1s，如果使用反馈基数后执行时间超过了1\*10+5=15秒，则会列入黑名单。 #### 示例 受环境、版本及部署形态差异影响，示例中的执行计划可能与实际不同。本示例仅作参考，具体优化效果请以实际执行计划为准。 - 通过后台线程自动进行模型训练，调整执行计划。 1. 启动数据库，查看enable_adaptive_cost参数是否开启，确保反馈基数估计功能的后台训练已正常开启。 ``` gaussdb=# SHOW enable_adaptive_cost; enable_adaptive_cost ---------------------- on (1 row) ``` 2. 设置计划展示格式。 ``` gaussdb=# SET explain_perf_mode = pretty; ``` 3. 执行由于错误的基数估计导致次优计划的语句，如含有多种连接路径和连接类型的查询SQL，观察执行计划在多次执行中是否迭代出更优计划。以JOB数据集为例，首先导入JOB数据集，并运行查询语句01b.sql。 ``` gaussdb=# EXPLAIN (ANALYZE) SELECT MIN(mc.note) AS production_note, MIN(t.title) AS movie_title, MIN(t.production_year) AS movie_year FROM company_type AS ct, info_type AS it, movie_companies AS mc, movie_info_idx AS mi_idx, title AS t WHERE ct.kind = 'production companies' AND it.info = 'bottom 10 rank' AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND t.production_year BETWEEN 2003 AND 2010 AND ct.id = mc.company_type_id AND t.id = mc.movie_id AND t.id = mi_idx.movie_id AND mc.movie_id = mi_idx.movie_id AND it.id = mi_idx.info_type_id; ``` 4. 观察执行计划可知，总执行时间为6763.152毫秒。重点关注A-rows（实际行数）与E-rows（估计行数）两列数据，可以发现当前计划生成过程中的基数估计存在显著偏差。例如，对于Hash Join(8, 11)算子，其实际行数仅为6行，而估计行数却高达407,582行。正是由于此类基数估计偏差，导致该执行计划并非当前最优选择。此时，因后台训练功能已开启，反馈基数估计模块的后台训练线程将被触发，自动执行模型训练任务。 ``` id | operation | A-time | A-rows | E-rows | Peak Memory | A-width | E-width | E-costs ----+--------------------------------------------------------------------------------------+----------+---------+---------+-------------+---------+---------+------------------------ 1 | -> Aggregate | 6763.152 | 1 | 1 | 37KB | | 113 | 310093.406..310093.416 2 | -> Hash Join (3,6) | 6763.081 | 4 | 519424 | 33KB | | 45 | 168520.603..306197.726 3 | -> Nested Loop (4,5) | 1331.081 | 140904 | 1304518 | 14KB | | 28 | 0.000..55534.270 4 | -> Seq Scan on company_type ct | 0.057 | 1 | 1 | 40KB | | 4 | 0.000..1.050 5 | -> Index Scan using company_type_id_movie_companies on movie_companies mc | 1277.665 | 140904 | 1304518 | 99KB | | 32 | 0.000..42488.040 6 | -> Hash | 5338.253 | 6 | 407582 | 2101KB | | 29 | 153872.828..153872.828 7 | -> Hash Join (8,11) | 5338.095 | 6 | 407582 | 25KB | | 29 | 4.452..153872.828 8 | -> Merge Join (9,10) | 5218.758 | 453508 | 1293307 | 22KB | | 33 | 2.027..147395.782 9 | -> Index Scan using title_pkey on title t | 3113.129 | 868454 | 867579 | 94KB | | 25 | 0.000..95529.890 10 | -> Index Scan using movie_id_movie_info_idx on movie_info_idx mi_idx | 1347.430 | 1380035 | 1380035 | 85KB | | 8 | 0.000..33401.173 11 | -> Hash | 0.245 | 1 | 1 | 299KB | | 4 | 2.413..2.413 12 | -> Seq Scan on info_type it | 0.167 | 1 | 1 | 43KB | | 4 | 0.000..2.413 (12 rows) ``` 5. 再次执行查询并观察执行计划，可见执行时间已优化（从6763.152毫秒降至5337.537毫秒）。分析基数估计情况，在反馈基数估计模型的预测下，多数算子的基数估计值得到了有效矫正，从而优化了执行计划的生成。然而，仍可观察到部分基数估计偏差存在。例如，Nested Loop (4, 10)算子的估计行数与实际行数之间仍存在显著误差。这是由于上一轮基数估计的矫正改变了计划选择，导致本次执行相同SQL时生成了包含新算子（即此Nested Loop）的不同执行计划。因此，在上一轮模型训练周期内，系统未能采集到该算子的相关数据用于训练。而在本轮执行中，该新算子的信息将被收集，并触发模型训练。待后续查询再次包含此算子时，系统即可加载对应的训练模型进行精准预测。 ``` gaussdb=# EXPLAIN (ANALYZE) SELECT MIN(mc.note) AS production_note, MIN(t.title) AS movie_title, MIN(t.production_year) AS movie_year FROM company_type AS ct, info_type AS it, movie_companies AS mc, movie_info_idx AS mi_idx, title AS t WHERE ct.kind = 'production companies' AND it.info = 'bottom 10 rank' AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND t.production_year BETWEEN 2003 AND 2010 AND ct.id = mc.company_type_id AND t.id = mc.movie_id AND t.id = mi_idx.movie_id AND mc.movie_id = mi_idx.movie_id AND it.id = mi_idx.info_type_id; id | operation | A-time | A-rows | E-rows | Peak Memory | A-width | E-width | E-costs ----+--------------------------------------------------------------------------------------+----------+---------+---------+-------------+---------+---------+------------------------ 1 | -> Aggregate | 5337.527 | 1 | 1 | 37KB | | 113 | 154787.966..154787.976 2 | -> Hash Join (3,11) | 5337.411 | 4 | 56104 | 25KB | | 45 | 5.514..154367.186 3 | -> Nested Loop (4,10) | 5336.835 | 78 | 645521 | 14KB | | 49 | 4.452..149520.686 4 | -> Hash Join (5,8) | 5335.656 | 6 | 6 | 25KB | | 29 | 4.452..149504.894 5 | -> Merge Join (6,7) | 5214.316 | 453508 | 420811 | 22KB | | 33 | 2.027..147395.782 6 | -> Index Scan using title_pkey on title t | 3136.195 | 868454 | 867579 | 94KB | | 25 | 0.000..95529.890 7 | -> Index Scan using movie_id_movie_info_idx on movie_info_idx mi_idx | 1315.340 | 1380035 | 1380035 | 85KB | | 8 | 0.000..33401.173 8 | -> Hash | 0.128 | 1 | 1 | 299KB | | 4 | 2.413..2.413 9 | -> Seq Scan on info_type it | 0.070 | 1 | 1 | 43KB | | 4 | 0.000..2.413 10 | -> Index Scan using movie_id_movie_companies on movie_companies mc | 0.882 | 78 | 2 | 99KB | | 32 | 0.000..2.612 11 | -> Hash | 0.095 | 1 | 1 | 299KB | | 4 | 1.050..1.050 12 | -> Seq Scan on company_type ct | 0.030 | 1 | 1 | 43KB | | 4 | 0.000..1.050 (12 rows) ``` 6. 再次执行查询并观察执行计划，可见上一轮基数估计未矫正的Nested Loop算子，其基数估计结果亦得到有效矫正**。** ``` gaussdb=# EXPLAIN (ANALYZE) SELECT MIN(mc.note) AS production_note, MIN(t.title) AS movie_title, MIN(t.production_year) AS movie_year FROM company_type AS ct, info_type AS it, movie_companies AS mc, movie_info_idx AS mi_idx, title AS t WHERE ct.kind = 'production companies' AND it.info = 'bottom 10 rank' AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND t.production_year BETWEEN 2003 AND 2010 AND ct.id = mc.company_type_id AND t.id = mc.movie_id AND t.id = mi_idx.movie_id AND mc.movie_id = mi_idx.movie_id AND it.id = mi_idx.info_type_id; id | operation | A-time | A-rows | E-rows | Peak Memory | A-width | E-width | E-costs ----+--------------------------------------------------------------------------------------+----------+---------+---------+-------------+---------+---------+------------------------ 1 | -> Aggregate | 5239.744 | 1 | 1 | 37KB | | 113 | 149522.816..149522.826 2 | -> Nested Loop (3,4) | 5239.688 | 4 | 4 | 18KB | | 45 | 4.452..149522.786 3 | -> Seq Scan on company_type ct | 0.031 | 1 | 1 | 40KB | | 4 | 0.000..1.050 4 | -> Nested Loop (5,11) | 5239.539 | 78 | 84 | 14KB | | 49 | 4.452..149520.686 5 | -> Hash Join (6,9) | 5238.656 | 6 | 6 | 25KB | | 29 | 4.452..149504.894 6 | -> Merge Join (7,8) | 5122.325 | 453508 | 420811 | 22KB | | 33 | 2.027..147395.782 7 | -> Index Scan using title_pkey on title t | 3063.495 | 868454 | 867579 | 21KB | | 25 | 0.000..95529.890 8 | -> Index Scan using movie_id_movie_info_idx on movie_info_idx mi_idx | 1295.687 | 1380035 | 1380035 | 85KB | | 8 | 0.000..33401.173 9 | -> Hash | 0.095 | 1 | 1 | 299KB | | 4 | 2.413..2.413 10 | -> Seq Scan on info_type it | 0.071 | 1 | 1 | 43KB | | 4 | 0.000..2.413 11 | -> Index Scan using movie_id_movie_companies on movie_companies mc | 0.775 | 78 | 2 | 99KB | | 32 | 0.000..2.612 ``` - 通过EXPLAIN ADAPTCOST选项展示模型使用情况，沿用上一节的执行计划。 1. 设置计划展示格式，展示模型使用情况仅支持normal模式。 ``` gaussdb=# SET explain_perf_mode = normal; ``` 2. 在EXPLAIN语句中加入ADAPTCOST选项，执行查询。 ``` gaussdb=# EXPLAIN (ANALYZE, ADAPTCOST) SELECT MIN(mc.note) AS production_note, MIN(t.title) AS movie_title, MIN(t.production_year) AS movie_year FROM company_type AS ct, info_type AS it, movie_companies AS mc, movie_info_idx AS mi_idx, title AS t WHERE ct.kind = 'production companies' AND it.info = 'bottom 10 rank' AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND t.production_year BETWEEN 2003 AND 2010 AND ct.id = mc.company_type_id AND t.id = mc.movie_id AND t.id = mi_idx.movie_id AND mc.movie_id = mi_idx.movie_id AND it.id = mi_idx.info_type_id; ``` 3. 观察执行计划输出，可见在算子详情中新增了cardest字段。当算子应用了反馈基数估计模型进行基数预测时，该字段会显示所加载的模型名称。如第二行的Nested Loop算子所示，其cardest字段值为UMM_S_3461496410,26633.5。这表明该算子使用了名为此标识符的UMM模型进行预测。模型名称即为该算子特征值（哈希值）**。** ``` QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------- Aggregate (cost=149522.82..149522.83 rows=1 cardest=default width=113) (actual time=5278.285..5278.286 rows=1 loops=1) -> Nested Loop (cost=4.45..149522.79 rows=4 cardest=trust UMM_S_3461496410,26633.5 width=45) (actual time=3421.652..5278.209 rows=4 loops=1) Join Filter: (mc.company_type_id = ct.id), (Expression Flatten Optimized) Rows Removed by Join Filter: 74 -> Seq Scan on company_type ct (cost=0.00..1.05 rows=1 cardest=try default width=4) (actual time=0.022..0.033 rows=1 loops=1) Filter: ((kind)::text = 'production companies'::text), (Expression Flatten Optimized) Rows Removed by Filter: 3 -> Nested Loop (cost=4.45..149520.69 rows=84 cardest=trust UMM_S_2994907757,26633.5(mixed) width=49) (actual time=3030.134..5278.040 rows=78 loops=1) -> Hash Join (cost=4.45..149504.89 rows=6 cardest=trust UMM_S_3093839448,26633.5(mixed) width=29) (actual time=3029.979..5277.112 rows=6 loops=1) Hash Cond: (mi_idx.info_type_id = it.id), (Expression Flatten Optimized) -> Merge Join (cost=2.03..147395.78 rows=420811 cardest=trust UMM_S_1518054377,26633.5(mixed) width=33) (actual time=0.091..5158.858 rows=453508 loops=1) Merge Cond: (t.id = mi_idx.movie_id) -> Index Scan using title_pkey on title t (cost=0.00..95529.89 rows=867579 cardest=try default width=25) (actual time=0.043..3081.927 rows=868454 loops=1) Filter: ((production_year >= 2003) AND (production_year <= 2010)), (Expression Flatten Optimized) Rows Removed by Filter: 1657356 -> Index Scan using movie_id_movie_info_idx on movie_info_idx mi_idx (cost=0.00..33401.17 rows=1380035 cardest=default width=8) (actual time=0.037..1309.014 rows=13 80035 loops=1) -> Hash (cost=2.41..2.41 rows=1 cardest=default width=4) (actual time=0.088..0.088 rows=1 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1kB -> Seq Scan on info_type it (cost=0.00..2.41 rows=1 cardest=try default width=4) (actual time=0.069..0.072 rows=1 loops=1) Filter: ((info)::text = 'bottom 10 rank'::text), (Expression Flatten Optimized) Rows Removed by Filter: 112 -> Index Scan using movie_id_movie_companies on movie_companies mc (cost=0.00..2.61 rows=2 cardest=try default width=32) (actual time=0.599..0.836 rows=78 loops=6) Index Cond: (movie_id = t.id) Filter: (note !~~ '%(as Metro-Goldwyn-Mayer Pictures)%'::text) Rows Removed by Filter: 15 Total runtime: 5279.630 ms ```