在 PostgreSQL 中强制执行连接顺序#postgresql认证

让我们首先创建一些表：

PgSQL
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)
1
2
3
4
5
6
7
8
9
10
11

在 PostgreSQL 中，我们可以轻松地使用 SQL 创建 SQL。psql 的优点在于，只需运行 gexec 即可将先前的输出用作新输入：

plan=# gexec
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
1
2
3
4
5
6

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

在 POSTGRESQL 中连接表

#PG培训#PG考试#postgresql培训#postgresql考试
以下查询显示了使用我们刚刚创建的表的简单连接：

PgSQL
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
1
2
3
4
5
6
7
8
9

这里的重要观察是什么？让我们来看看规划时间。PostgreSQL 需要 0.297 毫秒来找到运行查询的最佳执行计划（= 执行策略）。出现的问题是：规划器在哪里需要时间来规划查询？问题是：即使使用如上所示的显式连接，PostgreSQL 也会隐式连接这些表并决定最佳连接顺序。这在现实生活中意味着什么？让我们考虑一个连接“a join b join c”：即使我们编写一个 SQL 说连接“a to 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
1
2
3
4
5
6
7
8
9
10
11

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

但是，我还要提醒大家：优化器首先尝试重新构建连接是有原因的。如果查询比我们在此示例中看到的更昂贵，那么投入更多时间创建计划就很有意义了。换句话说：除非最终用户完全了解正在发生的事情，否则更改此变量可能会适得其反。因此，我们建议在更改此设置之前使用真实数据和真实工作负载测试您的查询和整个设置。通常，只更改单个查询的变量并保留 postgresql.conf 中的默认值（与所有其他操作一样）也是有益的。