一次复杂的SQL优化过程

 一、背景

这个复杂的查询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 (select d.*, nvl(s.paytype, '其他') as paytype, d.keyid as realId
                      from budget_t_distinfo d
                               left join busi_t_budgetdetail s
                                         on s.keyid = d.bg_bid
                      where 1 = 1
                        and d.bg_detailmoney > 0
                        and d.bg_source in ('1', '3')
                      union all
                      select d.*,
                             nvl(s.paytype, '其他')  as paytype,
                             nvl(a.keyid, d.keyid) as realId
                      from budget_t_distinfo d
                               left join busi_t_budgetdetail s
                                         on s.keyid = d.bg_bid
                               left join budget_t_distinfo a
                                         on a.bg_year = d.bg_year
                                             and a.bg_type = d.bg_type
                                             and a.bg_deptid = d.bg_deptid
                                             and a.bg_functiongcode = d.bg_functiongcode
                                             and a.bg_projectcode = d.bg_projectcode
                                             and a.bg_encocode = d.bg_encocode
                                             and a.bg_source in ('1', '3')
                      where 1 = 1
                        and d.bg_detailmoney > 0
                        and d.bg_source in ('2', '6')
                     ) 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 v.f1, v.f2, v.f3, v.f7, v.btype, v.bmname, sum(v.debitamount) as usedMoney
           from view_bd_acc v
           where 1 = 1
             and v.unitsid = 825
             and v.year = 2022
           group by v.f1, v.f2, v.f3, v.f7, v.btype, v.bmname)
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
    and nvl(b.f3, 0) = nvl(decode(t.bg_projectname, '请选择', '', t.bg_projectname), 0)
    and b.f7 = decode(t.bg_source, 1, '本年预算', 2, '本年预算', 3, '结转资金')
    and b.btype = decode(t.bg_type, 1, '基本支出', '项目支出')
    and b.bmname = t.bg_deptname
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
                            ->  Hash Right Join  (cost=103.47..696.05 rows=1280 width=247) (actual time=11.274..14.141 rows=1276 loops=1)
                                  Hash Cond: (s.keyid = d_1.bg_bid)
                                  Buffers: shared hit=65 read=381
                                  I/O Timings: read=8.926
                                  ->  Seq Scan on busi_t_budgetdetail s  (cost=0.00..528.02 rows=13802 width=19) (actual time=0.685..9.994 rows=13802 loops=1)
                                        Buffers: shared hit=9 read=381
                                        I/O Timings: read=8.926
                                  ->  Hash  (cost=87.47..87.47 rows=1280 width=201) (actual time=1.613..1.614 rows=1276 loops=1)
                                        Buckets: 2048  Batches: 1  Memory Usage: 291kB
                                        Buffers: shared hit=56
                                        ->  Seq Scan on budget_t_distinfo d_1  (cost=0.00..87.47 rows=1280 width=201) (actual time=0.014..0.983 rows=1276 loops=1)
                                              Filter: ((bg_detailmoney > '0'::numeric) AND ((bg_source)::text = ANY ('{1,3}'::text[])))
                                              Rows Removed by Filter: 822
                                              Buffers: shared hit=56
                      ->  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
                            ->  Hash Left Join  (cost=134.69..290.76 rows=7 width=256) (actual time=2.120..2.184 rows=11 loops=1)
                                  Hash Cond: (((d_2.bg_year)::text = (a.bg_year)::text) AND ((d_2.bg_type)::text = (a.bg_type)::text) AND ((d_2.bg_deptid)::text = (a.bg_deptid)::text) AND ((d_2.bg_functiongcode)::text = (a.bg_functiongcode)::text) AND ((d_2.bg_projectcode)::text = (a.bg_projectcode)::text) AND ((d_2.bg_encocode)::text = (a.bg_encocode)::text))
                                  Buffers: shared hit=145
                                  ->  Nested Loop Left Join  (cost=0.29..145.59 rows=7 width=214) (actual time=1.010..1.069 rows=11 loops=1)
                                        Buffers: shared hit=89
                                        ->  Seq Scan on budget_t_distinfo d_2  (cost=0.00..87.47 rows=7 width=201) (actual time=0.986..1.016 rows=11 loops=1)
                                              Filter: ((bg_detailmoney > '0'::numeric) AND ((bg_source)::text = ANY ('{2,6}'::text[])))
                                              Rows Removed by Filter: 2087
                                              Buffers: shared hit=56
                                        ->  Index Scan using busi_t_budgetdetail_busi_pk_budgetdetail on busi_t_budgetdetail s_1  (cost=0.29..8.30 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=11)
                                              Index Cond: (keyid = d_2.bg_bid)
                                              Buffers: shared hit=33
                                  ->  Hash  (cost=82.22..82.22 rows=2087 width=46) (actual time=1.091..1.092 rows=2085 loops=1)
                                        Buckets: 4096  Batches: 1  Memory Usage: 196kB
                                        Buffers: shared hit=56
                                        ->  Seq Scan on budget_t_distinfo a  (cost=0.00..82.22 rows=2087 width=46) (actual time=0.016..0.568 rows=2087 loops=1)
                                              Filter: ((bg_source)::text = ANY ('{1,3}'::text[]))
                                              Rows Removed by Filter: 11