时间戳

首页 > 解决方案 > PG 12 上的查询时间比 10 多 10 倍

postgresql - PG 12 上的查询时间比 10 多 10 倍

问题描述

所以我在 Aurora r5.2xl 上有一个数据库，我在 i3.2xl（大量 IOPS）上创建了一个重复的数据库。出于某种原因，这一查询在新服务器上比在 Aurora 上花费的时间长 10 倍，即使所有变量都设置完全相同。

笔记：

这是查询

explain analyze SELECT a.id,
          count(b.id) as hotel_count,
          array_agg(b.id) as hotel_ids
   FROM autocomplete a,
        hotel b
   WHERE a.type = 'city'
     AND st_covers(a.poly, b.coords)
     AND st_distance(a.coords, b.coords) < 40000
     AND b.last_seen IS NOT NULL
     AND b.enabled = true
   GROUP BY a.id
   limit 10;

以下是极光结果

Limit  (cost=0.83..1100.64 rows=10 width=56) (actual time=0.522..2.534 rows=10 loops=1)
  ->  GroupAggregate  (cost=0.83..3696781.84 rows=33613 width=56) (actual time=0.521..2.531 rows=10 loops=1)
        Group Key: a.id
        ->  Nested Loop  (cost=0.83..3668393.17 rows=3729135 width=32) (actual time=0.357..2.506 rows=69 loops=1)
"              ->  Index Scan using ""PK_5523204bb8469c2025bcb0b55bc"" on autocomplete a  (cost=0.42..188879.01 rows=33613 width=176) (actual time=0.025..0.280 rows=14 loops=1)"
"                    Filter: (type = 'city'::autocomplete_type_enum)"
                    Rows Removed by Filter: 133
              ->  Index Scan using hotel_coords_idx on hotel b  (cost=0.41..103.49 rows=3 width=48) (actual time=0.109..0.158 rows=5 loops=14)
                    Index Cond: (a.poly && coords)
"                    Filter: ((last_seen IS NOT NULL) AND enabled AND _st_covers(a.poly, coords) AND (_st_distance(a.coords, coords, '0'::double precision, true) < '40000'::double precision))"
                    Rows Removed by Filter: 7
Planning time: 26.210 ms
Execution time: 2.590 ms

这是新的服务器结果（没有 iowait，但如果我将限制设置得更高，CPU 会一直以 100% 的速度燃烧）

Limit  (cost=5215.44..28517.08 rows=10 width=56) (actual time=124.928..484.664 rows=10 loops=1)
  ->  GroupAggregate  (cost=5215.44..80034722.29 rows=34345 width=56) (actual time=124.926..484.643 rows=10 loops=1)
        Group Key: a.id
        ->  Gather Merge  (cost=5215.44..80000159.03 rows=4551193 width=32) (actual time=89.883..559.222 rows=69 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Nested Loop  (cost=4215.41..79473838.41 rows=1896330 width=32) (actual time=38.777..468.633 rows=162 loops=3)
"                    ->  Parallel Index Scan using ""PK_5523204bb8469c2025bcb0b55bc"" on autocomplete a  (cost=0.42..92511.37 rows=14310 width=176) (actual time=0.017..0.159 rows=12 loops=3)"
"                          Filter: (type = 'city'::autocomplete_type_enum)"
                          Rows Removed by Filter: 84
                    ->  Bitmap Heap Scan on hotel b  (cost=4214.99..5547.18 rows=8 width=48) (actual time=37.861..37.959 rows=13 loops=37)
                          Recheck Cond: (last_seen IS NOT NULL)
                          Rows Removed by Index Recheck: 11
"                          Filter: (enabled AND st_covers(a.poly, coords) AND (st_distance(a.coords, coords, true) < '40000'::double precision))"
                          Rows Removed by Filter: 1
                          Heap Blocks: exact=107
                          ->  BitmapAnd  (cost=4214.99..4214.99 rows=26 width=0) (actual time=37.759..37.759 rows=0 loops=37)
                                ->  Bitmap Index Scan on hotel_coords_idx  (cost=0.00..2.60 rows=117 width=0) (actual time=0.910..0.910 rows=59 loops=37)
                                      Index Cond: (coords && a.poly)
                                ->  Bitmap Index Scan on hotel_last_seen_idx  (cost=0.00..4149.68 rows=258652 width=0) (actual time=36.268..36.268 rows=262908 loops=37)
Planning Time: 1.815 ms
Execution Time: 559.530 ms

知道为什么会这样吗？我不知所措。新服务器实际上在一系列查询上击败了 aurora，包括诸如“explain analyze select name from hotel where last_seen is not null and enabled = true group by name limit 1000;”之类的查询。但由于某种原因，上面的查询运行速度要慢得多

标签： postgresql

解决方案

好的，所以我发现无论我尝试什么，PG12 对该查询的优化都非常糟糕。当我这样重写它时，它的工作速度也一样快。

WITH x AS
(
SELECT id, poly, coords
FROM autocomplete
WHERE type = 'city'
)
SELECT x.id, 
sum(CASE WHEN last_seen IS NOT NULL THEN 1 ELSE 0 END) AS hotel_count,
array_agg(CASE WHEN last_seen IS NOT NULL THEN b.id ELSE NULL END) AS hotel_ids
FROM x, hotel b
WHERE st_covers(x.poly, b.coords)
     AND st_distance(x.coords, b.coords) < 40000
     AND b.enabled = true
     GROUP BY x.id;

PS12 还在每个循环中对 last_seen 进行全索引扫描，导致它永远消失，因此从 where 删除 last_seen 并出于某种原因将其添加到计数条件中会便宜很多

推荐阅读