PostgreSQL的缩小数据集优化过程一例
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了PostgreSQL的缩小数据集优化过程一例,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6750字,纯文字阅读大概需要10分钟。
内容图文
![PostgreSQL的缩小数据集优化过程一例](/upload/InfoBanner/zyjiaocheng/549/2137176679c343a0afdd04bb1e0d1049.jpg)
小黄同学拿过来一句SQL问我,看看能不能优化,看了下,数据量不是很大,查询还不算很复杂。以下是记录优化过程: DB:PostgreSQ
小黄同学拿过来一句SQL问我,看看能不能优化,看了下,数据量不是很大,查询还不算很复杂。以下是记录优化过程:
DB:PostgreSQL 9.1
OS:CentOS 6
count(d_personal_report_view) ~ 9K条,涉及金额数量的字段类型为numeric(9,2)类型
原始SQL:
一.分析
看其结果是要取10条不同的数据做报表展示,用了同一张表做了两次关联,没有涉及查询条件,索引效果不大。分析这个SQL好不好,先看一下执行计划
二.优化过程
1.取消两张表的关联,只取一次查询 优化后的SQL: select dc.doctor_name,dc.department_name,dc.hospital_name,dc.part_hospital_name,dc.part_hospital_id,dc.department_id,dc.doctor_id, coalesce(sum(coalesce(dc.surgery_amount, '0.0')), '0.0') as surgery_amount,sum(coalesce(dc.surgery_quantity, 0)) as surgery_quantity, coalesce(sum(coalesce(dc.repair_amount, '0.0')), '0.0') as repair_amount,sum(coalesce(dc.repair_quantity, 0)) as repair_quantity, coalesce(sum(coalesce(dc.orthod_amount, '0.0')), '0.0') as orthod_amount,sum(coalesce(dc.orthod_quantity, 0)) as orthod_quantity, coalesce(sum(coalesce(dc.radiation_amount, '0.0')), '0.0') as radiation_amount,sum(coalesce(dc.radiation_quantity, 0)) as radiation_quantity, coalesce(sum(coalesce(dc.teethcln_amount, '0.0')), '0.0') as teethcln_amount,sum(coalesce(dc.teethcln_quantity, 0)) as teethcln_quantity, coalesce(sum(coalesce(dc.crop_amount, '0.0')), '0.0') as crop_amount,sum(coalesce(dc.crop_quantity, 0)) as crop_quantity, coalesce(sum(coalesce(dc.assay_amount, '0.0')), '0.0') as assay_amount,sum(coalesce(dc.assay_quantity, 0)) as assay_quantity, coalesce(sum(coalesce(dc.drugs_amount, '0.0')), '0.0') as drugs_amount,sum(coalesce(dc.drugs_quantity, 0)) as drugs_quantity, coalesce(sum(coalesce(dc.regist_amount, '0.0')), '0.0') as regist_amount,sum(coalesce(dc.regist_quantity, 0)) as regist_quantity, coalesce(sum(coalesce(dc.others_amount, '0.0')), '0.0') as others_amount,sum(coalesce(dc.others_quantity, 0)) as others_quantity, coalesce(sum(surgery_amount + repair_amount + orthod_amount + radiation_amount + teethcln_amount + crop_amount + assay_amount + drugs_amount + regist_amount + others_amount), '0.0') as totalRowAmount, coalesce(sum(surgery_quantity + repair_quantity + orthod_quantity + radiation_quantity + teethcln_quantity + crop_quantity + assay_quantity + drugs_quantity + regist_quantity + others_quantity), 0) as totalRowQuantity from d_personal_report_view dc where 1=1 group by dc.doctor_name,dc.department_name,dc.hospital_name,dc.part_hospital_name,dc.part_hospital_id,dc.department_id,dc.doctor_id order by dc.part_hospital_id,dc.doctor_id limit 10 offset 0
查看其执行计划:
2.其执行计划中的cost还是有点高,再次优化 postgresql中的sum会自动把null值替换成0,并考虑到不会存入''或者' '等空值,故可以去掉里面的coalesce转换函数。
优化后的SQL: select dc.doctor_name,dc.department_name,dc.hospital_name,dc.part_hospital_name,dc.part_hospital_id,dc.department_id,dc.doctor_id, sum(dc.surgery_amount) as surgery_amount,sum(dc.surgery_quantity) as surgery_quantity, sum(dc.repair_amount) as repair_amount,sum(dc.repair_quantity) as repair_quantity, sum(dc.orthod_amount) as orthod_amount,sum(dc.orthod_quantity) as orthod_quantity, sum(dc.radiation_amount) as radiation_amount,sum(dc.radiation_quantity) as radiation_quantity, sum(dc.teethcln_amount) as teethcln_amount,sum(dc.teethcln_quantity) as teethcln_quantity, sum(dc.crop_amount) as crop_amount,sum(dc.crop_quantity) as crop_quantity, sum(dc.assay_amount) as assay_amount,sum(dc.assay_quantity) as assay_quantity, sum(dc.drugs_amount) as drugs_amount,sum(dc.drugs_quantity) as drugs_quantity, sum(dc.regist_amount) as regist_amount,sum(dc.regist_quantity) as regist_quantity, sum(dc.others_amount) as others_amount,sum(dc.others_quantity) as others_quantity, sum(surgery_amount + repair_amount + orthod_amount + radiation_amount + teethcln_amount + crop_amount + assay_amount + drugs_amount + regist_amount + others_amount) as totalRowAmount, sum(surgery_quantity + repair_quantity + orthod_quantity + radiation_quantity + teethcln_quantity + crop_quantity + assay_quantity + drugs_quantity + regist_quantity + others_quantity) as totalRowQuantity from d_personal_report_view dc where 1=1 group by dc.doctor_name,dc.department_name,dc.hospital_name,dc.part_hospital_name,dc.part_hospital_id,dc.department_id,dc.doctor_id order by dc.part_hospital_id,dc.doctor_id limit 10 offset 0
内容总结
以上是互联网集市为您收集整理的PostgreSQL的缩小数据集优化过程一例全部内容,希望文章能够帮你解决PostgreSQL的缩小数据集优化过程一例所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。