GaussDB（DWS）性能调优indexscan导致的性能问题识别与优化

　　本文分享自华为云社区《GaussDB(DWS)性能调优：indexscan导致的性能问题识别与优化 #【玩转PB级数仓GaussDB(DWS)】-云社区-华为云》，作者： 譡里个檔 。
　　通常跑批加工场景下，都是大数量做关联操作，通常不建议使用索引。有些时候因为计划误判导致使用索引的可能会导致严重的性能问题。本文从一个典型的索引导致性能的场景重发，剖析此类问题的特征，定位方法和解决方法
　　1) 在某局点POC测试时发现某SQL语句比较慢，原始SQL如下WITH  /** etl_116583_7960703_994644 **/ LOADABLE as (select ＂boq_rel_type_id＂,＂to_pu_id＂,＂to_version＂,＂cycle_id＂,  ＂part_offset_flag＂,＂to_boq_id＂,＂descr＂,＂from_contract_id＂,  ＂from_version＂,＂from_pu_id＂,＂ss_id＂,＂to_contract_id＂,  ＂from_boq_id＂,＂enable_flag＂,＂last_update_date＂   from (SELECT /*+ PARALLEL(4)*/     BOQ_REL.FROM_BOQ_ID,     BOQ_REL.TO_BOQ_ID,     BOQ_REL.FROM_PU_ID,     BOQ_REL.TO_PU_ID,     BOQ_REL.PART_OFFSET_FLAG,     BOQ_REL.DESCR,     BOQ_REL.SS_ID,     BOQ_REL.CYCLE_ID,     NVL(BOQ_REL.FROM_VERSION, ＂SNULL＂) FROM_VERSION,     NVL(BOQ_REL.TO_VERSION, ＂SNULL＂) TO_VERSION,     BOQ_REL.LAST_UPDATE_DATE,     FROM_CON.CONTRACT_ID AS FROM_CONTRACT_ID,     TO_CON.CONTRACT_ID AS TO_CONTRACT_ID,     CLA.CLASS_ID AS BOQ_REL_TYPE_ID,     BOQ_REL.ENABLE_FLAG FROM (SELECT A.FROM_BOQ_ID,                    A.TO_BOQ_ID,                    A.FROM_PU_ID,                    A.TO_PU_ID,                    A.FROM_CONTRACT_NUMBER,                    A.TO_CONTRACT_NUMBER,                    A.BOQ_REL_TYPE_CODE,                    A.PART_OFFSET_FLAG,                    A.DESCR,                    A.SS_ID,                    A.FROM_VERSION,                    A.TO_VERSION,                    A.LAST_UPDATE_DATE,                    A.CYCLE_ID,                    A.ENABLE_FLAG,                    DECODE(A.SS_ID, 2820, 2600, A.SS_ID) SS_ID_TMP,                    ROW_NUMBER() OVER(PARTITION BY FROM_BOQ_ID, TO_BOQ_ID, FROM_PU_ID, TO_PU_ID, FROM_CONTRACT_NUMBER, TO_CONTRACT_NUMBER, BOQ_REL_TYPE_CODE, FROM_VERSION, TO_VERSION                                      ORDER BY DECODE(A.SS_ID, 2820, 1, 2600, 2, 3)) RN               FROM LDB_MD_BOQ_REL A) BOQ_REL,            (SELECT CONTRACT_ID,                    HW_CONTRACT_NUM,                    SS_ID               FROM DWI_MD_CONTRACT              WHERE CONTRACT_ID IS NOT NULL                AND END_TIME = TO_DATE(＂4712-12-31＂, ＂YYYY-MM-DD＂)) FROM_CON,            (SELECT CONTRACT_ID,                    HW_CONTRACT_NUM,                    SS_ID               FROM DWI_MD_CONTRACT              WHERE CONTRACT_ID IS NOT NULL                AND END_TIME = TO_DATE(＂4712-12-31＂, ＂YYYY-MM-DD＂)) TO_CON,            (SELECT CLASS_ID,                    CODE,                    CLASS_TYPE_ID,                    SS_ID               FROM DWI_MD_CLASS              WHERE CLASS_TYPE_ID = 193) CLA      WHERE BOQ_REL.RN = 1        AND BOQ_REL.FROM_CONTRACT_NUMBER = FROM_CON.HW_CONTRACT_NUM        AND BOQ_REL.SS_ID = FROM_CON.SS_ID        AND BOQ_REL.TO_CONTRACT_NUMBER = TO_CON.HW_CONTRACT_NUM        AND BOQ_REL.SS_ID = TO_CON.SS_ID        AND BOQ_REL.BOQ_REL_TYPE_CODE = CLA.CODE        AND BOQ_REL.SS_ID_TMP = CLA.SS_ID     ) t ), BEFORE_TARGET as (select ＂from_contract_id＂,＂from_pu_id＂,＂ss_id＂,＂from_boq_id＂,＂from_version＂,＂to_version＂,         ＂crt_cycle_id＂,＂to_pu_id＂,＂to_boq_id＂,＂del_flag＂,＂last_upd_cycle_id＂,＂last_update_date＂,         ＂descr＂,＂enable_flag＂,＂crt_job_instance_id＂,＂dq_improve_flag＂,＂upd_job_instance_id＂,         ＂to_contract_id＂,＂part_offset_flag＂,＂boq_rel_type_id＂      from (SELECT /*+PARALLEL(4)*/      FROM_BOQ_ID,      TO_BOQ_ID,      FROM_PU_ID,      TO_PU_ID,      FROM_CONTRACT_ID,      TO_CONTRACT_ID,      BOQ_REL_TYPE_ID,      PART_OFFSET_FLAG,      DESCR,      SS_ID,      CRT_CYCLE_ID,      LAST_UPD_CYCLE_ID,      DEL_FLAG,      DQ_IMPROVE_FLAG,      CRT_JOB_INSTANCE_ID,      UPD_JOB_INSTANCE_ID,      NVL(FROM_VERSION, ＂SNULL＂) FROM_VERSION,      NVL(TO_VERSION, ＂SNULL＂) TO_VERSION,      LAST_UPDATE_DATE,      ENABLE_FLAG       FROM DWI_MD_BOQ_REL     ) t ), CDC as (select LOADABLE.＂ss_id＂,LOADABLE.＂from_version＂,LOADABLE.＂from_boq_id＂,     LOADABLE.＂part_offset_flag＂,LOADABLE.＂from_pu_id＂,     case when BEFORE_TARGET.BOQ_REL_TYPE_ID is null and BEFORE_TARGET.FROM_BOQ_ID is null           and BEFORE_TARGET.FROM_CONTRACT_ID is null and BEFORE_TARGET.FROM_PU_ID is null           and BEFORE_TARGET.FROM_VERSION is null           and BEFORE_TARGET.TO_BOQ_ID is null and BEFORE_TARGET.TO_CONTRACT_ID is null           and BEFORE_TARGET.TO_PU_ID is null and BEFORE_TARGET.TO_VERSION is null          then 1           else 3      end as ＂change_code＂,     LOADABLE.＂to_version＂,LOADABLE.＂boq_rel_type_id＂,     LOADABLE.＂from_contract_id＂,LOADABLE.＂to_contract_id＂,     LOADABLE.＂descr＂,LOADABLE.＂last_update_date＂,     LOADABLE.＂to_pu_id＂,LOADABLE.＂enable_flag＂,LOADABLE.＂cycle_id＂,     LOADABLE.＂to_boq_id＂  from LOADABLE   left join BEFORE_TARGET on LOADABLE.BOQ_REL_TYPE_ID = BEFORE_TARGET.BOQ_REL_TYPE_ID      and LOADABLE.FROM_BOQ_ID = BEFORE_TARGET.FROM_BOQ_ID and LOADABLE.FROM_CONTRACT_ID = BEFORE_TARGET.FROM_CONTRACT_ID      and LOADABLE.FROM_PU_ID = BEFORE_TARGET.FROM_PU_ID and LOADABLE.FROM_VERSION = BEFORE_TARGET.FROM_VERSION      and LOADABLE.TO_BOQ_ID = BEFORE_TARGET.TO_BOQ_ID and LOADABLE.TO_CONTRACT_ID = BEFORE_TARGET.TO_CONTRACT_ID      and LOADABLE.TO_PU_ID = BEFORE_TARGET.TO_PU_ID and LOADABLE.TO_VERSION = BEFORE_TARGET.TO_VERSION ), TFM_FILTER_DATA_TARGET_OUTPUT_U as (select CDC.＂to_pu_id＂,CDC.＂boq_rel_type_id＂,CDC.＂ss_id＂,     Current_Timestamp() as ＂dw_last_update_date＂,CDC.＂to_version＂,     CDC.＂from_version＂,20230104000000 as ＂last_upd_cycle_id＂,     CDC.＂from_contract_id＂,CDC.＂last_update_date＂,CDC.＂descr＂,     ＂N＂ as ＂del_flag＂,CDC.＂from_boq_id＂,CDC.＂to_boq_id＂,     CDC.＂enable_flag＂,CDC.＂from_pu_id＂,-1 as ＂upd_job_instance_id＂,     ＂N＂ as ＂dq_improve_flag＂,CDC.＂to_contract_id＂,     CDC.＂part_offset_flag＂  from CDC where CDC.change_code=3 ) update DWI_MD_BOQ_REL TARGET_U  set ＂dq_improve_flag＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂dq_improve_flag＂,     ＂dw_last_update_date＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂dw_last_update_date＂,     ＂upd_job_instance_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂upd_job_instance_id＂,     ＂descr＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂descr＂,     ＂part_offset_flag＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂part_offset_flag＂,     ＂last_update_date＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂last_update_date＂,     ＂del_flag＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂del_flag＂,     ＂last_upd_cycle_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂last_upd_cycle_id＂,     ＂enable_flag＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂enable_flag＂,     ＂ss_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂ss_id＂  from TFM_FILTER_DATA_TARGET_OUTPUT_U where TARGET_U.＂boq_rel_type_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂boq_rel_type_id＂      and TARGET_U.＂to_version＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂to_version＂     and TARGET_U.＂to_version＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂to_version＂     and TARGET_U.＂to_pu_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂to_pu_id＂     and TARGET_U.＂to_pu_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂to_pu_id＂     and TARGET_U.＂to_contract_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂to_contract_id＂     and TARGET_U.＂to_contract_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂to_contract_id＂     and TARGET_U.＂to_boq_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂to_boq_id＂     and TARGET_U.＂to_boq_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂to_boq_id＂     and TARGET_U.＂from_version＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂from_version＂     and TARGET_U.＂from_version＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂from_version＂     and TARGET_U.＂from_pu_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂from_pu_id＂     and TARGET_U.＂from_pu_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂from_pu_id＂     and TARGET_U.＂from_contract_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂from_contract_id＂     and TARGET_U.＂from_contract_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂from_contract_id＂     and TARGET_U.＂from_boq_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂from_boq_id＂     and TARGET_U.＂from_boq_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂from_boq_id＂     and TARGET_U.＂boq_rel_type_id＂ = TFM_FILTER_DATA_TARGET_OUTPUT_U.＂boq_rel_type_id＂ ;
　　2) 查询此query的topSQL信息的warning字段，发现SQL自诊断信息中有索引相关告警信息。
　　3) 查询此query的topSQL信息(如下图)，分析历史执行信息，发现id=20的CStore Index Scan算子的耗时为90796.980ms，SQL执行总时长137135.658ms。CStore Index Scan算子的耗时占比为 66%
　　4) 找到原始SQL语句，对查询语句中出现的表dwimd.dwi_md_contract 进行hint，强制其走顺序扫描，避免走indexscan（全量语句见附件）
　　5）对语句进行explain verbose，查看计划，发现计划符合预期（即表dwimd.dwi_md_contract走tablescan，对于列存表计划上显式为CStore Scan）
　　6）对语句执行EXPLAIN ANALYZE操作(即实际执行语句)，查看实际执行时间如下，发现SQL语句性能提升近10倍。全量的执行信息见附件
　　附件：hint后的explain analyze 华为云博客_大数据博客_AI博客_云计算博客_开发者博客_技术博客-华为云附件：hint后的query 鍗庝负浜戝崥瀹 澶ф暟鎹 崥瀹 AI鍗氬 _浜戣 绠楀崥瀹 寮 鍙戣 呭崥瀹 鎶 鏈 崥瀹 -鍗庝负浜
　　点击下方，第一时间了解华为云新鲜技术~
　　华为云博客_大数据博客_AI博客_云计算博客_开发者中心-华为云
　　#华为云开发者联盟#