调整数据库性能-开发人员通常喜欢它或讨厌它。 我很喜欢这个，并且想分享一些我最近在PostgreSQL中优化执行不佳查询的方法。 我的方法并不详尽，但是对于那些只为调音而苦恼的人来说是一本教科书。
搜索慢查询
开始调优的第一个显而易见的方法是找到工作不佳的特定运算符。
pg_stats_statements
pg_stats_statements模块是一个很好的起点。 它只是跟踪SQL语句的执行统计信息，并且可以作为查找低效率查询的简便方法。
一旦安装了该模块，一个名为
pg_stat_statements的系统视图将具有其所有属性。 一旦他有机会收集足够的数据，就查找具有相对较高的
total_time值的查询
。 首先关注这些运营商。
SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
auto_explain
auto_explain模块对于查找慢速查询也很有用，但有两个明显的优点：它注册实际的执行计划，并支持使用
log_nested_statements选项记录嵌套语句。 嵌套语句是在函数内部执行的语句。 如果您的应用程序使用许多功能，则auto_explain对于获取详细的执行计划非常有用。
log_min_duration选项可根据查询执行计划的运行时间来控制记录它们。 例如，如果将该值设置为1000，则所有时间超过1秒的记录都将被注册。
索引调整
另一个重要的调整策略是确保正确使用索引。 作为前提，我们需要包括统计收集器。
Postgres Statistics Collector是一流的子系统，它收集各种有用的性能统计信息。
通过启用此收集器，您将获得大量包含所有属性的
pg_stat _...视图 。 特别是，我发现这对于查找丢失和未使用的索引特别有用。
索引缺失
缺少索引可能是提高查询性能的最简单解决方案之一。 但是，它们不是灵丹妙药，应该正确使用（稍后会详细介绍）。 如果启用了统计信息收集器，则可以运行以下查询（
source ）。
SELECT relname, seq_scan - idx_scan AS too_much_seq, CASE WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC;
该查询查找具有比索引扫描更多的顺序扫描（索引扫描）的表-明确表明索引会有所帮助。 这不会告诉您要在哪个列上创建索引，因此将需要更多的工作。 但是，了解哪些表需要它们是一个很好的第一步。
未使用的索引
索引所有实体，对不对？ 您是否知道未使用的索引会对写入性能产生不利影响？ 原因是在创建Postgres索引时，它在写操作（INSERT / UPDATE / DELETE）之后要承担更新该索引的任务。 因此，添加索引是一种平衡操作，因为它可以加快数据的读取速度（如果创建正确），但是会减慢写操作的速度。 要查找未使用的索引，可以运行以下查询。
SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false;
关于开发环境统计信息的注意事项
依赖本地开发数据库的统计信息可能会出现问题。 理想情况下，您可以从工作计算机上获取以上统计信息，或者从还原的工作备份中生成上述统计信息。 怎么了 环境因素会改变Postgres查询优化器的行为。 两个例子：
- 当机器的内存较少时，PostgreSQL可能无法执行哈希联接，否则它可以并且会更快地执行。
- 如果表中的行数不如开发数据库中的那么多，则PostgresSQL可能更喜欢对表进行顺序扫描，而不是使用可用索引。 当表大小较小时，Seq Scan可以更快。 （注意：您可以运行
SET enable_seqscan = OFF
在会话中，以便优化程序选择使用索引，即使顺序扫描可以更快。 在处理没有大量数据的开发数据库时，这很有用）
了解执行计划
既然您发现了一些缓慢的查询，就该开始乐趣了。
说明
设置查询时，肯定需要
EXPLAIN命令。 他告诉你真正发生的事情。 要使用它，只需将
EXPLAIN添加到查询中并运行它。 PostgreSQL将向您显示其使用的执行计划。
使用EXPLAIN进行调整时，建议您始终使用
ANALYZE选项（
EXPLAIN ANALYZE ），因为它可以为您提供更准确的结果。 ANALYZE选项实际上执行该语句（而不只是评估它），然后对其进行解释。
让我们开始了解一下
EXPLAIN的输出。 这是一个例子：
结
首先要了解的是，每个带有前一个“->”（以及顶行）的缩进块都称为节点。 节点是具有相关成本和交付时间的逻辑工作单元（如果愿意，可以称为“步骤”）。 每个节点上显示的成本和时间是累积的，并将所有子节点组合在一起。 这意味着最上面的行（节点）显示整个操作员的总成本和实际时间。 这很重要，因为您可以轻松地向下钻取以确定哪些节点是瓶颈。
费用
cost=146.63..148.65
第一个数字是初始成本（获得第一条记录的成本），第二个数字是处理整个节点的成本（从头到尾的总成本）。
实际上，这是执行该语句必须满足的PostgreSQL估计成本。 该数字并不意味着完成请求将花费多长时间，尽管通常需要直接关系才能完成请求。 成本是用于评估所需工作的5个工作要素的组合：顺序采样，不一致（随机）采样，行处理，处理运算符（函数）和处理索引的记录。 成本是输入/输出和处理器负载，重要的是要知道相对较高的成本意味着PostgresSQL认为它将不得不做更多的工作。 优化器根据成本决定使用哪个执行计划。 优化器更喜欢较低的成本。
实际时间
actual time=55.009..55.012
以毫秒为单位，第一个数字是开始时间（检索第一条记录的时间），第二个数字是处理整个节点所需的时间（从开始到结束的总时间）。 容易理解，对吧？
在上面的示例中，花费55.009毫秒获得第一条记录，花费55.012毫秒完成整个节点。
了解有关执行计划的更多信息。
有一些非常好的文章可以帮助您理解EXPLAIN的结果。 我建议不要花时间在这2个精彩的资源上花一些时间来真正理解它们，而不是在这里讲述它们：
要求调整
现在，您知道哪些操作员的工作状况不佳并且可以看到您的执行计划，是时候开始调整查询以提高性能了。 在这里，您可以更改查询和/或添加索引以尝试获得更好的执行计划。 从瓶颈开始，看看是否可以进行任何更改以降低成本和/或交付周期。
数据缓存和成本注释
在进行更改和评估实施计划时，为了查看是否会有改进，重要的是要知道将来的实施可能取决于缓存数据，从而获得最佳结果。 如果您仅运行一次请求，请进行更正并再次运行，即使执行计划不太令人满意，它很有可能会运行得更快。 这是因为PostgreSQL可以缓存第一次启动时使用的数据，而可以在第二次启动时使用它。 因此，您必须至少完成3次查询并取平均结果以比较成本。
我学到的东西可以帮助改善执行计划：
- 指标
- 通过添加索引排除连续扫描（Seq扫描）（如果表大小不小）
- 使用多列索引时，请确保注意定义包含列的顺序- 更多信息
- 尝试对经常使用的数据具有高度选择性的索引。 这将使它们的使用效率更高。
- 条件在哪里
- 避免喜欢
- 避免在WHERE子句中调用函数
- 避免在（）中使用大条件
- 加盟
- 连接表时，请尝试在ON子句中使用简单的等式表达式（即a.id = b.person_id）。 这使您可以使用更有效的联接方法（即哈希联接，而不是嵌套循环联接）
- 尽可能将子查询转换为JOIN语句，因为这通常使优化器了解目标并可能选择最佳计划。
- 正确使用COMPOUNDS：您是否只是因为得到重复的结果而使用GROUP BY或DISTINCT？ 这通常表示不正确使用JOIN，并可能导致更高的成本。
- 如果执行计划使用哈希联接，则表大小估计不正确可能会非常慢。 因此，通过查看清理策略 ，确保您的表统计信息正确。
- 尽可能避免关联子查询 ； 他们会大大增加请求的成本
- 根据条件检查字符串是否存在时，请使用EXISTS ，因为它类似于短路（当发现至少一个匹配项时停止处理）
- 一般建议