PostgreSQL 10 含多函数、动态SQL、多态的慢查询优化咨询
优化建议
- 替换自定义累乘聚合函数
MUL:利用数学等价逻辑将累乘计算改写为exp(sum(ln(字段)))(需额外处理字段为0、负数的边界情况),原生聚合函数的执行效率远高于自定义聚合,可大幅降低老化因子计算环节的开销。 - 调整函数属性:将
get_aged_data和get_aging_factors的属性从VOLATILE改为STABLE,输入参数固定时函数返回结果不变,调整后查询规划器可对函数调用做更好的优化,避免重复执行;同时将get_aging_factors的ROWS参数调整为实际返回行数(约50行),引导规划器选择更合适的关联执行计划。 - 缓存老化因子计算结果:老化因子版本和起止年份固定时,计算结果不会变化,可针对常用的查询时间范围创建物化视图预存老化因子聚合结果,查询时直接读取物化视图,无需每次实时聚合计算。
- 优化元数据查询逻辑:无需每次拼接动态SQL时都查询
information_schema系统表,可提前将各版本的数据表、老化因子表的结构元数据存入自定义元数据表,直接读取自定义表获取字段信息,减少系统表查询开销。 - 优化排序逻辑:当前查询末尾强制按
recid排序,76万条数据的排序开销占比较高,若业务侧无严格排序要求可直接去掉ORDER BY d.recid子句;若必须排序,可给数据表新增(xstate, recid)复合索引,关联查询时可直接按索引顺序返回结果,避免额外的排序操作。 - 新增老化因子表索引:给各版本老化因子表创建
(fromyear, state)复合索引,get_aging_factors的过滤、分组逻辑都可直接命中索引,避免全表扫描。 - 移除冗余类型转换:当前动态SQL中对所有字段都做了强制类型转换,大部分同类型转换为冗余操作,可清理不必要的类型转换逻辑,降低计算开销。
内容的提问来源于stack exchange,提问作者EMF
相关产品推荐
相关产品推荐

