GaussDB(DWS)性能调优：NVL函数导致估算行数不准优化案例

1、【问题描述】

DWS存在NVL函数导致优化器估算不准的问题（该问题8.3.0已修改，8.2.1.2XX版本可以用表达式统计信息收集去规避），该问题会导致执行计划跳变，对SQL性能存在很大的影响。

2、【原始SQL】

SELECT XAO.AR_SUB_MODULE_SOURCE_TABLE_ID,
         XAO.AR_OTHER_DISTR_LINE_ID,
         XAO.AR_SUB_MODULE_SRC_TABLE_TYPE,
         ROW_NUMBER() OVER(PARTITION BY XAO.AR_SUB_MODULE_SOURCE_TABLE_ID ORDER BY XAO.AR_OTHER_DISTR_LINE_ID) RN
    FROM DWIFIN.DWI_XLA_AR_OTHER_DISTR_LINE XAO
   WHERE 1 = 1
     AND XAO.AR_SUB_MODULE_SRC_TABLE_TYPE IN ('REC', 'APPLIED')
		 AND NVL(XAO.DELETE_FLAG,'N') = 'N' /*202403版本WAVE4-ERP解耦*/
     -- AND XAO.REF_ACCOUNT_CLASS = 'REV'

      (SELECT /*+USE_HASH(CLA2 LEDE2 MER2 A)*/
        DISTINCT A.AR_INVOICE_ID,
                 LEDE2.LC_DR_AMT - LEDE2.LC_CR_AMT LOCAL_DISTRIBUTION_LINE_AMT,
                 LEDE2.TC_DR_AMT - LEDE2.TC_CR_AMT DISTRIBUTION_LINE_AMT
          FROM DWIFIN.DWI_XLA_AR_INVOICE_DISTR_LINE A,
               DWIFIN.DWI_XLA_SUB_MODULE_ENTRY_REL  MER2,
               DWIFIN.DWI_XLA_SUB_LEDGER_ENTRY      LEDE2,
               DWIMD_DW1.DWI_MD_CLASS                   CLA2
         WHERE A.AR_INVOICE_DISTR_LINE_ID = MER2.SUB_MODULE_ENTRY_ID
           AND MER2.SUB_LEDGER_ID = LEDE2.SUB_LEDGER_ID
           AND MER2.SUB_LEDGER_ENTRY_ID = LEDE2.SUB_LEDGER_ENTRY_ID
           AND LEDE2.ACCOUNT_CLASS_ID = CLA2.CLASS_ID
              --AND CLA1.CODE IN ('REV', 'UNEARN')
           AND MER2.SOURCE_APPLICATION_ID IN (222, 866310008)
           AND LEDE2.SOURCE_APPLICATION_ID IN (222, 866310008)
           AND A.SET_OF_BOOKS_ID = '10013783'
           AND CLA2.CODE IN ('RECEIVABLE')
           AND A.DEL_FLAG = 'N'
           AND A.DEL_FLAG = 'N'
					 AND NVL(A.DELETE_FLAG,'N') = 'N' /*202403版本WAVE4-ERP解耦*/) TT

   AND ARA.DEL_FLAG = 'N'
   AND T2.DEL_FLAG = 'N'
   AND T3.DEL_FLAG = 'N'
	 AND NVL(ARA.DELETE_FLAG,'N') = 'N' /*202403版本WAVE4-ERP解耦*/
	 AND NVL(T3.DELETE_FLAG,'N') = 'N' /*202403版本WAVE4-ERP解耦*/

3、【性能分析】

该SQL由于估算行数不准，导致计划易出现跳变，影响SQL执行性能，其verbose计划如下图所示，存在多个表估算行数为1，查看其过滤条件发现是NVL函数引起的。


针对该函数导致估算不准的问题，可以将NVL函数改写为：

NVL(XXX,'YYY') = 'YYY'   ->   XXX is null and XXX = 'YYY'

如下所示，将该案例中的SQL进行改写

SELECT XAO.AR_SUB_MODULE_SOURCE_TABLE_ID,
         XAO.AR_OTHER_DISTR_LINE_ID,
         XAO.AR_SUB_MODULE_SRC_TABLE_TYPE,
         ROW_NUMBER() OVER(PARTITION BY XAO.AR_SUB_MODULE_SOURCE_TABLE_ID ORDER BY XAO.AR_OTHER_DISTR_LINE_ID) RN
    FROM DWIFIN.DWI_XLA_AR_OTHER_DISTR_LINE XAO
   WHERE 1 = 1
     AND XAO.AR_SUB_MODULE_SRC_TABLE_TYPE IN ('REC', 'APPLIED')
		 AND (XAO.DELETE_FLAG is null or XAO.DELETE_FLAG = 'N')
     -- AND XAO.REF_ACCOUNT_CLASS = 'REV'

      (SELECT /*+USE_HASH(CLA2 LEDE2 MER2 A)*/
        DISTINCT A.AR_INVOICE_ID,
                 LEDE2.LC_DR_AMT - LEDE2.LC_CR_AMT LOCAL_DISTRIBUTION_LINE_AMT,
                 LEDE2.TC_DR_AMT - LEDE2.TC_CR_AMT DISTRIBUTION_LINE_AMT
          FROM DWIFIN.DWI_XLA_AR_INVOICE_DISTR_LINE A,
               DWIFIN.DWI_XLA_SUB_MODULE_ENTRY_REL  MER2,
               DWIFIN.DWI_XLA_SUB_LEDGER_ENTRY      LEDE2,
               DWIMD_DW1.DWI_MD_CLASS                   CLA2
         WHERE A.AR_INVOICE_DISTR_LINE_ID = MER2.SUB_MODULE_ENTRY_ID
           AND MER2.SUB_LEDGER_ID = LEDE2.SUB_LEDGER_ID
           AND MER2.SUB_LEDGER_ENTRY_ID = LEDE2.SUB_LEDGER_ENTRY_ID
           AND LEDE2.ACCOUNT_CLASS_ID = CLA2.CLASS_ID
              --AND CLA1.CODE IN ('REV', 'UNEARN')
           AND MER2.SOURCE_APPLICATION_ID IN (222, 866310008)
           AND LEDE2.SOURCE_APPLICATION_ID IN (222, 866310008)
           AND A.SET_OF_BOOKS_ID = '10013783'
           AND CLA2.CODE IN ('RECEIVABLE')
           AND A.DEL_FLAG = 'N'
           AND A.DEL_FLAG = 'N'
					 AND (A.DELETE_FLAG is null or A.DELETE_FLAG = 'N')/*202403版本WAVE4-ERP解耦*/) TT

   AND ARA.DEL_FLAG = 'N'
   AND T2.DEL_FLAG = 'N'
   AND T3.DEL_FLAG = 'N'
	 AND (ARA.DELETE_FLAG is null or ARA.DELETE_FLAG = 'N')
	 AND (T3.DELETE_FLAG is null or T3.DELETE_FLAG = 'N')

优化后执行计划如下图所示，未出现优化器估算不准的情况，提高了SQL执行性能，也能避免出现计划跳变的问题。