在PostgreSQL中强制执行连接顺序

在布拉格举行的pgconfeu23是一个非常出色的活动，在这次活动之后，我决定以博文的形式与大家分享我所介绍的一些情况，或许能对其中的一些话题有所启发。其中一个想法是PostgreSQL处理连接以及连接顺序的方式。从内部看，PostgreSQL 在优化查询方面做得很好，但它到底是如何工作的呢？

让我们先创建一些表:

plan=# SELECT   'CREATE TABLE x' || id || ' (id int)'
 FROM      generate_series(1, 5) AS id;
         ?column?         
--------------------------
 CREATE TABLE x1 (id int)
 CREATE TABLE x2 (id int)
 CREATE TABLE x3 (id int)
 CREATE TABLE x4 (id int)
 CREATE TABLE x5 (id int)
(5 rows)


复制

在PostgreSQL中，我们可以很容易地创建SQL。psql的美妙之处在于，我们可以简单地运行\gexec将之前的输出作为新的输入使用。

plan=# \gexec
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
复制

我们有5个表可以作为示例数据结构。

在PostgreSQL中连接表

以下查询展示了使用我们刚刚创建的表进行简单连接的示例：

plan=# explain (timing, analyze)  SELECT *
           FROM    x1 JOIN x2 ON  (x1.id = x2.id)
                      JOIN x3 ON  (x2.id = x3.id)
                      JOIN x4 ON  (x3.id = x4.id)
                      JOIN x5 ON  (x4.id = x5.id);
…
Planning Time: 0.297 ms
Execution Time: 0.046 ms
复制

在这里需要重点观察的是计划时间。PostgreSQL需要0.297毫秒来找到运行查询的最佳执行计划（执行策略）。引发的问题是：规划器在何处需要时间来计划查询？事实是：即使如上面展示的使用显式连接，PostgreSQL也会隐式连接这些表，并决定最佳连接顺序。这在现实生活中意味着什么？我们考虑一个连接“a join b join c”：即使我们编写了一个SQL命令说将“a连接到b”，优化器可能仍然决定投票支持“c join a join b”，因为它能保证得到相同的结果。为什么会这样？因为它可能大大提高查询的效率。让优化器决定最佳连接顺序是一种重要的内部优化。

然而，我们必须关注执行计划时间-尤其是如果涉及许多表（10个以上的表）。

在SQL中控制连接行为

如果执行计划时间是一个问题，我们可以强制PostgreSQL使用我们期望的连接顺序。控制这种行为的变量是join_collapse_limit。这意味着什么？基本上它控制了计划中的隐式显式连接的数量。换句话说：PostgreSQL可以优化多少个显式连接。

如果我们将此变量设置为1，这意味着我们强制PostgreSQL使用我们选择的连接顺序：

plan=# SET join_collapse_limit TO 1;
SET
plan=# explain (timing, analyze)  SELECT *
           FROM    x1 JOIN x2 ON  (x1.id = x2.id)
                      JOIN x3 ON  (x2.id = x3.id)
                      JOIN x4 ON  (x3.id = x4.id)
                      JOIN x5 ON  (x4.id = x5.id);
…
Planning Time: 0.069 ms
Execution Time: 0.046 ms
复制

这里真正值得注意的是执行计划速度的显著提升。我们可以看到惊人的4倍加速。

然而，我需要提醒大家：优化器试图重新构造连接肯定是有原因的。如果查询成本比我们在上述示例中看到的更高，那么投入更多时间来创建计划就很有意义。换句话说：除非最终用户完全了解发生了什么，否则改变这个变量可能会适得其反。因此，我们建议在更改此设置之前使用真实数据和真实工作负载测试您的查询和整个设置。一般来说，仅为单个查询更改变量并将postgresql.conf中的默认值保持不变，对于所有其他操作可能是有益的。

点击文章底部“阅读原文”查看原文内容！