十二月 15, 2023
摘要：在本教程中，您将学习如何在 PostgreSQL 中调整优化器配置参数。
多年来，PostgreSQL 的规划器有了很大的改进。尽管如此，有些场景可能性能会变差，用户必须指示规划器做正确的事情。
为了修改计划，PostgreSQL 提供了几个运行时变量，这些变量将对计划产生重大影响。这个想法是让最终用户有机会，使计划中的某些类型的节点比其他节点更耗时。这在实践中意味着什么？这是一个简单的计划：
test=# explain SELECT *
FROM generate_series(1, 100) AS a,
generate_series(1, 100) AS b
WHERE a = b;
QUERY PLAN
------------------------------------------------------------------------------------
Hash Join (cost=2.25..4.63 rows=100 width=8)
Hash Cond: (a.a = b.b)
-> Function Scan on generate_series a (cost=0.00..1.00 rows=100 width=4)
-> Hash (cost=1.00..1.00 rows=100 width=4)
-> Function Scan on generate_series b (cost=0.00..1.00 rows=100 width=4)
(5 rows)
在这里，PostgreSQL 将会扫描函数结果，并执行哈希连接。让我们在 PostgreSQL 11 或更早版本中运行相同的查询，并看看执行计划：
QUERY PLAN
------------------------------------------------------------------------------------
Merge Join (cost=119.66..199.66 rows=5000 width=8)
Merge Cond: (a.a = b.b)
-> Sort (cost=59.83..62.33 rows=1000 width=4)
Sort Key: a.a
-> Function Scan on generate_series a (cost=0.00..10.00 rows=1000 width=4)
-> Sort (cost=59.83..62.33 rows=1000 width=4)
Sort Key: b.b
-> Function Scan on generate_series b (cost=0.00..10.00 rows=1000 width=4)
(8 rows)
你能看到这两个计划之间的区别吗？在 PostgreSQL 12 中，集合返回函数的估计值已经是正确的。在旧版本中，优化器仍然估计一个集合返回函数将始终返回 1000 行。在 PostgreSQL 中，有一些优化器支持函数可以帮助估计结果集。因此，PostgreSQL 12 及更高版本中的计划远远优于旧计划。
在新计划中，我们可以看到的是执行哈希联接，这当然是最有效的做事方式。但是，如果我们比优化器更聪明呢？幸运的是，PostgreSQL 有办法调整优化器行为。您可以在连接中设置参数，来更改默认的估算成本。其工作原理如下：
test=# SET enable_hashjoin TO off;
SET
test=# explain SELECT *
FROM generate_series(1, 100) AS a,
generate_series(1, 100) AS b
WHERE a = b;
QUERY PLAN
------------------------------------------------------------------------------------
Merge Join (cost=8.65..10.65 rows=100 width=8)
Merge Cond: (a.a = b.b)
-> Sort (cost=4.32..4.57 rows=100 width=4)
Sort Key: a.a
-> Function Scan on generate_series a (cost=0.00..1.00 rows=100 width=4)
-> Sort (cost=4.32..4.57 rows=100 width=4)
Sort Key: b.b
-> Function Scan on generate_series b (cost=0.00..1.00 rows=100 width=4)
(8 rows)
PostgreSQL 假设哈希连接函数是很差的，并且使它们的代价变得非常昂贵。因此，它会退回到合并连接。然而，我们也可以关闭合并连接：
test=# explain SELECT *
FROM generate_series(1, 100) AS a,
generate_series(1, 100) AS b
WHERE a = b;
QUERY PLAN
----------------------------------------------------------------------------
Nested Loop (cost=0.01..226.00 rows=100 width=8)
Join Filter: (a.a = b.b)
-> Function Scan on generate_series a (cost=0.00..1.00 rows=100 width=4)
-> Function Scan on generate_series b (cost=0.00..1.00 rows=100 width=4)
(4 rows)
PostgreSQL 正在慢慢用完可供选择的计划。以下示例显示了，如果我们也关闭嵌套循环会发生什么：
test=# SET enable_nestloop TO off;
SET
test=# explain SELECT *
FROM generate_series(1, 100) AS a,
generate_series(1, 100) AS b
WHERE a = b;
QUERY PLAN
-----------------------------------------------------------------------------
Nested Loop (cost=10000000000.00..10000000226.00 rows=100 width=8)
Join Filter: (a.a = b.b)
-> Function Scan on generate_series a (cost=0.00..1.00 rows=100 width=4)
-> Function Scan on generate_series b (cost=0.00..1.00 rows=100 width=4)
JIT:
Functions: 10
Options: Inlining true, Optimization true, Expressions true, Deforming true
(7 rows)
重要的是，关闭并不意味着真正关闭 — 它只是意味着代价非常昂贵。如果 PostgreSQL 没有更划算的选择，它将回退到我们关闭的选择。否则，将不再有任何方法可以执行 SQL。
哪些设置会影响规划器？下面这些开关都是可用的：
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_parallel_append = on
#enable_seqscan = on
#enable_sort = on
#enable_incrementalsort = on
#enable_tidscan = on
#enable_partitionwise_join = off
#enable_partitionwise_aggregate = off
#enable_parallel_hash = on
#enable_partition_pruning = on
这些设置绝对是有益的，请务必理解调整它们时应谨慎处理。它们应该只用于加速单个查询，而不是全局关闭。关闭选项可能会很快对您不利并破坏性能。因此，在更改这些参数之前，请务必再三考虑是否值得这么做。