PostgresSQL 性能优化笔记

最近跑case 的数据库性能出现问题，查询语句需要很久才出来。借此机会来学习整理下PG的性能优化。

1. 尽量避免排序，排序是比较消耗性能的。尽量在内存中完成。PG里面是work_mem (integer)，单位是KB，默认值是4MB。
2. 索引。如果过滤的数据量比较小时，走索引。在20%-40%，可以走索引也可以不走。大于40%基本上是全表扫描了。
3. 对索引字段计算时，必须在运算符的右侧，否则索引不生效。
4. 表之间查询的，连接处安排索引。
5. 复合索引，和MySQL一样，遵循最左前缀。
6. has join，适合结果集比较大的查询，在内存中查询。
7. nest loop，比较耗内存，左边的每一项都会和右边的一项进行关联。适用于结果集相差很大，小的在左。
8. 多表查询，查询规划器一般会帮助优化链接。如果指定left/right /full join，则会严格按照定义的顺序进行查询。
9. 可以使用explain来查看执行计划。
10. 及时更新执行计划中使用的统计信息。
11. 可以通过以下查询：

SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'test%';
relkind 类型：i-index，r-自己本身，reltuples：项目数；relpages：所占页数。

估计成本通过 （磁盘页面读取【relpages】seq_page_cost）+（行扫描【reltuples】cpu_tuple_cost）计算。默认情况下， seq_page_cost是1.0，cpu_tuple_cost是0.01。

12. 对于大数据，可以使用临时表来降低。
13. 插入优化：

• 关闭自动提交（autocommit=false）
• 多次插入数据用copy命令更高效
• 临时删除index
• 外键关联的删除

14. image.png