从2秒到2毫秒，一则SQL优化案例

零 背景说明

这是生产环境下，一则PostgreSQL数据库SQL优化的案例小结：单条SQL执行时间，从优化前2秒到优化后，降低到2毫秒，性能提升1000倍。数据库版本为PostgreSQL 9.3。原始SQL语句如下：

优化前执行计划如下：

一 分析问题

结合执行来分析SQL语句，查看表结构，数据量分布：

看到，这是一个简单的2张表做join连接的SQL查询语句，优化器选择了嵌套循环nested loop的关联方式，其中一张表t_report_type为小表，数据量为459，优化器选择全表扫描的方式获取数据，另外一张表t_report_temp数据量相对多一些，366132条数据，但是满足条件的数据却只有1126条，同时选择了index only scan的方式，单次执行耗时4.744毫秒。

这是从执行计划看到的基本信息，能优化的突破口基本也就是对于表的访问能不能更快？虽然这里选择的是index only scan的方式。

三 解决问题

先尝试重新收集表的统计信息，再查看其执行计划是否有好转？

发现，并没有好转。观察到其执行计划中，对于相对大的那个表t_report_temp，优化器选择了通过t_report_temp_01这个index来做的index only scan，每次耗时4毫秒左右，但是loops=459，累积起来就1800多毫秒了。

同时，该indext_report_temp_01是一个联合index，基于(corp_id, industry_id, partner_id, report_code, report_type, report_name) 6个字段创建的联合index，观察表和该index的大小：

发现，这个index 80MB，比表本身74MB还大。是不是推断出，此种情况下，优化器选择通过该index来获取数据不够好呢，或者说还不如直接扫描这个表呢？

开始验证：

此时，可以看到，当我们禁用index only scan，index scan，bitmap index scan之后，使优化器对于表t_report_temp选择全表扫描之后，整个SQL的执行效率反而更好。

于是，尝试在(report_type,corp_id,partner_id)创建联合index，观察一下SQL效率：

发现，此时SQL的执行耗时从之前的2秒降低到2.6毫秒。

四 小结

通过深入分析SQL执行计划、结合表结构、数据量及数据分布，是做好SQL优化的基本出发点。需要更为深入学习和分析SQL执行计划，能看懂SQL执行计划，对SQL执行计划有感觉，多看多读执行计划，多思考。

PostgreSQL官方文档中，关于执行计划描述，有这么一句话：

Plan-reading is an art that requires some experience to master, but this section attempts to cover the basics.