图表，报告和分析-所有这些都以某种方式存在于任何甚至很小的企业的后台。 当它挤在Excel / Numbers / Libre中的常规表中，但数据仍然不是很大时，通常使用关系数据库（例如PostgreSQL，MySQL或MariaDB）构建满足公司内部需求的传统解决方案。
这些数据库是免费的，由于有了SQL，它们可以方便地与系统中的其他组件集成，因此很受欢迎，大多数开发人员和分析人员都可以使用它们。 他们可以消化足够大的负载（流量和流量）以从容应对，直到公司负担得起用于分析和报告的更复杂（更昂贵）的解决方案为止。
起始位置
但是，即使在经过反复研究的技术中，总会有各种细微差别会突然加重工程师的后顾之忧。 除了可靠性之外，数据库最常提到的问题是数据库的性能。 显然，随着数据量的增加，DB响应率会降低，但是如果可以预料地发生这种情况并且与负载的增加保持一致，那么这还不错。 您总是可以预先看到数据库何时开始引起关注，并计划升级或过渡到根本不同的数据库。 如果数据库性能意外降级，那就更糟了。
提高数据库性能的话题已经有很长的历史了，并且涉及面很广，在本文中，我只希望集中讨论一个方向。 即，在评估PostgreSQL数据库中查询计划的有效性时，以及随着时间的推移更改此效率以使数据库调度程序的行为更可预测。
尽管将要讨论的许多内容都适用于该数据库的所有最新版本，但下面的示例表示版本11.2，目前是后者。
在我们深入研究细节之前，有必要先讨论一下并说出关系数据库中性能问题的根源。 当数据库“变慢”时，到底在忙什么呢？ 内存不足（大量磁盘或网络访问），处理器不足，这些都是采用明确解决方案时显而易见的问题，但是还有哪些因素会影响查询的执行速度呢？
刷新记忆
为了使数据库能够响应SQL查询，它需要构建一个查询计划（在哪些表和列中查看需要哪些索引，从中选择什么，与之进行比较，需要多少内存等等）。 该计划以树的形式形成，其节点只是一些典型的操作，具有不同的计算复杂性。 例如，以下是其中一些（N是执行操作的行数）：
| 运作方式 | 做什么了 | 费用 |
|---|
| SELECT ... WHERE ...数据获取操作 |
| 序列扫描 | 我们从表中加载每一行并检查条件。 | O（N） |
索引扫描
（b树索引） | 数据直接在索引中，因此我们根据条件搜索索引的必要元素，然后从那里获取数据。 | O（对数（N）），在排序的树中搜索元素。 |
索引扫描
（哈希索引） | 数据直接在索引中，因此我们根据条件搜索索引的必要元素，然后从那里获取数据。 | O（1），在哈希表中搜索项目，不包括创建哈希的开销 |
| 位图堆扫描 | 我们通过索引选择必要行的编号，然后仅加载必要行并对其进行其他检查。 | 索引扫描+序列扫描（M）
其中M是在索引扫描后找到的行数。 假定M << N，即 index比Seq Scan更有用。 |
| 联接操作（从多个表联接，选择） |
| 嵌套循环 | 对于左表中的每一行，请在右表中查找合适的行。 | O（N 2 ）。
但是，如果其中一个表比另一个（字典）小得多，并且实际上不随时间增长，则实际成本可以降低到O（N）。 |
| 哈希联接 | 对于左右表中的每一行，我们考虑哈希值，这减少了可能的连接选项的搜索次数。 | O（N），但在哈希函数效率非常低或连接的大量相同字段的情况下，可能会有O（N 2 ） |
| 合并加入 | 根据条件，我们对左右表格进行排序，然后将两个排序后的列表合并 | O（N *对数（N））
排序费用+查看清单。 |
| 聚合操作（GROUP BY，DISTINCT） |
| 集团汇总 | 我们根据聚合条件对表进行排序，然后在排序后的列表中对相邻行进行分组。 | O（N *对数（N）） |
| 杂凑集 | 我们考虑哈希用于每一行的聚集条件。 对于具有相同散列的行，我们执行聚合。 | O（N） |
如您所见，查询的成本很大程度上取决于表中数据的位置以及此顺序与所使用的哈希操作相对应的方式。 嵌套循环，尽管其成本为O（N
2 ），但当其中一个联接表退化为一或几行时，它比哈希联接或合并联接更有利可图。
除了CPU资源外，成本还包括内存使用情况。 两者都是有限的资源，因此查询计划者必须找到一个折衷方案。 如果在数学上通过哈希联接来连接两个表更有利可图，但是在内存中根本没有空间容纳如此大的哈希表，例如，数据库可能被迫使用合并联接。 “慢速”嵌套循环通常不需要额外的内存，可以在启动后立即产生结果。
这些操作的相对成本在图中更清楚地显示。 这些不是绝对数字，只是不同操作的近似比率。
嵌套循环图在下面“开始”，因为 它不需要额外的计算或内存分配或复制中间数据，但它的成本为O（N
2 ）。 合并联接和哈希联接的初始成本较高，但是，在经过N个值后，它们开始及时击败嵌套循环。 计划程序尝试选择成本最低的计划，并且在上表中坚持使用不同N（绿色虚线箭头）的不同操作。 随着行数达到N1，使用嵌套循环更有利可图，从N1到N2，使用合并联接更有利可图，然后在N2之后，对哈希联接更有利可图，但是哈希联接需要内存来创建哈希表。 并且当到达N3时，此内存不足，导致强制使用合并联接。
选择计划时，调度程序会使用数据库中某些“原子”操作的一组相对成本来估算计划中每个操作的成本。 例如，计算，比较，将页面加载到内存等。 这是默认配置中的一些参数的列表，但其中没有很多：
| 相对成本常数 | 预设值 |
|---|
| seq_page_cost | 1.0 |
| random_page_cost | 4.0 |
| cpu_tuple_cost | 0.01 |
| cpu_index_tuple_cost | 0.005 |
| cpu_operator_cost | 0.0025 |
| parallel_tuple_cost | 0.1 |
| parallel_setup_cost | 1000.0 |
的确，仅这些常量就很少了，您仍然需要知道非常“ N”的含义，也就是说，在每个这样的操作中，必须处理之前结果的多少行。 此处的上限很明显-数据库“知道”任何表中有多少数据，并且始终可以“最大”地进行计算。 例如，如果您有两个表，每个表100行，那么将它们连接起来可以在输出中产生0到10,000行。 因此，下一个输入操作最多可以有10,000行。
但是，如果您至少了解表中数据的性质，则可以更准确地预测此行数。 例如，对于来自上面示例的100个行的两个表，如果您事先知道联接将不会产生10,000行，而是产生100行，则可以大大降低下一操作的估计成本。 在这种情况下，该计划可能比其他计划更有效。
开箱即用的优化
为了使调度程序能够更准确地预测中间结果的大小，PostgreSQL使用了对表的统计信息收集，这些统计信息存储在pg_statistic或更具可读性的版本pg_stats中。 真空开始时会自动更新，或使用ANALYZE命令明确更新。 该表存储有关表中哪些数据和类型的性质的各种信息。 特别是值的直方图，空白字段的百分比和其他信息。 规划人员使用所有这些信息可以更准确地预测计划树中每个工序的数据量，从而更准确地计算工序成本和整个计划。
以查询为例：
SELECT t1.important_value FROM t1 WHERE t1.a > 100
假设“ t1.a”列中的值的直方图显示在表的大约1％的行中发现了大于100的值。 然后我们可以预测，这样的样本将返回表“ t1”中所有行的约百分之一。
该数据库使您有机会通过EXPLAIN命令查看计划的预计成本，以及使用EXPLAIN ANALYZE的实际时间。
似乎有了自动统计信息，现在一切都会好起来的，但是可能会有困难。
Citus Data上有一篇
很好的文章 ，其中举例说明了自动统计数据效率低下以及使用CREATE STATISTICS（PG 10.0提供）收集其他统计
数据的例子。
因此，对于调度程序，在计算成本时有两个错误来源：
- 默认情况下，基本操作的相对成本（seq_page_cost，cpu_operator_cost等）可能与实际情况有很大不同（cpu成本0.01，srq页面加载成本-随机页面加载为1或4）。 与100次比较将等于1页加载的事实相去甚远。
- 预测中间操作中的行数时出错。 在这种情况下，实际的运营成本可能与预测有很大不同。
在复杂的查询中，草拟和预测所有可能的计划本身会花费很多时间。 如果数据库仅计划一分钟的请求，那么在1秒钟内返回数据有什么用？ PostgreSQL有一个针对这种情况的Geqo优化器；它是一个调度程序，它不会构建所有可能的计划，而是从一些随机的计划开始，并完成最佳计划，从而预测降低成本的方法。 尽管这会加快搜索至少某种或多或少的最佳计划的速度，但它们也不会提高预测的准确性。
突如其来的计划-竞争对手
如果一切顺利，您的请求将尽快完成。 随着数据量的增加，数据库中查询的执行速度逐渐提高，经过一段时间的观察，您可以粗略地预测何时需要增加内存或CPU核心数量或扩展集群等。
但是，我们必须考虑到这样一个事实，即最优计划的竞争对手的执行成本很高，这是我们所没有看到的。 而且，如果数据库突然将查询计划更改为另一个计划，这将令人惊讶。 如果数据库跳到更有效的计划，那就很好了。 如果没有呢？ 让我们看一下图片。 这是两个计划（红色和绿色）的预计实施成本和实时性：
在这里，一个计划以绿色显示，而最接近的“竞争对手”以红色显示。 虚线显示了预计成本的图表，实线是实时的。 灰色虚线箭头显示了计划者的选择。
假设某个星期五晚上某个中间操作的预测行数达到N1，并且“红色”预测开始胜过“绿色”预测。 调度程序开始使用它。 实际查询执行时间立即跳跃（从绿色实线切换为红色实线），也就是说，数据库降级计划采取步骤（或可能是“隔离墙”）的形式。 实际上，这样的“墙”可以将查询执行时间增加一个数量级或更多。
值得注意的是，这种情况对于后端办公和分析而言可能比在前端更为典型，因为前端通常适用于更多同时查询，因此在数据库中使用更简单的查询，因此计划预测中的错误较小。 如果这是用于报告或分析的数据库，则查询可能会非常复杂。
如何生活？
随之而来的问题是：是否有可能以某种方式预见这种“水下”无形的计划？ 毕竟，问题不在于它们不是最佳方案，而是切换到另一个计划可能会不可预测地发生，并且根据卑鄙定律，这是最不幸的时刻。
不幸的是，您无法直接看到它们，但是可以通过更改选择它们的实际权重来查找替代计划。 这种方法的意思是将计划者认为最佳的当前计划从视线中移开，以便他最亲近的竞争对手之一成为最佳计划，因此可以通过EXPLAIN团队看到他。 定期检查此类“竞争对手”和主计划中的成本变化，您可以评估数据库不久将“跳转”到另一个计划的可能性。
除了收集有关替代计划的预测的数据之外，您还可以运行它们并评估其性能，这也可以使您了解数据库的内部“幸福感”。
让我们看看我们拥有用于此类实验的工具。
首先，您可以使用会话变量显式“禁止”特定操作。 方便地，不需要在配置中更改它们并重新加载数据库，它们的值仅在当前打开的会话中更改，并且不会影响其他会话，因此您可以直接对真实数据进行实验。 以下是它们的默认值列表。 几乎包括所有操作：
| 使用的作业 | 预设值 |
|---|
enable_bitmapscan
enable_hashagg
enable_hashjoin
enable_indexscan
enable_indexonlyscan
enable_material
enable_mergejoin
enable_nestloop
enable_parallel_append
enable_seqscan
enable_sort
enable_tidscan
enable_parallel_hash
enable_partition_pruning | 在 |
enable_partitionwise_join
enable_partitionwise_aggregate | 关 |
通过禁止或允许某些操作，我们强制调度程序选择使用同一EXPLAIN命令可以看到的其他计划。 实际上，“禁止”操作并不禁止其使用，而只是大大增加了其成本。 在PostgreSQL中，每个“禁止”操作都会自动堆积相当于100亿个常规单位的成本。 此外，在EXPLAIN中，该计划的总重量可能会过高，但在这数百亿美元的背景下，由于通常适合较小的订单，因此可以清楚地看到其余工序的重量。
以下两个操作尤其令人感兴趣：
- 哈希加入。 它的复杂度为O（N），但是由于预测的结果有误，您无法容纳在内存中，因此必须进行合并联接，其成本为O（N * log（N））。
- 嵌套循环。 它的复杂度为O（N 2 ），因此，大小预测中的误差将二次影响这种连接的速度。
例如，让我们从查询中获取一些实数，这些查询是我们在公司中进行的优化。
计划1.在所有允许的操作中，最佳计划的总成本为274962.09单位。
计划2。使用“禁止”嵌套循环，成本增加到40000534153.85。 尽管有禁令，但这400亿美元的成本却是嵌套循环使用量的4倍。 其余的534153.85-恰恰是该计划中所有其他作业成本的预测。 正如我们所看到的，它大约是最佳计划成本的2倍，也就是说，它已经足够接近它了。
计划3。使用“禁止”哈希联接，成本为383253.77。 该计划实际上是在没有使用哈希联接操作的情况下制定的，因为我们看不到数十亿美元。 但是，它的成本比最优成本高30％，而最优成本也非常接近。
实际上，查询执行时间如下：
计划1 （允许所有操作）在约9分钟内完成。
计划2 （带有“禁止”嵌套循环）在1.5秒内完成。
计划3 （带有“禁止”哈希联接）在约5分钟内完成。
正如您所看到的，原因是对嵌套循环成本的错误预测。 实际上，在将EXPLAIN与EXPLAIN ANALYZE进行比较时，会在中间操作中检测到错误的N定义错误。 嵌套循环而不是预测的单行，而是遇到了数千行，这导致查询的执行时间增加了两个数量级。
使用“禁止”散列连接节省的费用与用排序和合并连接取代散列相关，在这种情况下，散列和合并连接的工作速度比散列连接更快。 请注意，实际上，该计划2几乎比“最佳”计划1快两倍。
实际上，如果您的请求突然（在数据库升级后或仅在其本身之后）开始运行的时间比以前长得多，请首先尝试拒绝哈希联接或嵌套循环，然后看看这如何影响查询速度。 成功的情况下，您至少可以禁止一个新的非最佳计划，然后返回到以前的快速计划。
为此，您无需在数据库重新启动时更改PostgreSQL配置文件，在任何控制台中，从数据库更改打开会话所需变量的值都非常简单。 其余会话将不受影响，配置将仅针对您当前的会话进行更改。 例如，像这样：
SET enable_hashjoin='on'; SET enable_nestloop='off'; SELECT … FROM … ( )
影响计划选择的第二种方法是更改底层操作的权重。 这里没有通用的配方，但是，例如，如果您的数据库具有“预热”缓存，并且整个数据都存储在内存中，则顺序页面加载的成本可能与加载随机页面的成本没有区别。 而在默认配置中，随机费用是顺序费用的4倍。
或者，另一个示例，运行并行处理的条件成本默认为1000，而加载页面的成本为1.0。 首先一次更改仅一个参数来确定它是否影响计划的选择是有意义的。 最简单的方法是通过将参数设置为0或某个较高的值（一百万）来开始。
但是，请记住，通过提高一个请求的性能可以降低另一个请求的性能。 通常，有广阔的实验领域。 最好一次更改一次，一次更改一次。
替代治疗方案
如果不提及至少两个PostgreSQL扩展，关于调度程序的故事将是不完整的。
第一个是
SR_PLAN ，用于保存计算的计划并强制其进一步使用。 这有助于使数据库行为在计划选择方面更可预测。
第二个是
Adaptive Query Optimizer ，它从查询的实时执行中实现对调度程序的反馈，也就是说，调度程序会测量已执行查询的实际结果，并牢记这一点在将来进行调整。 因此，数据库针对特定数据和查询进行了“自我调整”。
当数据库变慢时，数据库还能做什么？
现在，我们或多或少地整理了查询计划，让我们看看在数据库本身以及使用该数据库的应用程序中，可以从中获得最大性能的还有哪些地方可以改进。
假设查询计划已经是最佳的。 如果我们排除最明显的问题（内存不足或磁盘/网络速度慢），那么仍然需要计算哈希值。 将来有可能对PostgreSQL进行改进（使用GPU甚至CPU的SSE2 / SSE3 / AVX指令），这是很大的机会，但是到目前为止，这还没有完成，并且哈希计算几乎从未使用硬件的硬件功能。 您可以在此数据库中提供一些帮助。
如果您注意到的话，默认情况下，PostgreSQL中的索引被创建为b树。 它们的用处在于它们用途广泛。 这样的索引既可以用于相等条件，也可以用于比较条件（或多或少）。 在这样的索引中找到项目是对数成本。 但是，如果您的查询仅包含一个相等条件，则索引也可以创建为哈希索引，其开销是恒定的。
此外，您仍然可以尝试修改请求，以便使用其并行执行。 要确切地了解如何重写它，最好使自己熟悉调度程序自动禁止并发的情况列表，并避免这种情况。 关于此主题
的手册简要描述了所有情况，因此在这里重复它们是没有意义的。
如果请求仍然不擅长并行处理该怎么办？ 很伤心，看看你的强大的多核心数据库，在这里你是唯一的客户，一个核心由占用100％，而所有其他核只是看它。 在这种情况下，您必须从应用程序方面帮助数据库。 由于为每个会话分配了自己的核心，因此您可以打开其中的几个并将常规查询分成多个部分，从而进行更短和更快的选择，并将它们组合成应用程序中已经存在的通用结果。 这将占用PostgreSQL数据库中的最大可用CPU资源。
总之，我想指出的是，上述诊断和优化选项只是冰山一角，但是它们非常易于使用，可以帮助直接在运营数据上快速识别问题，而不会冒险破坏配置或破坏其他应用程序的运行。
成功的查询，准确而短期的计划。