GaussDB(DWS) 性能调优：indexscan 导致的性能问题识别与优化

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_FLAGFROM (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_Uwhere 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";