【译】PostgreSQL 中的 JOIN 策略及其性能

发表于 分类于 本文字数： 3k 阅读时长 ≈ 3 分钟

在 PostgreSQL 数据库中有三种 JOIN（连接）策略，它们的工作方式截然不同。如果 PostgreSQL 选择了错误的策略，查询性能会受到很大的影响。这篇文章解释了这些连接策略，如何通过索引来支持它们，它们存在什么问题，以及如何调整连接从而获得更好的性能。

术语

Relation（关系表）

一个连接将组合来自两个关系表的数据，这些关系表可以是一个基表，也可以是任何计划节点的结果。例如，类似下面的连接：

1
2
3
4
SELECT ...
FROM a
   JOIN (b LEFT JOIN c ON ...)
      ON ...

基表 a 将和 b 与 c 的连接结果进行连接。

Inner 和 outer 关系表

任何连接的执行计划都类似于下面的样子：

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN (COSTS OFF)
SELECT * FROM a JOIN b USING (id);

         QUERY PLAN
----------------------------
 Hash Join
   Hash Cond: (a.id = b.id)
   ->  Seq Scan on a
   ->  Hash
         ->  Seq Scan on b
(5 rows)

我们把连接关系的上层（在这种情况下是对 a 的顺序扫描）称为连接的外层关系，我们把下层关系（从 b 计算的散列）称为内层关系。

连接条件以及连接键

两个关系的笛卡尔乘积或交叉连接是指把一个关系的每一条记录与另一个关系的每一条记录结合起来。连接条件是一个过滤器，它排除了这些组合中的一些。有几种写连接条件的方法，但都可以转化为：

1
a <join type> JOIN b ON <join condition>

如果连接条件的形式为：

1
a.col1 <operator> b.col2 [AND ...]

那么 a.clo1 和 b.col2 被称为连接键。

请注意，对于内连接来说，连接条件和 WHERE 条件之间没有区别，但这对于外连接来说并不成立。

嵌套循环连接策略

这是最简单和最通用的连接策略。PostgreSQL 按顺序扫描外部关系，并为每个结果行扫描内部关系以查找匹配行。

有助于嵌套循环连接的索引

由于按顺序扫描外部关系，因此外部关系上的任何索引都无济于事。但是内部关系的连接键上的索引可以大大加快嵌套循环连接的速度。

嵌套循环连接策略的用例

如果外部关系很小，嵌套循环连接就特别有效，因为这样内部循环就不会被频繁执行。这是在具有规范化数据模型的 OLTP 工作负载中使用的典型的连接策略，它的效率很高。如果外层关系很大，嵌套的循环连接通常是非常低效的，即使它们被内部关系的索引所支持。

除此之外，如果没有连接条件使用 = 操作符，它是唯一可以使用的连接策略。因此，在没有其他策略可以使用的情况下，它也可以作为一种后备策略。

Hash 连接策略

首先，PostgreSQL 按顺序扫描内部关系并建立一个哈希表，其中哈希键由所有使用 = 操作符的连接键组成。然后，它按顺序扫描外部关系，并为找到的每一条记录探测哈希，以找到匹配的连接键。

这有点类似于一个嵌套的循环连接。建立哈希表是一个额外的启动工作，但是探测哈希表要比扫描内部关系快得多。

有助于 Hash 连接的索引

由于我们按顺序扫描两个关系，因此没有索引对哈希连接有帮助。

Hash 连接策略的用例

如果涉及的关系都不小，但较小的表的哈希表适合在 work_mem 中，那么哈希连接是最好的。这是因为，否则 PostgreSQL 会分批建立哈希表，并将其存储在临时磁盘文件中，这将损害性能。在这种情况下，优化器通常会选择一个不同的连接策略，比如合并(Merge)连接。

只有当连接条件中的运算符是 = 时，在哈希表中查找数值才会起作用，所以你至少需要一个带有该运算符的连接条件。

合并连接策略

在一个合并连接中，PostgreSQL 使用 = 运算符来选择所有的连接条件。然后它通过连接键对两个表进行排序（这意味着数据类型必须是可排序的）。然后在两个排序的列表中进行迭代，找到匹配的条目。

有助于合并连接的索引

在排序键上的索引可以加速排序，所以在两个关系的连接键上的索引可以加速合并连接的速度。然而，显式排序通常更便宜，除非可以使用仅索引扫描。

合并连接策略的用例

如果涉及的关系对于适合 work_mem 的散列来说都太大了，优化器通常会选择合并连接。所以这是连接真正大表的最佳策略。

像散列连接一样，只有当至少有一个带有 = 操作符的连接条件时，合并连接才是可行的。

PostgreSQL 连接策略汇总表

嵌套循环连接 Hash 连接 合并连接
算法 对于每个外部关系行，扫描内部关系 从内部关系构建哈希，扫描外部关系，探测哈希 对关系进行排序并合并行
有帮助的索引 内部关系的连接键上的索引 无 两个关系的连接键上的索引
适合的条件 外部关系很小 Hash 适合 work_mem 两个表都很大

对查询性能的影响

选择错误的连接策略会导致性能不佳：

  • 如果优化器低估了行数，它可能会错误地选择嵌套循环连接。然后它会比预期的更频繁地扫描内部关系，从而导致性能不佳。
  • 如果优化器高估了行数，它可能会错误地选择散列或合并连接。然后它必须完全扫描这两个关系，这可能比使用内部关系上的索引的嵌套循环连接更糟糕。

在这两种情况下，错误的行计数估计都是问题的原因。因此，虽然连接可能是我们花费大部分执行时间的地方，但原因是之前发生的错误估计。

如何让 PostgreSQL 选择正确的连接策略

找出最好的连接策略是什么（也许 PostgreSQL 无论如何都在做正确的事情）。您可以使用 SET 命令临时禁用不同的连接策略，该命令会更改当前数据库会话中的参数：

1
2
3
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_nestloop = off;

请注意，您不能真正禁用嵌套循环连接，只能阻止 PostgreSQL 使用它们。如果没有带有 = 运算符的连接条件，则嵌套循环连接是唯一的方法。

调优查询通常不是一项简单直接的任务。但是，这里有一些指导方针和想法：

  • 如果坏的连接策略是由于错误的估计而选择的，那么试着改善这个估计。对表进行分析，也许增加 default_statistics_target，看看是否会有不同。试着用更简单的 WHERE 条件重写查询，使优化器的任务更容易。
  • 尝试增加 work_mem 并查看是否可以获得更便宜的哈希连接。
  • 配置告诉 PostgreSQL 你的硬件和资源的参数：random_page_cost、effective_cache_size 和 effective_io_concurrency。这将允许它正确定价索引扫描。
  • 您可以使用仅索引扫描加速嵌套循环和合并连接。为此，您必须将所有必需的列添加到索引中（最好使用 INCLUDE 子句），并确保经常清理表。

总结

了解连接策略对于想要了解执行计划和调整查询的任何人来说都是至关重要的。查询调优的艺术无法在一篇文章中传达，但我希望我可以在这里收集一些相关信息。

如果您想阅读有关使用联接调整查询的更多信息，请阅读我们有关该主题的其他一些文章，例如连接 100 万个表或加速 GROUP BY 和连接。

译者著

笑林广记 - 贪官

有农夫种茄不活，求计于老圃。
圃曰：“此不难，每茄树下埋钱一文即活。”
问其何故，答曰：“有钱者生，无钱者死。”