一次复杂查询的优化过程 - 函数稳定性引发的性能问题
一、背景
这个复杂的查询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
Buffers: shared hit=56
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
-> Sort (cost=39621.18..39621.19 rows=4 width=224) (actual time=447.580..447.593 rows=22 loops=1)
Sort Key: v.f1, v.f2, v.f3, v.f7, v.btype, v.bmname
Sort Method: quicksort Memory: 28kB
Buffers: shared hit=304972 read=387
I/O Timings: read=47.456
-> Subquery Scan on v (cost=39620.83..39621.14 rows=4 width=224) (actual time=447.507..447.576 rows=22 loops=1)
Buffers: shared hit=304972 read=387
I/O Timings: read=47.456
-> GroupAggregate (cost=39620.83..39621.10 rows=4 width=452) (actual time=447.506..447.572 rows=22 loops=1)
Group Key: t_1.btype, t_1.f1, t_1.f2, t_1.f3, t_1.f7, t_1.bmname, t_1.paytype, t_1.unitsid, t_1.accountperiod, t_1.year, p.departcode
Buffers: shared hit=304972 read=387
I/O Timings: read=47.456
-> Sort (cost=39620.83..39620.84 rows=4 width=356) (actual time=447.495..447.507 rows=65 loops=1)
Sort Key: t_1.btype, t_1.f1, t_1.f2, t_1.f3, t_1.f7, t_1.bmname, t_1.paytype, t_1.accountperiod, p.departcode
Sort Method: quicksort Memory: 41kB
Buffers: shared hit=304972 read=387
I/O Timings: read=47.456
-> Hash Right Join (cost=39497.72..39620.79 rows=4 width=356) (actual time=447.105..447.370 rows=65 loops=1)
Hash Cond: ((p.unitsid = t_1.unitsid) AND ((p.departname)::text = (t_1.bmname)::text))
Buffers: shared hit=304972 read=387
I/O Timings: read=47.456
-> Seq Scan on sys_t_department p (cost=0.00..122.62 rows=58 width=21) (actual time=1.787..2.017 rows=58 loops=1)
Filter: ((unitsid = '825'::numeric) AND ((useflag)::text = '1'::text))
Rows Removed by Filter: 4717
Buffers: shared hit=3 read=48
I/O Timings: read=1.472
-> Hash (cost=39497.66..39497.66 rows=4 width=352) (actual time=445.308..445.317 rows=65 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 18kB
Buffers: shared hit=304969 read=339
I/O Timings: read=45.984
-> Subquery Scan on t_1 (cost=39410.84..39497.66 rows=4 width=352) (actual time=436.419..445.254 rows=65 loops=1)
Buffers: shared hit=304969 read=339
I/O Timings: read=45.984
-> Append (cost=39410.84..39497.62 rows=4 width=352) (actual time=436.418..445.236 rows=65 loops=1)
Buffers: shared hit=304969 read=339
I/O Timings: read=45.984
-> Nested Loop Left Join (cost=39410.84..39420.59 rows=1 width=325) (actual time=436.417..444.912 rows=47 loops=1)
Filter: (NVL(s_2.keyid, '825'::numeric) = '825'::numeric)
Buffers: shared hit=304890 read=339
I/O Timings: read=45.984
-> GroupAggregate (cost=39410.27..39410.31 rows=1 width=1949) (actual time=436.073..436.232 rows=47 loops=1)
Group Key: j0.keyid, v_1.accountid, v_1.year, v_1.accountperiod, v_1.transitroleid, j.subject, v_1.status
Buffers: shared hit=302942 read=339
I/O Timings: read=45.984
-> Sort (cost=39410.27..39410.28 rows=1 width=1793) (actual time=436.057..436.068 rows=47 loops=1)
Sort Key: j0.keyid, v_1.accountid, v_1.accountperiod, v_1.transitroleid, j.subject, v_1.status
Sort Method: quicksort Memory: 45kB
Buffers: shared hit=302942 read=339
I/O Timings: read=45.984
-> Nested Loop (cost=27587.45..39410.26 rows=1 width=1793) (actual time=280.458..436.004 rows=47 loops=1)
Buffers: shared hit=302942 read=339
I/O Timings: read=45.984
-> Hash Join (cost=27587.03..34466.00 rows=1255 width=1783) (actual time=134.776..229.053 rows=70418 loops=1)
Hash Cond: (j0.journalid = j.keyid)
Buffers: shared hit=21623 read=42
I/O Timings: read=1.045
-> Seq Scan on accounting_journalassist j0 (cost=0.00..6548.60 rows=125857 width=1657) (actual time=0.006..31.338 rows=167439 loops=1)
Filter: ((f9 IS NOT NULL) AND ((f6 IS NULL) OR ((f6)::text = '001'::text)))
Rows Removed by Filter: 117649
Buffers: shared hit=2985
-> Hash (cost=27531.84..27531.84 rows=4415 width=138) (actual time=134.752..134.753 rows=70505 loops=1)
Buckets: 131072 (originally 8192) Batches: 1 (originally 1) Memory Usage: 20392kB
Buffers: shared hit=18638 read=42
I/O Timings: read=1.045
-> Seq Scan on accounting_journal j (cost=0.00..27531.84 rows=4415 width=138) (actual time=0.020..116.354 rows=70505 loops=1)
Filter: ((split_part((subject)::text, ' '::text, 1) ~~ '71010101%'::text) OR (split_part((subject)::text, ' '::text, 1) ~~ '71010102%'::text))
Rows Removed by Filter: 372087
Buffers: shared hit=18638 read=42
I/O Timings: read=1.045
-> Index Scan using accounting_voucher_pk_voucher on accounting_voucher v_1 (cost=0.42..3.94 rows=1 width=22) (actual time=0.003..0.003 rows=0 loops=70418)
Index Cond: (keyid = j.voucherid)
Filter: ((DECODE(sn, NULL::boolean, '0'::numeric, sn) > '0'::numeric) AND ((status)::text <> '4'::text) AND ((status)::text <> '4'::text) AND (year = '2022'::numeric) AND (DECODE((to_char((withto)::text))::character varying, NULL::boolean, '0'::numeric, text_numeric(to_char((withto)::text))) = '0'::numeric))
Rows Removed by Filter: 1
Buffers: shared hit=281319 read=297
I/O Timings: read=44.939
-> Nested Loop Left Join (cost=0.57..8.75 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=47)
Buffers: shared hit=329
-> Nested Loop Left Join (cost=0.42..8.49 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=47)
Buffers: shared hit=235
-> Index Scan using accounting_account_pk_accounting_account on accounting_account b_1 (cost=0.28..8.29 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=47)
Index Cond: (keyid = v_1.accountid)
Buffers: shared hit=141
-> Index Scan using accounting_unit_pk_accounting_unit on accounting_unit u (cost=0.14..0.20 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=47)
Index Cond: (keyid = b_1.unitid)
Buffers: shared hit=94
-> Index Scan using sys_t_units_sys_ix_units_unitscode on sys_t_units s_2 (cost=0.14..0.25 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=47)
Index Cond: ((unitscode)::text = (u.code)::text)
Buffers: shared hit=94
-> Subquery Scan on *SELECT* 2_1 (cost=13.52..77.00 rows=3 width=298) (actual time=0.195..0.313 rows=18 loops=1)
Buffers: shared hit=79
-> Hash Right Join (cost=13.52..76.97 rows=3 width=298) (actual time=0.193..0.308 rows=18 loops=1)
Hash Cond: (s_3.borrowid = b_2.keyid)
Buffers: shared hit=79
-> Seq Scan on busi_t_borrow_subject s_3 (cost=0.00..32.28 rows=628 width=126) (actual time=0.003..0.077 rows=628 loops=1)
Buffers: shared hit=26
-> Hash (cost=13.49..13.49 rows=3 width=24) (actual time=0.042..0.042 rows=18 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=15
-> Index Scan using busi_t_borrow_busi_ix_borrow_year on busi_t_borrow b_2 (cost=0.28..13.49 rows=3 width=24) (actual time=0.013..0.038 rows=18 loops=1)
Index Cond: (year_ = '2022'::numeric)
Filter: ((unitsid = '825'::numeric) AND ((historystatus)::integer < 3) AND ((accountstatus)::integer >= 3))
Rows Removed by Filter: 10
Buffers: shared hit=15
SubPlan 2
-> Aggregate (cost=8.31..8.32 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=18)
Buffers: shared hit=38
-> Index Scan using busi_t_repay_idx_borrowid on busi_t_repay r (cost=0.28..8.30 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=18)
Index Cond: (borrowid = b_2.keyid)
Filter: (((historystatus)::integer < 3) AND ((accountstatus)::integer >= 3))
Rows Removed by Filter: 0
Buffers: shared hit=38
-> 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 函数的调用。
以下是最终优化后的查询计划，用时0.15秒。可以看到，CTE t 进行了扁平化，而不是 CTE t 的结果先执行出来，再进行连接。
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
-> Sort (cost=7481.84..7481.85 rows=4 width=224) (actual time=49.837..49.851 rows=22 loops=1)
Sort Key: v.f1, v.f2, v.f3, v.f7, v.btype, v.bmname
Sort Method: quicksort Memory: 28kB
Buffers: shared hit=6925
-> Subquery Scan on v (cost=7481.49..7481.80 rows=4 width=224) (actual time=49.762..49.831 rows=22 loops=1)
Buffers: shared hit=6925
-> GroupAggregate (cost=7481.49..7481.76 rows=4 width=452) (actual time=49.762..49.828 rows=22 loops=1)
Group Key: t.btype, t.f1, t.f2, t.f3, t.f7, t.bmname, t.paytype, t.unitsid, t.accountperiod, t.year, p.departcode
Buffers: shared hit=6925
-> Sort (cost=7481.49..7481.50 rows=4 width=356) (actual time=49.753..49.766 rows=65 loops=1)
Sort Key: t.btype, t.f1, t.f2, t.f3, t.f7, t.bmname, t.paytype, t.accountperiod, p.departcode
Sort Method: quicksort Memory: 41kB
Buffers: shared hit=6925
-> Hash Right Join (cost=7426.68..7481.45 rows=4 width=356) (actual time=49.588..49.630 rows=65 loops=1)
Hash Cond: ((p.unitsid = t.unitsid) AND ((p.departname)::text = (t.bmname)::text))
Buffers: shared hit=6925
-> Bitmap Heap Scan on sys_t_department p (cost=4.73..59.06 rows=58 width=21) (actual time=0.017..0.027 rows=58 loops=1)
Recheck Cond: (unitsid = '825'::numeric)
Filter: ((useflag)::text = '1'::text)
Heap Blocks: exact=3
Buffers: shared hit=6
-> Bitmap Index Scan on sys_t_department_unitsid (cost=0.00..4.72 rows=58 width=0) (actual time=0.009..0.009 rows=58 loops=1)
Index Cond: (unitsid = '825'::numeric)
Buffers: shared hit=3
-> Hash (cost=7421.89..7421.89 rows=4 width=352) (actual time=49.563..49.572 rows=65 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 18kB
Buffers: shared hit=6919
-> Subquery Scan on t (cost=7335.07..7421.89 rows=4 width=352) (actual time=40.958..49.531 rows=65 loops=1)
Buffers: shared hit=6919
-> Append (cost=7335.07..7421.85 rows=4 width=352) (actual time=40.957..49.516 rows=65 loops=1)
Buffers: shared hit=6919
-> Nested Loop Left Join (cost=7335.07..7344.82 rows=1 width=325) (actual time=40.956..49.252 rows=47 loops=1)
Filter: (NVL(s_2.keyid, '825'::numeric) = '825'::numeric)
Buffers: shared hit=6840
-> GroupAggregate (cost=7334.50..7334.55 rows=1 width=1949) (actual time=40.628..40.767 rows=47 loops=1)
Group Key: j0.keyid, v_1.accountid, v_1.year, v_1.accountperiod, v_1.transitroleid, j.subject, v_1.status
Buffers: shared hit=4892
-> Sort (cost=7334.50..7334.51 rows=1 width=1793) (actual time=40.611..40.618 rows=47 loops=1)
Sort Key: j0.keyid, v_1.accountid, v_1.accountperiod, v_1.transitroleid, j.subject, v_1.status
Sort Method: quicksort Memory: 45kB
Buffers: shared hit=4892
-> Nested Loop (cost=0.84..7334.49 rows=1 width=1793) (actual time=33.344..40.578 rows=47 loops=1)
Buffers: shared hit=4892
-> Nested Loop (cost=0.42..7331.13 rows=5 width=148) (actual time=33.334..40.435 rows=47 loops=1)
Buffers: shared hit=4704
-> Seq Scan on accounting_voucher v_1 (cost=0.00..7105.20 rows=10 width=22) (actual time=32.981..39.962 rows=123 loops=1)
Filter: ((sn > '0'::numeric) AND ((status)::text <> '4'::text) AND (NVL(withto, '0'::numeric) = '0'::numeric) AND (year = '2022'::numeric))
Rows Removed by Filter: 149187
Buffers: shared hit=4119
-> Index Scan using accounting_journal_index_voucherid on accounting_journal j (cost=0.42..22.58 rows=1 width=138) (actual time=0.003..0.004 rows=0 loops=123)
Index Cond: (voucherid = v_1.keyid)
Filter: ((split_part((subject)::text, ' '::text, 1) ~~ '71010101%'::text) OR (split_part((subject)::text, ' '::text, 1) ~~ '71010102%'::text))
Rows Removed by Filter: 4
Buffers: shared hit=585
-> Index Scan using accounting_journalassist_index_journalid on accounting_journalassist j0 (cost=0.42..0.66 rows=1 width=1657) (actual time=0.002..0.003 rows=1 loops=47)
Index Cond: (journalid = j.keyid)
Filter: ((f9 IS NOT NULL) AND ((f6 IS NULL) OR ((f6)::text = '001'::text)))
Buffers: shared hit=188
-> Nested Loop Left Join (cost=0.57..8.75 rows=1 width=10) (actual time=0.004..0.005 rows=1 loops=47)
Buffers: shared hit=329
-> Nested Loop Left Join (cost=0.42..8.49 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=47)
Buffers: shared hit=235
-> Index Scan using accounting_account_pk_accounting_account on accounting_account b_1 (cost=0.28..8.29 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=47)
Index Cond: (keyid = v_1.accountid)
Buffers: shared hit=141
-> Index Scan using accounting_unit_pk_accounting_unit on accounting_unit u (cost=0.14..0.20 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=47)
Index Cond: (keyid = b_1.unitid)
Buffers: shared hit=94
-> Index Scan using sys_t_units_sys_ix_units_unitscode on sys_t_units s_2 (cost=0.14..0.25 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=47)
Index Cond: ((unitscode)::text = (u.code)::text)
Buffers: shared hit=94
-> Subquery Scan on *SELECT* 2_1 (cost=13.52..77.00 rows=3 width=298) (actual time=0.154..0.254 rows=18 loops=1)
Buffers: shared hit=79
-> Hash Right Join (cost=13.52..76.97 rows=3 width=298) (actual time=0.153..0.251 rows=18 loops=1)
Hash Cond: (s_3.borrowid = b_2.keyid)
Buffers: shared hit=79
-> Seq Scan on busi_t_borrow_subject s_3 (cost=0.00..32.28 rows=628 width=126) (actual time=0.003..0.059 rows=628 loops=1)
Buffers: shared hit=26
-> Hash (cost=13.49..13.49 rows=3 width=24) (actual time=0.036..0.036 rows=18 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=15
-> Index Scan using busi_t_borrow_busi_ix_borrow_year on busi_t_borrow b_2 (cost=0.28..13.49 rows=3 width=24) (actual time=0.008..0.032 rows=18 loops=1)
Index Cond: (year_ = '2022'::numeric)
Filter: ((unitsid = '825'::numeric) AND ((historystatus)::integer < 3) AND ((accountstatus)::integer >= 3))
Rows Removed by Filter: 10
Buffers: shared hit=15
SubPlan 1
-> Aggregate (cost=8.31..8.32 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=18)
Buffers: shared hit=38
-> Index Scan using busi_t_repay_idx_borrowid on busi_t_repay r (cost=0.28..8.30 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=18)
Index Cond: (borrowid = b_2.keyid)
Filter: (((historystatus)::integer < 3) AND ((accountstatus)::integer >= 3))
Rows Removed by Filter: 0
Buffers: shared hit=38
-> 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
-> Nested Loop Left Join (cost=0.29..147.78 rows=6 width=247) (actual time=0.597..0.795 rows=72 loops=1)
Buffers: shared hit=272
-> Seq Scan on budget_t_distinfo d (cost=0.00..97.96 rows=6 width=201) (actual time=0.586..0.671 rows=72 loops=1)
Filter: ((bg_detailmoney > '0'::numeric) AND ((bg_source)::text = ANY ('{1,3}'::text[])) AND ((bg_year)::integer = 2022))
Rows Removed by Filter: 2026
Buffers: shared hit=56
-> Index Scan using busi_t_budgetdetail_busi_pk_budgetdetail on busi_t_budgetdetail s (cost=0.29..8.30 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=72)
Index Cond: (keyid = d.bg_bid)
Buffers: shared hit=216
-> 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
-> Nested Loop Left Join (cost=98.27..235.48 rows=1 width=256) (actual time=1.119..1.122 rows=1 loops=1)
Buffers: shared hit=115
-> Hash Right Join (cost=97.99..227.18 rows=1 width=206) (actual time=1.113..1.115 rows=1 loops=1)
Hash Cond: (((a.bg_year)::text = (d_1.bg_year)::text) AND ((a.bg_type)::text = (d_1.bg_type)::text) AND ((a.bg_deptid)::text = (d_1.bg_deptid)::text) AND ((a.bg_functiongcode)::text = (d_1.bg_functiongcode)::text) AND ((a.bg_projectcode)::text = (d_1.bg_projectcode)::text) AND ((a.bg_encocode)::text = (d_1.bg_encocode)::text))
Buffers: shared hit=112
-> Seq Scan on budget_t_distinfo a (cost=0.00..82.22 rows=2087 width=46) (actual time=0.004..0.384 rows=2087 loops=1)
Filter: ((bg_source)::text = ANY ('{1,3}'::text[]))
Rows Removed by Filter: 11
Buffers: shared hit=56
-> Hash (cost=97.96..97.96 rows=1 width=201) (actual time=0.457..0.458 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=56
-> Seq Scan on budget_t_distinfo d_1 (cost=0.00..97.96 rows=1 width=201) (actual time=0.451..0.453 rows=1 loops=1)
Filter: ((bg_detailmoney > '0'::numeric) AND ((bg_source)::text = ANY ('{2,6}'::text[])) AND ((bg_year)::integer = 2022))
Rows Removed by Filter: 2097
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.004..0.004 rows=1 loops=1)
Index Cond: (keyid = d_1.bg_bid)
Buffers: shared hit=3
Planning Time: 2.245 ms
Execution Time: 156.745 ms
三、优化过程
1、子查询平面化
子查询平面化是指优化器将把子查询融合到上层查询。
- 分析查询计划
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行。这里不仅有1200行数据无效，而且CTE包含的用户函数，被无效执行1200次，造成性能的主要问题。
造成这种现象的原因，就是子查询没有平面化。限制子查询平面化的，是用户函数属性，查询得知此函数属性是不稳定。
- 修改用户函数属性
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；
- 优化后的计划
查询计划中，没有创建CTE t，已经与上层查询融合。
用户函数的执行次数是73次，节省时长 = (57423.904-49.561)/1287*(1287-73)=54120ms 。
2、查询所需的索引
如果没有适合的索引，查询就会读取全表
- 视图带来time累计计算的黑盒，在计划中，最终用时突然增长。
Hash Right Join (cost=40002.45..40004.42 rows=7 width=377) (actual time=427.929..3568.261 rows=73 loops=1)
这是视图用时造成的。独立执行视图代码，得知对大表使用了Seq Scan，根据过滤条件，建立适当的索引。
create index busi_t_reimburse_subject_i1 on busi_t_reimburse_subject (f9, economicsubjectname, nvl(projectsubjectname, 0));
优化后的查询计划,用时节省了3.0秒
Hash Right Join (cost=40002.45..40004.42 rows=7 width=377) (actual time=370.715..470.194 rows=73 loops=1)
- B-tree索引不支持字符模糊过滤
计划中，仍有较大的Seq Scan
Seq Scan on zgf.accounting_journal j (cost=0.00..27531.84 rows=4415 width=137) (actual time=0.022..104.008 rows=70505 loops=1) Filter: ((split_part((j.subject)::text, ' '::text, 1) ~~ '71010101%'::text) OR (split_part((j.subject)::text, ' '::text, 1) ~~ '71010102%'::text)) Rows Removed by Filter: 372087
这里使用了字符模糊匹配，需要建立gin型索引
create extension sys_trgm ; create index accounting_journal_subjectpre on accounting_journal USING gin (split_part((subject), ' ', 1) gin_trgm_ops);
优化后的查询计划,用时节省了100ms
BitmapOr (cost=806.92..806.92 rows=71038 width=0) (actual time=23.473..23.474 rows=0 loops=1) -> Bitmap Index Scan on accounting_journal_subjectpre (cost=0.00..329.41 rows=29522 width=0) (actual time=11.421..11.421 rows=70525 loops=1) Index Cond: (split_part((j.subject)::text, ' '::text, 1) ~~ '71010101%'::text)" -> Bitmap Index Scan on accounting_journal_subjectpre (cost=0.00..443.37 rows=41516 width=0) (actual time=12.051..12.051 rows=41362 loops=1) Index Cond: (split_part((j.subject)::text, ' '::text, 1) ~~ '71010102%'::text)"
- 增加索引
计划中，有较大的Seq Scan
Seq Scan on zgf.sys_t_department p (cost=0.00..122.62 rows=58 width=21) (actual time=0.481..0.521 rows=58 loops=1) Filter: ((p.unitsid = '825'::numeric) AND ((p.useflag)::text = '1'::text)) Rows Removed by Filter: 4717
创建B-tree索引
create index sys_t_department_unitsid on sys_t_department (unitsid);
优化后，用时节省0.1ms
Bitmap Index Scan on sys_t_department_unitsid (cost=0.00..4.72 rows=58 width=0) (actual time=0.011..0.011 rows=58 loops=1) Index Cond: (p.unitsid = '825'::numeric)
3、用时较长的node
计划中还有几处node，用时较长，可以尝试优化一处用时较长的node
Nested Loop (cost=21761.45..52337.05 rows=1 width=1793) (actual time=161.838..293.567 rows=47 loops=1) join : accounting_journalassist , accounting_journal , accounting_voucher 等
分析结果，因为视图的过滤条件，语法繁琐，浪费了CPU时间。
--原视图代码 CREATE FORCE VIEW view_reportquery AS SELECT ... FROM ... WHERE ... AND (DECODE((to_char((v.withto)::text))::character varying, NULL::character varying, "numeric"(0), text_numeric(to_char((v.withto)::text))) = (0)::numeric) AND (DECODE(v.sn, text_numeric(NULL::character varying), "numeric"(0), v.sn) > (0)::numeric) AND ((v.status)::text <> '4'::text)) GROUP BY ...; --新视图代码 CREATE OR REPLACE VIEW view_reportquery AS SELECT ... FROM ... WHERE ... AND nvl(v.withto, 0) = 0 AND v.sn > 0 AND ... GROUP BY ... ;
优化后，用时节省150ms
Nested Loop (cost=0.84..7334.49 rows=1 width=1793) (actual time=32.992..40.572 rows=47 loops=1) join : accounting_journalassist , accounting_journal , accounting_voucher 等
其他node的优化，对性能影响较小。
四、总结
- 执行计划，不能完整反应所有的细节，重视时长与数据块的超长增长，要将其视为病灶。
- 简单的表达式，具有最优的性能。
- 查询优化之路是没有止境，目标是平衡查询的性能与兼容性。
KINGBASE研究院
