postgresql - 读取 PostgreSQL 查询计划时如何获得 "think better"？

Question

今天我花了一个多小时来思考一个我无法理解的查询计划。查询是一个 UPDATE，它根本不会运行。完全陷入僵局:pg_locks 表明它也没有等待任何事情。现在，我不认为自己是最好或最差的查询计划阅读器，但我发现这本书特别难。我想知道如何阅读这些内容？Pg aces 是否遵循一种方法来查明错误？

我打算再问一个关于如何解决这个问题的问题，但现在我要专门谈谈如何阅读这些类型的计划。

                                         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)

这个查询计划的问题 - 我相信我理解 - 最好由 RhodiumToad 说(他在这方面肯定做得更好，所以我敢打赌他的解释会更好)irc://irc.freenode.net/#postgresql:

oh, that plan is potentially disastrous the problem with that plan is that it's running a hugely expensive hashjoin for each row the problem is the rows=1 estimate from the other join and the planner thinks it's ok to put a hugely expensive query in the inner path of a nestloop where the outer path is estimated to return only one row. since, obviously, by the planner's estimate the expensive part will only be run once but this has an obvious tendency to really mess up in practice the problem is that the planner believes its own estimates ideally, the planner needs to know the difference between "estimated to return 1 row" and "not possible to return more than 1 row" but it's not at all clear how to incorporate that into the existing code

他接着说:

it can affect any join, but usually joins against subqueries are the most likely

现在，当我阅读此计划时，我首先注意到的是 Nested Loop Anti Join，它的成本为 169,413(我会坚持使用上限) .此 Anti-Join 分解为成本为 31,738 的 Nested Loop 的结果，以及成本为 Hash Join 的结果137,424。现在，137,424 比 31,738 大很多，所以我知道问题出在 Hash Join 上。

然后我继续EXPLAIN ANALYZE查询之外的哈希连接段。它在 7 秒内执行。我确保在 (lot_id, vin) 和 (co.code, and v.code) 上有索引——有。我分别禁用了 seq_scan 和 hashjoin 并注意到速度增加了不到 2 秒。还不足以解释为什么它在一小时后没有进展。

但是，毕竟我完全错了!是的，它是查询中较慢的部分，但因为 rows="1" 位(我假设它在 Nested Loop Anti Join 上)。这是规划器错误估计行数的错误(缺乏能力)？我应该如何解读这一点以得出与 RhodiumToad 相同的结论？

仅仅是 rows="1" 就应该触发我弄清楚这一点吗？

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

Answer 1

要看清此类问题，需要了解哪些地方可能出错。但是要找到查询计划中的问题，请尝试从内到外验证生成的计划，检查行数估计是否合理以及成本估计是否与花费的时间匹配。顺便提一句。这两个成本估算不是下限和上限，第一个是生产第一行输出的估算成本，第二个数字是估算的总成本，请参阅 explain documentation有关详细信息，还有一些 planner documentation可用的。它还有助于了解不同的访问方法是如何工作的。作为起点，维基百科有关于 nested loop 的信息, hash和 merge joins .

在您的示例中，您将从:

           ->  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 vehicles 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 vehicles 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。