我怎样才能“更好地思考”？当阅读 PostgreSQL 查询计划时？

Question

今天我花了一个多小时对一个我无法理解的查询计划感到困惑。该查询是一个UPDATE，它根本无法运行。完全陷入僵局：pg_locks表明它也没有等待任何东西。现在，我不认为自己是最好或最差的查询计划读者，但我发现这个特别困难。我想知道如何阅读这些内容？是否有 Pg ace 遵循的方法来查明错误？

我计划提出另一个关于如何解决此问题的问题，但现在我具体讨论如何阅读这些类型的计划。

                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=47680.88..169413.12 rows=1 width=77)
   Join Filter: ((co.fkey_style = v.chrome_styleid) AND (co.name = o.name))
   ->  Nested Loop  (cost=5301.58..31738.10 rows=1 width=81)
         ->  Hash Join  (cost=5301.58..29722.32 rows=229 width=40)
               Hash Cond: ((io.lot_id = iv.lot_id) AND ((io.vin)::text = (iv.vin)::text))
               ->  Seq Scan on options io  (cost=0.00..20223.32 rows=23004 width=36)
                     Filter: (name IS NULL)
               ->  Hash  (cost=4547.33..4547.33 rows=36150 width=24)
                     ->  Seq Scan on vehicles iv  (cost=0.00..4547.33 rows=36150 width=24)
                           Filter: (date_sold IS NULL)
         ->  Index Scan using options_pkey on options co  (cost=0.00..8.79 rows=1 width=49)
               Index Cond: ((co.fkey_style = iv.chrome_styleid) AND (co.code = io.code))
   ->  Hash Join  (cost=42379.30..137424.09 rows=16729 width=26)
         Hash Cond: ((v.lot_id = o.lot_id) AND ((v.vin)::text = (o.vin)::text))
         ->  Seq Scan on vehicles v  (cost=0.00..4547.33 rows=65233 width=24)
         ->  Hash  (cost=20223.32..20223.32 rows=931332 width=44)
               ->  Seq Scan on options o  (cost=0.00..20223.32 rows=931332 width=44)
(17 rows)

这个查询计划的问题 - 我相信我理解 - 可能最好由 irc 的 RhodiumToad 说出来（他在这方面肯定更擅长，所以我敢打赌他的解释会更好）： //irc.freenode.net/#postgresql：

哦，这个计划可能是灾难性的 该计划的问题在于它为每行运行一个非常昂贵的哈希连接 问题是来自其他连接的 rows=1 估计和 规划器认为可以将非常昂贵的查询放在嵌套循环的内部路径中，其中外部路径估计只返回一行。 因为，显然，根据规划者的估计，昂贵的部分只会运行一次 但这在实践中很明显会搞砸 问题在于规划者相信自己的估计 理想情况下，规划者需要知道“预计返回 1 行”和“不可能返回超过 1 行”之间的区别 但根本不清楚如何将其合并到现有代码中

他接着说：

它可以影响任何连接，但通常针对子查询的连接是最有可能的

现在，当我阅读此计划时，我注意到的第一件事是嵌套循环反连接，其成本为169,413< /code> （我会坚持上限）。此反连接分解为 Nested Loop 的结果，成本为 31,738，以及 Hash Join 的结果，成本为137,424。现在，137,424 比 31,738 大得多，所以我知道问题出在哈希连接上。

然后我继续在查询之外EXPLAIN ANALYZE 哈希连接段。它在 7 秒内执行。我确保（lot_id、vin）和（co.code 和 v.code）上有索引——有。我分别禁用了 seq_scan 和 hashjoin，发现速度提高了不到 2 秒。还不够接近，无法解释为什么一小时后没有进展。

但是，毕竟我完全错了！是的，这是查询中较慢的部分，但是因为 rows="1" 位（我认为它位于嵌套循环反连接 上）。这是规划器错误估计行数的错误（缺乏能力）？我该如何解读这一点才能得出与 RhodiumToad 相同的结论？

难道只是 rows="1" 应该触发我弄清楚这一点吗？

我确实对所有涉及的表运行了 VACUUM FULL ANALYZE，这是 Postgresql 8.4。

Answer 1

解决此类问题需要一些关于哪里可能出错的经验。但要发现查询计划中的问题，请尝试从内到外验证生成的计划，检查行数估计是否合理以及成本估计是否与花费的时间匹配。顺便提一句。两个成本估计不是下限和上限，第一个是生成第一行输出的估计成本，第二个数字是估计的总成本，请参阅 解释文档了解详细信息，还有一些 规划器文档可用。它还有助于了解不同的访问方法如何工作。作为起点，维基百科有关于嵌套循环、哈希 和合并联接。

在您的示例中，您首先要执行以下操作：

           ->  Seq Scan on options io  (cost=0.00..20223.32 rows=23004 width=36)
                 Filter: (name IS NULL)

运行 EXPLAIN ANALYZE SELECT * FROM options WHERE name IS NULL; 并查看返回的行是否与估计值匹配。 2 倍通常不是问题，您正在尝试找出数量级的差异。

然后请参阅EXPLAIN ANALYZE SELECT * FROM cars WHERE date_sold IS NULL; 返回预期的行数。

然后上一级进行哈希连接：

     ->  Hash Join  (cost=5301.58..29722.32 rows=229 width=40)
           Hash Cond: ((io.lot_id = iv.lot_id) AND ((io.vin)::text = (iv.vin)::text))

查看 EXPLAIN ANALYZE SELECT * FROM cars AS iv INNER JOIN options io ON (io.lot_id = iv.lot_id) AND ((io.vin)::text = (iv) .vin)::text) WHERE iv.date_sold IS NULL AND io.name IS NULL; 结果为 229 行。

再上一层添加INNER JOIN options co ON (co.fkey_style = iv.chrome_styleid) AND (co.code = io.code)并且预计仅返回一行。这可能就是问题所在，因为如果实际行数从 1 到 100，则遍历包含的嵌套循环的内部循环的总成本估计会减少 100 倍。

规划者所犯的根本错误可能是它期望加入 co 的两个谓词彼此独立并成倍增加它们的选择性。但实际上它们可能高度相关，并且选择性更接近 MIN(s1, s2) 而不是 s1*s2。

Answer 2

您分析过表格吗？ pg_stats 对这些表有什么说明？查询计划基于统计数据，这些必须没问题。你用什么版本？ 8.4？

可以使用统计信息、relpages 数量、行数以及 postgresql.conf 中规划器成本常量的设置来计算成本。

work_mem 也参与其中，它可能太低并迫使规划器进行 seqscan，从而降低性能......