PostgreSQL升级到12+将哈希连接更改为慢速嵌套循环

问题描述

我们正在尝试从版本9系列升级，并有一个打破交易的慢查询，它在10和11中运行正常，但在12和13中运行速度慢许多倍。我测试了11和12系列中的次要版本，次要版本不会影响它。

问题在于规划器选择嵌套循环联接，而不是它应该使用的哈希联接。

v11哈希联接：

->  Nested Loop Left Join  (cost=276056.74..285056.52 rows=1714 width=230) (actual time=13519.865..15864.542 rows=57 loops=1)
      Join Filter: (placement_type.placement_type_id = job_order.placement_type_id)
      Rows Removed by Join Filter: 57
      ->  Nested Loop Left Join  (cost=276056.74..284967.78 rows=1714 width=224) (actual time=13519.837..15864.202 rows=57 loops=1)
            ->  Hash Join  (cost=276056.32..284215.53 rows=1714 width=217) (actual time=13519.803..15863.465 rows=57 loops=1)
                  Hash Cond: (ori.order_id = job_order_1.order_id)
                  ->  HashAggregate  (cost=246953.68..250381.92 rows=342824 width=487) (actual time=13397.503..15017.114 rows=1053462 loops=1)

V12嵌套循环：

->  Nested Loop Left Join  (cost=304636.78..316645.32 rows=1716 width=230) (actual time=17799.135..152297.231 rows=57 loops=1)
      Join Filter: (placement_type.placement_type_id = job_order.placement_type_id)
      Rows Removed by Join Filter: 57
      ->  Nested Loop Left Join  (cost=304636.78..316556.50 rows=1716 width=224) (actual time=17799.111..152296.749 rows=57 loops=1)
            ->  Nested Loop  (cost=304636.37..315803.37 rows=1716 width=217) (actual time=17799.075..152295.098 rows=57 loops=1)
                  Join Filter: (job_order_1.order_id = ori.order_id)
                  Rows Removed by Join Filter: 60047277

在我们的测试环境中，升级过程是在测试此查询之前使用pg_Upgrade和完整分析完成的。

那么12中发生了什么变化？

推荐答案

感谢PG邮件列表上的人，他们应该为这个答案而受到赞扬。

在PostgreSQL 12之前，使用CTE会造成优化障碍。&在某些情况下，幸运的是，这会为查询带来良好的性能，否则规划者无法成功估计。

添加MATERIALIZED关键字(WITH ... AS MATERIALIZED (...))会强制执行旧的行为。因此，这是解决性能问题的一种方法。另一种方法是优化给规划者带来麻烦的SQL，当规划者决定重新安排CTE的处理时，这些麻烦可能会浮出水面。

这篇关于PostgreSQL升级到12+将哈希连接更改为慢速嵌套循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！