PostgreSQL SQL优化 函数稳定态引发的性能问题
1 背景知识
一个复杂的查询SQL语句，使用了用户自定义函数，多个视图嵌套，代码逻辑复杂，运行时长过长。
分析方向，基于查询计划，定位耗时较多的节点，通过改变调用对象，实现优化查询性能。
2 查询语句，优化前后的计划
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 = 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: 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 函数的调用。
3 优化过程
3.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行 （t.bg_year = 2022 条件没有下推到CTE)。这里不仅有1200行数据无效，而且CTE包含的用户函数，被无效执行1200次，造成性能的主要问题。
造成这种现象的原因，就是子查询没有平面化。限制子查询平面化的，是用户函数属性，查询得知此函数属性是不稳定。
Note
子查询平面化是指优化器将把子查询融合到上层查询。
3.2 修改用户函数属性
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 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
查询计划中，没有创建CTE，已经与上层查询融合。