文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >Postgres 11:查询计划升级后使用seq扫描

问Postgres 11:查询计划升级后使用seq扫描
EN

Database Administration用户
提问于 2020-12-29 00:59:41
回答 2查看 1.2K关注 0票数 4

情况

我们有一个托管在RDS上的数据库，它有几百个表，其中有几个相当大。

我们最近将数据库从9.5.22升级到11.8，性能显著下降。

升级之后，我们在实例上运行VACUUM ANALYZE (与./analyze_new_cluster.sh相反，因为我们无法在RDS实例上运行shell )。

这对局势没有任何帮助。我构造了另一个单独的11.8数据库实例并运行了一个VACUUM FULL ANALYZE，该数据库显示了相同的查询计划器行为，因此在VACUUM命令中包括FULL没有帮助(就像在一些这样的答案中所建议的那样)。

我们发现有一个查询显示升级前后性能发生了最剧烈的变化：

代码语言：javascript
复制
SELECT f.uuid, p.name
FROM flights f 
LEFT OUTER JOIN passengers p 
    ON f.uuid = p.flight_id 
WHERE f.uuid IN (< UUIDs >)
ORDER BY f.date_created ASC;

以前P95潜伏期在4ms以下。现在，P95是15秒。

当 the WHERE clause中的UUID数量包含5个或更多UUID时，就会出现UUID问题。

所涉表格的结构(简化)如下：

代码语言：javascript
复制
                    Table "public.flights"
 Column       | Type                     | Modifiers | Storage | Stats target 
--------------+--------------------------+-----------+---------+--------------
 uuid         | uuid                     | not null  | plain   |              
 date_created | timestamp with time zone | not null  | plain   |              

Indexes:
    "flights_pkey" PRIMARY KEY, btree (uuid)


                    Table "public.passengers"
 Column    | Type                   | Modifiers                     | Storage | Stats target
-----------+------------------------+-------------------------------+---------+-------------
 id        | bigint                 | not null default nextval(...) | plain   |             
 flight_id | uuid                   | not null                      | plain   |             
 name      | character varying(128) | not null                      | plain   |             

Indexes:
    "passengers_pkey" PRIMARY KEY, btree (id)
    "passengers_a08cee2d" btree (flight_id)
Foreign-key constraints:
    "p_flight_id_75a46b87233dc365_fk_flights_uuid" FOREIGN KEY (flight_id) REFERENCES flights(uuid) DEFERRABLE INITIALLY DEFERRED

flights表有大约1700万行。passengers表大约有26亿行。

执行计划

postgres 9.5实例( WHERE子句中有50个UUID)

代码语言：javascript
复制
 Sort  (cost=7273695.73..7273707.45 rows=4688 width=36) (actual time=0.420..0.420 rows=0 loops=1)
   Sort Key: f.date_created
   Sort Method: quicksort  Memory: 25kB
   ->  Nested Loop Left Join  (cost=1652.68..7273409.89 rows=4688 width=36) (actual time=0.408..0.408 rows=0 loops=1)
         ->  Index Scan using flights_pkey on flights f  (cost=0.56..428.86 rows=50 width=24) (actual time=0.406..0.406 rows=0 loops=1)
               Index Cond: (uuid = ANY ('{2c0adac6-79bb-48a1-a0ba-bd8f537d68de,...,a6605812-9a5b-46c4-9989-4d24d195e1c0}'::uuid[]))
         ->  Bitmap Heap Scan on passengers p  (cost=1652.12..145082.56 rows=37706 width=28) (never executed)
               Recheck Cond: (f.uuid = flight_id)
               ->  Bitmap Index Scan on passengers_a08cee2d  (cost=0.00..1642.70 rows=37706 width=0) (never executed)
                     Index Cond: (f.uuid = flight_id)
 Planning time: 0.289 ms
 Execution time: 0.479 ms
(12 rows)

postgres 11实例( WHERE子句中有50个UUID)

代码语言：javascript
复制
 Gather Merge  (cost=3149109.16..3149552.99 rows=3804 width=36) (actual time=3880.756..3882.219 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=3148109.14..3148113.89 rows=1902 width=36) (actual time=3878.194..3878.194 rows=0 loops=3)
         Sort Key: f.date_created
         Sort Method: quicksort  Memory: 25kB
         Worker 0:  Sort Method: quicksort  Memory: 25kB
         Worker 1:  Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop Left Join  (cost=745.27..3148005.54 rows=1902 width=36) (actual time=3878.170..3878.170 rows=0 loops=3)
               ->  Parallel Seq Scan on flights f  (cost=0.00..669647.32 rows=21 width=24) (actual time=3878.167..3878.168 rows=0 loops=3)
                     Filter: (uuid = ANY ('{2c0adac6-79bb-48a1-a0ba-bd8f537d68de,...,a6605812-9a5b-46c4-9989-4d24d195e1c0}'::uuid[]))
                     Rows Removed by Filter: 5631600
               ->  Bitmap Heap Scan on passengers p  (cost=745.27..117695.86 rows=32120 width=28) (never executed)
                     Recheck Cond: (f.uuid = flight_id)
                     ->  Bitmap Index Scan on passengers_a08cee2d  (cost=0.00..737.24 rows=32120 width=0) (never executed)
                           Index Cond: (f.uuid = flight_id)
 Planning Time: 0.286 ms
 Execution Time: 3882.262 ms
(18 rows)

我的最佳评估

在这两种情况下，passengers表上的扫描都不会执行。这实际上是因为我向查询提供的UUID在flights表中不存在。我只是想传递一个更大的数字，以触发如何扫描flights表的不同行为。

在postgres 9.5实例中，它使用索引条件执行索引扫描，因为它需要50行(我向查询提供的UUID数量)，并返回无行(因为它们都不存在)。

在postgres 11实例中，它希望使用过滤器对表执行顺序扫描(并行)。过滤器实质上删除顺序扫描(S)返回的所有行。

当传递给WHERE子句的UUID少于10个时，postgres 11实例将生成与postgres 9.5实例相同的索引扫描查询计划。这使我认为统计数据的差异导致了这种情况，然而，对于我所检查的数据，这些统计数据在这两种情况下都是相似的--参见下面(除非我没有提取正确的值，这是非常可能的)。

我读过很多关于“糟糕的查询”的答案，但是他们并没有解决我认为可能是大版本升级的结果。

我检查了每个数据库的default_statistics_target (都是100)和random_page_cost (都是4)。

我认识到将enable_seqscan设置为OFF不是一个永久的解决方案，但是它确实强制postgres 11实例返回与postgres 9.5实例相同的查询计划。

我通过设置max_parallel_workers_per_gather = 0进行了实验，这也产生了强制postgres 11返回避免顺序扫描的查询计划的预期效果，但我认为禁用数据库的该功能是不明智的。

更改ORDER BY值(包括将其完全从查询中删除)对查询计划没有任何影响。

代码语言：javascript
复制
-- on pg 11 instance with enable_seqscan = OFF OR max_parallel_workers_per_gather = 0

 Sort  (cost=5901559.44..5901570.85 rows=4566 width=36)
   Sort Key: f.date_created
   ->  Nested Loop Left Join  (cost=745.83..5901281.90 rows=4566 width=36)
         ->  Index Scan using flight_pkey on flight f  (cost=0.56..428.99 rows=50 width=24)
               Index Cond: (uuid = ANY ('{2c0adac6-79bb-48a1-a0ba-bd8f537d68de,...,a6605812-9a5b-46c4-9989-4d24d195e1c0}'::uuid[]))
         ->  Bitmap Heap Scan on passengers p  (cost=745.27..117695.86 rows=32120 width=28)
               Recheck Cond: (f.uuid = flight_id)
               ->  Bitmap Index Scan on passengers_a08cee2d  (cost=0.00..737.24 rows=32120 width=0)
                     Index Cond: (f.uuid = flight_id)
(9 rows)

我达到了在黑暗中刺伤的地步，并试图比较uuid列在flights表中的值。它们都显示了null_frac、avg_width、n_distinct和correlation值的相似值。

我的问题

鉴于以上所述，我缺少什么来帮助postgres查询计划器避免昂贵的顺序扫描？

所有设置和统计信息在两个实例之间似乎是相同的，只有postgres版本。

9.5实例没有任何列具有与缺省值不同的stats目标。因此，在有人建议增加这一价值之前，如果postgres 9.5实例在没有postgres 9.5实例的情况下产生一个“好的”计划，为什么还要帮助postgres 11实例呢？

有什么关于postgres 11 (平行工人吗？)这让它认为它可以比索引扫描更快地执行顺序扫描？考虑到规划师期望返回21行，这似乎不太可能，但代价是巨大的。

代码语言：javascript
复制
Parallel Seq Scan on flights f  (cost=0.00..669647.32 rows=21

谢谢。

<#>编辑：

我们的解决方案

基于反馈，我们通过设置max_parallel_workers_per_gather = 0禁用了并行查询，问题就消失了。

我们还增加了统计目标(尽管人们对此表示怀疑)，并将尝试在将来启用并行查询的方法，而不会触发同样的“坏”行为。

奖励:禁用并行查询之前和之后的查询延迟图：

postgresql
postgresql-performance
postgresql-11
EN

回答 2

Database Administration用户

回答已采纳

发布于 2020-12-30 02:13:40

您的统计数据在不同版本之间变化不大。他们在这两方面都相当差。但改变的是，糟糕的统计数据使得并行计划看起来很有吸引力，现在并行计划已经存在了。

这实际上是因为我为查询提供的UUID不存在于flights表中。我只是想传递一个更大的数字，以触发如何扫描航班表的不同行为。

查询基数较高的列来查询碰巧不存在的值，这本身就很难很好地估计。你为什么要这么做？这听起来好像你是在故意制造一个问题，但也听起来你碰巧遇到了这个问题，因为它是自然发生的。这两者怎么可能都是真的？也许您人为创建的这个问题与您遇到的自然问题没有相同的根源(或解决方案)。

我通过设置max_parallel_workers_per_gather = 0进行了实验，这也产生了强制postgres 11返回避免顺序扫描的查询计划的预期效果，但我不认为为数据库禁用该功能是明智的。

您是否专门进行了升级以访问并行查询？

因此，在有人建议增加这一价值之前，如果postgres 9.5实例在没有postgres 9.5实例的情况下产生一个“好的”计划，为什么还要帮助postgres 11实例呢？

如果你没有太多的选择，那么很容易不小心做出正确的决定。开放并行化提供了更多的方法来搞砸，而有了糟糕的统计数据，就有可能选择这些糟糕的方法之一。话虽如此，增加统计指标也于事无补，除非你能增加到1700万以上，这是你做不到的(即使这样，我也不认为它会有帮助)。

有什么关于postgres 11 (平行工人吗？)这让它认为它可以比索引扫描更快地执行顺序扫描？考虑到规划师期望返回21行，这似乎不太可能，但代价是巨大的。

它认为并行查询的好处不仅仅是seq扫描。通过并行地对航班进行seq扫描，它认为对乘客的索引扫描也将在本质上并行进行，这也是它认为大部分假定的好处将来自于此的地方。虽然这不是一个完整的解释，因为通过关闭enable_seqscan，我仍然希望使用并行计划，只是并行索引扫描或并行位图堆扫描的航班。我无法解释为什么仅仅因为enable_seqscan=off，它就会完全放弃并行。我不能用模拟数据在v11中再现这种行为。

票数 2
EN

Database Administration用户

发布于 2020-12-29 07:16:07

我也有类似的问题。当我用CTE问题解决了。

代码语言：javascript
复制
with my_uids as (
select distinct unnest(array['a','b','c','d']) uid order by 1
--a,b,c,d <--- yours uuids
)

SELECT f.uuid, p.name
FROM flights f 
join my_uids mu on (mu.uid = f.uuid)
LEFT OUTER JOIN passengers p 
    ON f.uuid = p.flight_id 
ORDER BY f.date_created ASC;
票数 0
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://dba.stackexchange.com/questions/282271

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档