Postgresql join_collapse_limit 和查询计划的时间

Question

我刚刚发现 join_collapse_limit 一直在阻止 PostgreSQL 规划器找到更好的连接顺序。在我的例子中，将限制增加到 10(从默认值 8)允许规划器将搜索时间从约 30 秒缩短到约 1 毫秒，这是更容易接受的。

文档表明，将此设置“过高”可能会导致规划时间过长，但甚至没有提供有关各种值的规划步骤可能需要多长时间的“经验法则”。我知道一般问题在时间上是指数级的，但我找不到确定实际计划时间的方法，除非它只是运行 ANALYZE SELECT ... 所需的时间。如果是这样的话，我相信默认值 8 对于现代计算机来说已经很低了，因为我无法检测到 8 和 10 之间的规划速度没有差异。

问题:

1) 如何衡量计划时间？

2) 大约，join_collapse_limit 可以达到多高，并且仍然期望计划花费的时间少于几百毫秒？

Answer 1

1) How can one measure planning time?

新的 9.4 版 PostgreSQL(在撰写本文时尚未发布)将在 EXPLAIN 和 EXPLAIN ANALYZE 中增加计划时间，因此您将能够使用这些。

对于旧版本，您的假设是正确的，确定计划时间的更好方法是执行一个简单的EXPLAIN(没有ANALYZE)并检查它花费的时间，在 psql 中，您可以通过启用 \timing 来实现(我通常在 ~/.psqlrc 中这样做)。

2) Approximately, how high can join_collapse_limit be and still expect planning to take less than a couple hundred milliseconds?

The PostgreSQL hackers team already discussed about raising it to bigger values 。但看起来他们无法保证它适用于所有情况。

问题在于，为 N 表寻找最佳连接顺序的计划采用了 O(N!)(阶乘)方法。因此，提高的数字非常高，您可以通过以下查询简单地看到:

$ SELECT i, (i)! AS num_comparisons FROM generate_series(8, 20) i;
 i  |   num_comparisons   
----+---------------------
  8 |               40320
  9 |              362880
 10 |             3628800
 11 |            39916800
 12 |           479001600
 13 |          6227020800
 14 |         87178291200
 15 |       1307674368000
 16 |      20922789888000
 17 |     355687428096000
 18 |    6402373705728000
 19 |  121645100408832000
 20 | 2432902008176640000
(13 rows)

如您所见，在默认值 8 下，我们最多进行大约 40K 次比较，您建议的 10 使它达到 3M，这对于现代计算机来说仍然不是很多，但下一个值开始变得太大，它只是增加得太快了，20 太疯狂了(21!甚至不适合 64 位整数)。

当然，有时你可以将它设置为更大的值，比如 16，这样(理论上)可以进行大约 20 万亿次比较，并且仍然有很好的规划时间，这是因为 PostgreSQL 在规划时切掉了一些路径并且不不需要总是检查所有订单，但假设它总是如此并将如此高的值设置为默认值，对我来说似乎不是一个好方法。将来可能会出现一些意想不到的查询，它会检查所有订单，然后您只有一个查询会导致服务器宕机。

根据我的经验，我假设 10 作为任何安装在好的服务器上的默认值，其中一些我什至使用 12。如果你愿意，我建议你将它设置为 10，有时，尝试设置它更高(我不会超过 12)并继续(密切)监控以查看它的行为。