SQL优化 -- 一例函数稳定态引发的性能问题

原创 Kingbase研究院 2022-03-20

7049

一、背景

本文展示的这个复杂的查询SQL语句，使用了用户自定义函数，多个视图嵌套，代码逻辑复杂，运行时间过长。

二、查询语句，优化前后的计划

SQL语句如下：

analyse;
explain (analyse ,buffers ,verbose ,costs ,timing )
with t as
         (select d.*, nvl(getfinanceamount(d.keyid), 0) useMoney
          from (select t.realId                  as keyId,
                       t.bg_type,
                       t.bg_year,
                       t.bg_deptname,
                       t.bg_deptId,
                       t.bg_functiongname,
                       t.bg_functiongcode,
                       t.bg_projectname,
                       t.bg_projectcode,
                       t.bg_enconame,
                       t.bg_encocode,
                       sum(t.bg_budgetmoney)     as bgBudgetMoney,
                       sum(t.bg_budgetdeptmoney) as bgBudgetDeptMoney,
                       t.bg_budgetdeptpp,
                       sum(t.bg_detailmoney)     as bgDetailMoney,
                       t.bg_detailpp,
                       t.bg_source,
                       t.bg_bid,
                       t.bg_memo,
                       t.budgetsourcetype,
                       t.paytype
                from ( ......
                      union all
                       ......
                     ) t
                group by t.realId, t.bg_type, t.bg_year, t.bg_deptname, t.bg_deptId, t.bg_functiongname,
                         t.bg_functiongcode,
                         t.bg_projectname, t.bg_projectcode, t.bg_enconame, t.bg_encocode, t.bg_budgetdeptpp,
                         t.bg_detailpp, t.bg_source, t.bg_bid, t.bg_memo, t.budgetsourcetype, t.paytype) d
         ),
     b as ( ...... )
select t.*, nvl(b.usedMoney, 0) as usedMoney
from t left join b on b.f1 = t.bg_functiongname
    and b.f2 = t.bg_enconame
    ......
where 1 = 1
  and t.bg_year = 2022
;

优化前的查询计划，用时57秒

Nested Loop Left Join  (cost=40763.36..40768.61 rows=1 width=2284) (actual time=1102.648..58109.460 rows=73 loops=1)
  Join Filter: ((b.f1 = (t.bg_functiongname)::text) AND (b.f2 = (t.bg_enconame)::text) AND ((b.bmname)::text = (t.bg_deptname)::text) AND ((NVL((b.f3)::character varying, '0'::character varying))::text = (NVL(DECODE(t.bg_projectname, '请选择'::text, NULL::character varying, t.bg_projectname), '0'::character varying))::text) AND (b.btype = (DECODE(text_numeric(t.bg_type), '1'::numeric, '基本支出'::character varying, '项目支出'::character varying))::text) AND ((b.f7)::text = (DECODE(text_numeric(t.bg_source), '1'::numeric, '本年预算'::character varying, '2'::numeric, '本年预算'::character varying, '3'::numeric, '结转资金'::character varying, NULL::character varying))::text))
  Rows Removed by Join Filter: 1157
  Buffers: shared hit=10447414 read=7332
  I/O Timings: read=1110.574
  CTE t
    ->  Subquery Scan on d  (cost=1086.55..1142.05 rows=200 width=344) (actual time=213.277..57656.258 rows=1287 loops=1)
          Buffers: shared hit=10142442 read=6945
          I/O Timings: read=1063.118
          ->  HashAggregate  (cost=1086.55..1090.05 rows=200 width=312) (actual time=17.982..22.077 rows=1287 loops=1)
                Group Key: *SELECT* 1.realid, *SELECT* 1.bg_type, *SELECT* 1.bg_year, *SELECT* 1.bg_deptname, *SELECT* 1.bg_deptid, *SELECT* 1.bg_functiongname, *SELECT* 1.bg_functiongcode, *SELECT* 1.bg_projectname, *SELECT* 1.bg_projectcode, *SELECT* 1.bg_enconame, *SELECT* 1.bg_encocode, *SELECT* 1.bg_budgetdeptpp, *SELECT* 1.bg_detailpp, *SELECT* 1.bg_source, *SELECT* 1.bg_bid, *SELECT* 1.bg_memo, *SELECT* 1.budgetsourcetype, *SELECT* 1.paytype
                Buffers: shared hit=210 read=381
                I/O Timings: read=8.926
                ->  Append  (cost=103.47..1018.98 rows=1287 width=233) (actual time=11.277..16.589 rows=1287 loops=1)
                      Buffers: shared hit=210 read=381
                      I/O Timings: read=8.926
                      ->  Subquery Scan on *SELECT* 1  (cost=103.47..708.85 rows=1280 width=233) (actual time=11.276..14.317 rows=1276 loops=1)
                            Buffers: shared hit=65 read=381
                            I/O Timings: read=8.926
                            ......
                      ->  Subquery Scan on *SELECT* 2  (cost=134.69..290.83 rows=7 width=242) (actual time=2.123..2.189 rows=11 loops=1)
                            Buffers: shared hit=145
                            ......
  CTE b
    ->  GroupAggregate  (cost=39621.18..39621.31 rows=4 width=224) (actual time=447.585..447.609 rows=16 loops=1)
          Group Key: v.f1, v.f2, v.f3, v.f7, v.btype, v.bmname
          Buffers: shared hit=304972 read=387
          I/O Timings: read=47.456
          ......
  ->  CTE Scan on t  (cost=0.00..5.00 rows=1 width=2252) (actual time=655.036..57661.296 rows=73 loops=1)
        Filter: ((bg_year)::integer = 2022)
        Rows Removed by Filter: 1214
        Buffers: shared hit=10142442 read=6945
        I/O Timings: read=1063.118
  ->  CTE Scan on b  (cost=0.00..0.08 rows=4 width=224) (actual time=6.132..6.134 rows=16 loops=73)
        Buffers: shared hit=304972 read=387
        I/O Timings: read=47.456
Planning Time: 3.436 ms
Execution Time: 58109.920 ms

执行计划耗时分析：主要耗时在subquery scan，而不是在 join and aggregate 部分。

为什么 " subquery scan on d " 会如此耗时？结合语句，可以确定耗时点是在于 getfinanceamount 函数的调用。

三、优化过程

分析查询计划

CTE t
    ->  Subquery Scan on d  (cost=1086.55..1142.05 rows=200 width=344) (actual time=49.561..57423.904 rows=1287 loops=1)
...
->  CTE Scan on t  (cost=0.00..5.00 rows=1 width=2252) (actual time=396.500..57429.582 rows=73 loops=1)

从计划中得知，CTE t生成数据1287行，最终过滤后得到数据73行（t.bg_year = 2022)。这里不仅有1200行数据无效，而且CTE包含的用户函数，被无效地执行1200次，造成性能的主要问题。

造成这种现象的原因，就是子查询没有提升。而限制子查询提升的原因，是用户函数属性，查询得知此函数属性是不稳定。

PS：子查询提升是指优化器将把子查询融合到上层查询。

修改用户函数属性

select proname,
    CASE
        WHEN p.provolatile = 'i' THEN 'immutable'
        WHEN p.provolatile = 's' THEN 'stable'
        WHEN p.provolatile = 'v' THEN 'volatile'
        END as Volatility
from zgf.pg_catalog.pg_proc p
where proname = 'getfinanceamount';

     proname      | Volatility 
------------------+------------
 getfinanceamount | volatile
(1 行记录)

--修改函数属性：

alter function getfinanceamount stable；

优化后的计划

Hash Right Join  (cost=7866.26..7868.22 rows=7 width=377) (actual time=54.484..156.509 rows=73 loops=1)
  Hash Cond: ((b.f1 = (*SELECT* 1.bg_functiongname)::text) AND (b.f2 = (*SELECT* 1.bg_enconame)::text) AND ((NVL((b.f3)::character varying, '0'::character varying))::text = (NVL(DECODE(*SELECT* 1.bg_projectname, '请选择'::text, NULL::character varying, *SELECT* 1.bg_projectname), '0'::character varying))::text) AND ((b.f7)::text = (DECODE(text_numeric(*SELECT* 1.bg_source), '1'::numeric, '本年预算'::character varying, '2'::numeric, '本年预算'::character varying, '3'::numeric, '结转资金'::character varying, NULL::character varying))::text) AND (b.btype = (DECODE(text_numeric(*SELECT* 1.bg_type), '1'::numeric, '基本支出'::character varying, '项目支出'::character varying))::text) AND ((b.bmname)::text = (*SELECT* 1.bg_deptname)::text))
  Buffers: shared hit=23216
  CTE b
    ->  GroupAggregate  (cost=7481.84..7481.97 rows=4 width=224) (actual time=49.840..49.882 rows=16 loops=1)
          Group Key: v.f1, v.f2, v.f3, v.f7, v.btype, v.bmname
          Buffers: shared hit=6925
          ......
  ->  CTE Scan on b  (cost=0.00..0.08 rows=4 width=224) (actual time=49.842..49.882 rows=16 loops=1)
        Buffers: shared hit=6925
  ->  Hash  (cost=384.11..384.11 rows=7 width=313) (actual time=2.153..2.159 rows=73 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 24kB
        Buffers: shared hit=387
        ->  GroupAggregate  (cost=383.53..384.04 rows=7 width=313) (actual time=1.998..2.092 rows=73 loops=1)
              Group Key: *SELECT* 1.realid, *SELECT* 1.bg_type, *SELECT* 1.bg_year, *SELECT* 1.bg_deptname, *SELECT* 1.bg_deptid, *SELECT* 1.bg_functiongname, *SELECT* 1.bg_functiongcode, *SELECT* 1.bg_projectname, *SELECT* 1.bg_projectcode, *SELECT* 1.bg_enconame, *SELECT* 1.bg_encocode, *SELECT* 1.bg_budgetdeptpp, *SELECT* 1.bg_detailpp, *SELECT* 1.bg_source, *SELECT* 1.bg_bid, *SELECT* 1.bg_memo, *SELECT* 1.budgetsourcetype, *SELECT* 1.paytype
              Buffers: shared hit=387
              ->  Sort  (cost=383.53..383.55 rows=7 width=234) (actual time=1.984..1.991 rows=73 loops=1)
                    Sort Key: *SELECT* 1.realid, *SELECT* 1.bg_type, *SELECT* 1.bg_year, *SELECT* 1.bg_deptname, *SELECT* 1.bg_deptid, *SELECT* 1.bg_functiongname, *SELECT* 1.bg_functiongcode, *SELECT* 1.bg_projectname, *SELECT* 1.bg_projectcode, *SELECT* 1.bg_enconame, *SELECT* 1.bg_encocode, *SELECT* 1.bg_budgetdeptpp, *SELECT* 1.bg_detailpp, *SELECT* 1.bg_source, *SELECT* 1.bg_bid, *SELECT* 1.bg_memo, *SELECT* 1.budgetsourcetype, *SELECT* 1.paytype
                    Sort Method: quicksort  Memory: 45kB
                    Buffers: shared hit=387
                    ->  Result  (cost=0.29..383.43 rows=7 width=234) (actual time=0.600..1.944 rows=73 loops=1)
                          Buffers: shared hit=387
                          ->  Append  (cost=0.29..383.36 rows=7 width=234) (actual time=0.599..1.934 rows=73 loops=1)
                                Buffers: shared hit=387
                                ->  Subquery Scan on *SELECT* 1  (cost=0.29..147.84 rows=6 width=233) (actual time=0.598..0.805 rows=72 loops=1)
                                      Buffers: shared hit=272
                                      ......
                                ->  Subquery Scan on *SELECT* 2  (cost=98.27..235.49 rows=1 width=242) (actual time=1.120..1.123 rows=1 loops=1)
                                      Buffers: shared hit=115
                                      ......
Planning Time: 2.245 ms
Execution Time: 156.745 ms