Postgresql数据库脚本性能优化纪要

postgresql脚本性能优化

总结一下这段时间在编写PG脚本过程中遇到的性能问题，记录几个主要的脚本性能优化方法，现在记录的可能不完整，后续再陆续补充。

脚本性能问题排查方法

在编写脚本的第一阶段，主要考虑的是功能的实现，并未考虑性能问题，所以刚完成的脚本多多少少都会有性能的问题。
在这里，先介绍下我在性能优化过程中排查耗时点的方法，主要理念是先整体，再细分，逐层剥离的方法。

• 排查方法
  1，分析整个脚本的主要功能点，并划分成若敢干个功能块。
  2，把这若干个功能块分为若干步骤，对每个步骤增加耗时的日志记录。
  3，找出耗时较多的步骤，逐个分析耗时原因。分析耗时多的步骤中的SQL语句，单独执行各个语句，确定耗时多的几条语句。
  4，剥离出这些语句单独执行，并输出执行计划，根据执行计划确定性能差的原因。
  5，找到原因，针对性的进行优化。

优化方法

• 索引
  确保带条件的查询语句都能走索引。根据查询语句的执行计划分析查询是全表扫描还是索引扫描。
  执行计划相关说明参考PG执行计划.

• 索引字段

  避免在查询条件中，对索引字段使用聚合函数（函数索引除外），对索引字段使用聚合函数会导致无法正常通过索引查询，严重影响查询性能。
  比如有如下查询语句，从test表中查询2016年6月1号到5号的数据，且id=2，data_type=2，test表建立的索引包含字段id，log_time和data_type：

  select * from test where id = 2 and cast(log_time as date) between '2016-06-01' and '2016-06-05' and data_type = 2;

  由于查询条件中队log_time字段使用了聚合函数，导致该查询无法正常命中包含id，log_time和data_type三个字段的索引。

• 联合查询
  使用联合查询替代游标循环。
  经常遇到从两张表中联合查询的情况，此类查询也可使用游标的方式，先从一张表中查出所需数据到游标，然后再循环游标，在循环中再查询另一张表。
  在大多数情况下，使用游标循环的方式比联合查询性能差很多，所以在遇到使用游标循环的实现，首先要考虑能否使用联合查询代替。

• 数据缓存
  对于一个脚本需要查询多张表的数据的业务，或者跨库查询的业务，有必要把所需的数据提前缓存的一张表中，加快查询时的性能。

• 格式转换
  避免在查询条件中执行格式转换操作，如：

  select * from test where log_time > cast(current_date as timestamp);

  此类情况，应提前使用参数存储转换后的数据，然后在查询条件中使用此参数。

• 不用count（*）
  在判断某个元素是否存在时，尽量不要用count（*）来判断，如磐石是否存在表test，语句如下：

  select count(*) from pg_class where relname = 'test';

  这种实现方式性能较差，建议使用类似：

  select relname from pg_class where relname = 'test';if found then... ....end if;

• 自动analyze
  打开配置文件中的auto analyze功能，并设置合理的触发条件，当表里的数据更新或者删除一定条数的数据后，自动触发pg数据库的analyze功能，能够加快查询数据的性能。