# 操作指导 #### 前置条件 - 数据库运行正常。 - 反馈基数功能开启。 - 数据库查询解析正常进入计划优化阶段。 #### 使用指导 打开参数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. 观察执行计划可知，总执行时间为1442.612毫秒。重点关注A-rows（实际行数）与E-rows（估计行数）两列数据，可以发现当前计划生成过程中的基数估计存在显著偏差。例如，对于Hash Join(5, 11)算子，其实际行数仅为4行，而估计行数却高达528329行。正是由于此类基数估计偏差，导致该执行计划并非当前最优选择。此时，因后台训练功能已开启，反馈基数估计模块的后台训练线程将被触发，自动执行模型训练任务。 ``` id | operation | A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs ----+---------------------------------------------------------------------------------------+---------------------+---------+---------+---------------+----------+---------+---------+----------- 1 | -> Aggregate | 1442.612 | 1 | 1 | 29KB | | | 113 | 107165.90 2 | -> Streaming (type: GATHER) | 1442.467 | 3 | 3 | 150KB | | | 113 | 107165.90 3 | -> Aggregate | [1382.897,1422.194] | 3 | 3 | [27KB,28KB] | 1MB | | 113 | 107165.78 4 | -> Hash Join (5,11) | [1382.845,1422.167] | 4 | 528329 | [10KB,10KB] | 1MB | | 45 | 105844.94 5 | -> Streaming(type: REDISTRIBUTE) | [435.768,481.418] | 140904 | 1303008 | [69KB,69KB] | 2MB | | 28 | 55550.75 6 | -> Nested Loop (7,9) | [495.739,559.512] | 140904 | 1303008 | [4KB,4KB] | 1MB | | 28 | 19718.03 7 | -> Streaming(type: BROADCAST) | [0.063,33.318] | 3 | 3 | [45KB,45KB] | 2MB | | 4 | 2.30 8 | -> Seq Scan on company_type ct | [0.003,0.074] | 1 | 1 | [32KB,32KB] | 1MB | | 4 | 2.02 9 | -> Bitmap Heap Scan on movie_companies mc | [475.771,539.464] | 140904 | 1303008 | [74KB,76KB] | 1MB | | 32 | 15372.37 10 | -> Bitmap Index Scan using company_type_id_movie_companies | [120.751,130.875] | 1334883 | 1304564 | [356KB,356KB] | 1MB | | 0 | 3495.96 11 | -> Hash | [833.688,891.113] | 6 | 407937 | [2MB,2MB] | 16MB | [48,52] | 29 | 44769.35 12 | -> Hash Join (13,14) | [833.646,891.058] | 6 | 407937 | [11KB,11KB] | 1MB | | 29 | 44769.35 13 | -> Seq Scan on title t | [704.619,725.190] | 868629 | 872254 | [52KB,52KB] | 1MB | | 25 | 24237.56 14 | -> Hash | [13.300,84.206] | 10 | 345009 | [1MB,1MB] | 16MB | [20,20] | 4 | 15190.38 15 | -> Streaming(type: REDISTRIBUTE) | [13.221,84.122] | 10 | 345009 | [45KB,45KB] | 2MB | | 4 | 15190.38 16 | -> Nested Loop (17,19) | [10.662,46.745] | 10 | 345009 | [3KB,3KB] | 1MB | | 4 | 5702.64 17 | -> Streaming(type: BROADCAST) | [10.177,46.329] | 3 | 3 | [45KB,45KB] | 2MB | | 4 | 1.75 18 | -> Seq Scan on info_type it | [0.271,0.349] | 1 | 1 | [32KB,32KB] | 1MB | | 4 | 1.48 19 | -> Bitmap Heap Scan on movie_info_idx mi_idx | [0.210,0.376] | 10 | 345009 | [23KB,24KB] | 1MB | | 8 | 4550.86 20 | -> Bitmap Index Scan using info_type_id_movie_info_idx | [0.160,0.271] | 10 | 345009 | [3KB,3KB] | 1MB | | 0 | 926.57 (20 rows) ``` 5. 再次执行查询并观察执行计划，可见执行时间已优化（从6763.152毫秒降至5337.537毫秒）。分析基数估计情况，在反馈基数估计模型的预测下，多数算子的基数估计值得到了有效矫正，从而优化了执行计划的生成。 ``` 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 | E-memory | A-width | E-width | E-costs ----+------------------------------------------------------------------------------------------+-----------------+--------+--------+-------------+----------+---------+---------+--------- 1 | -> Aggregate | 72.986 | 1 | 1 | 29KB | | | 113 | 5858.87 2 | -> Streaming (type: GATHER) | 72.860 | 3 | 3 | 150KB | | | 113 | 5858.87 3 | -> Aggregate | [55.949,56.995] | 3 | 3 | [26KB,27KB] | 1MB | | 113 | 5858.74 4 | -> Nested Loop (5,7) | [55.815,56.920] | 4 | 57132 | [8KB,8KB] | 1MB | | 45 | 5715.90 5 | -> Streaming(type: BROADCAST) | [0.099,0.197] | 3 | 3 | [45KB,45KB] | 2MB | | 4 | 2.30 6 | -> Seq Scan on company_type ct | [0.001,0.038] | 1 | 1 | [32KB,32KB] | 1MB | | 4 | 2.02 7 | -> Materialize | [55.498,56.637] | 81 | 19 | [14KB,18KB] | 16MB | | 49 | 5713.55 8 | -> Nested Loop (9,18) | [55.427,56.565] | 78 | 19 | [9KB,9KB] | 1MB | | 49 | 5713.53 9 | -> Streaming(type: BROADCAST) | [53.757,54.850] | 18 | 36 | [46KB,46KB] | 2MB | | 29 | 5705.90 10 | -> Nested Loop (11,17) | [38.080,44.807] | 6 | 12 | [6KB,6KB] | 1MB | | 29 | 5704.81 11 | -> Streaming(type: REDISTRIBUTE) | [37.643,43.903] | 10 | 10 | [45KB,45KB] | 2MB | | 4 | 5702.88 12 | -> Nested Loop (13,15) | [0.237,37.279] | 10 | 10 | [3KB,3KB] | 1MB | | 4 | 5702.64 13 | -> Streaming(type: BROADCAST) | [0.074,37.080] | 3 | 3 | [45KB,45KB] | 2MB | | 4 | 1.75 14 | -> Seq Scan on info_type it | [0.080,0.153] | 1 | 1 | [32KB,32KB] | 1MB | | 4 | 1.48 15 | -> Bitmap Heap Scan on movie_info_idx mi_idx | [0.113,0.138] | 10 | 345009 | [23KB,24KB] | 1MB | | 8 | 4550.86 16 | -> Bitmap Index Scan using info_type_id_movie_info_idx | [0.039,0.071] | 10 | 345009 | [3KB,3KB] | 1MB | | 0 | 926.57 17 | -> Index Scan using title_pkey on title t | [0.310,0.761] | 6 | 1 | [26KB,26KB] | 1MB | | 25 | 0.63 18 | -> Index Scan using movie_id_movie_companies on movie_companies mc | [0.791,1.698] | 78 | 2 | [16KB,16KB] | 1MB | | 32 | 0.62 (18 rows) ``` - 通过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_3988043888,17038.5。这表明该算子使用了名为此标识符的UMM模型进行预测。模型名称即为该算子特征值（哈希值）**。** ``` QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- Aggregate (cost=5711.89..5711.96 rows=1 cardest=default width=113) (actual time=98.249..98.250 rows=1 loops=1) -> Streaming (type: GATHER) (cost=5711.89..5711.96 rows=3 cardest=default width=113) (actual time=96.016..98.148 rows=3 loops=1) Node/s: All datanodes -> Aggregate (cost=5711.83..5711.84 rows=3 cardest=default width=113) (actual time=[49.865,49.866]..[88.874,88.874], rows=3) -> Nested Loop (cost=955.32..5711.82 rows=4 cardest=trust UMM_S_3988043888,17038.5 width=45) (actual time=[49.777,49.812]..[88.842,88.842], rows=4) Join Filter: (mc.company_type_id = ct.id), (Expression Flatten Optimized) Rows Removed by Join Filter: 74 -> Streaming(type: BROADCAST) (cost=0.00..2.30 rows=3 cardest=default width=4) (actual time=[0.070,0.075]..[0.099,0.105], rows=3) Spawn on: All datanodes -> Seq Scan on company_type ct (cost=0.00..2.02 rows=1 cardest=try default width=4) (actual time=[0.002,0.002]..[0.050,0.052], rows=1) Filter: ((kind)::text = 'production companies'::text), (Expression Flatten Optimized) Rows Removed by Filter: 3 -> Materialize (cost=955.32..5709.24 rows=84 cardest=default width=49) (actual time=[49.590,49.594]..[88.547,88.555], rows=81) -> Nested Loop (cost=955.32..5709.17 rows=84 cardest=trust UMM_S_229582469,17038.5(mixed) width=49) (actual time=[49.097,49.510]..[87.876,88.476], rows=78) Join Filter: (t.id = mc.movie_id), (Expression Flatten Optimized) -> Streaming(type: BROADCAST) (cost=955.32..5705.36 rows=18 cardest=default width=29) (actual time=[48.944,48.971]..[87.581,87.619], rows=18) Spawn on: All datanodes -> Nested Loop (cost=955.32..5704.81 rows=6 cardest=trust UMM_S_550930755,17038.5(mixed) width=29) (actual time=[0.227,30.510]..[77.161,77.197], rows=6) -> Streaming(type: REDISTRIBUTE) (cost=955.32..5702.88 rows=10 cardest=default width=4) (actual time=[0.073,30.357]..[76.971,76.982], rows=10) Spawn on: All datanodes -> Nested Loop (cost=955.32..5702.64 rows=10 cardest=trust UMM_F_1223820966(mixed) width=4) (actual time=[0.188,0.985]..[34.022,73.538], row s=10) -> Streaming(type: BROADCAST) (cost=0.00..1.75 rows=3 cardest=default width=4) (actual time=[0.074,0.867]..[33.869,73.378], rows=3) Spawn on: All datanodes -> Seq Scan on info_type it (cost=0.00..1.48 rows=1 cardest=try default width=4) (actual time=[0.062,0.064]..[0.077,0.077], rows =1) Filter: ((info)::text = 'bottom 10 rank'::text), (Expression Flatten Optimized) Rows Removed by Filter: 112 -> Bitmap Heap Scan on movie_info_idx mi_idx (cost=955.32..4550.86 rows=345009 cardest=default width=8) (actual time=[0.067,0.068]..[0 .093,0.097], rows=10) Recheck Cond: (info_type_id = it.id), (Expression Flatten Optimized) -> Bitmap Index Scan on info_type_id_movie_info_idx (cost=0.00..926.57 rows=345009 cardest=default width=0) (actual time=[0.034, 0.034]..[0.047,0.047], rows=10) Index Cond: (info_type_id = it.id) -> Index Scan using title_pkey on title t (cost=0.00..0.63 rows=1 cardest=try default width=25) (actual time=[0.107,0.107]..[0.215,0.215], rows=6) Index Cond: (id = mi_idx.movie_id) Filter: ((production_year >= 2003) AND (production_year <= 2010)) Rows Removed by Filter: 4 -> Index Scan using movie_id_movie_companies on movie_companies mc (cost=0.00..0.62 rows=2 cardest=try default width=32) (actual time=[0.248,0.402]..[0.336,0. 632], rows=78) Index Cond: (movie_id = mi_idx.movie_id) Filter: (note !~~ '%(as Metro-Goldwyn-Mayer Pictures)%'::text) Rows Removed by Filter: 15 Total runtime: 100.224 ms (39 rows) ```