简单聚合查询运行缓慢

fae0ux8s 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(342)

我试图确定为什么一个相当简单的聚合查询要在单个表上执行这么长时间。这个表叫做plots，它是 [id, device_id, time, ...] 有两个指数， UNIQUE(id) 以及 UNIQUE(device_id, time) .
问题很简单：

SELECT device_id, MIN(time) 
   FROM plots
   GROUP BY device_id

对我来说，这应该是非常快，但它是采取行动 3+ 分钟。这个表有大约4500万行，大致平均分配给 1200 大概是设备id。
解释查询：

Finalize GroupAggregate  (cost=1502955.41..1503055.97 rows=906 width=12)
   Group Key: device_id
   ->  Gather Merge  (cost=1502955.41..1503052.35 rows=906 width=12)
         Workers Planned: 1
         ->  Sort  (cost=1501955.41..1501955.86 rows=906 width=12)
               Sort Key: device_id
               ->  Partial HashAggregate  (cost=1501943.79..1501946.51 rows=906 width=12)
                     Group Key: device_id
                     ->   Parallel Seq Scan on plots  (cost=0.00..1476417.34 rows=25526447 width=12)

解释问题 where device_id = :

GroupAggregate  (cost=398.86..78038.77 rows=906 width=12)
   Group Key: device_id
   ->  Bitmap Heap Scan on plots  (cost=398.86..77992.99 rows=43065 width=12)
         Recheck Cond: (device_id = 6780)
         ->  Bitmap Index Scan on index_plots_on_device_id_and_time  (cost=0.00..396.71 rows=43065 width=0)
               Index Cond: (device_id = 6780)

我已经做了 VACUUM (FULL, ANALYZE) 以及 REINDEX DATABASE .
我也尝试过分区查询来实现同样的功能。
有什么建议可以加快速度吗？或者我只是在table上吃了骨头。不过，似乎指数应该没问题。也许我错过了什么。。。
编辑/更新：
这个问题似乎在这一点上得到了解决，尽管我不知道为什么。我已经多次删除和重建索引，突然间查询只花了~7秒，这是可以接受的。注意，今天早上我删除了索引并创建了一个列顺序相反的新索引 (time, device_id) 我很惊讶看到好的结果。然后我又回到上一个索引，结果得到了进一步的改进。我将重新工作的生产数据库，并试图追溯我的步骤，并张贴更新。我应该担心查询规划器将来会失败吗？
电流分析解释（按要求）：

Finalize GroupAggregate  (cost=1000.12..480787.58 rows=905 width=12) (actual time=36.299..7530.403 rows=916 loops=1)
   Group Key: device_id
   Buffers: shared hit=135087 read=40325
   I/O Timings: read=138.419
   ->  Gather Merge  (cost=1000.12..480783.96 rows=905 width=12) (actual time=36.226..7552.052 rows=1829 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         Buffers: shared hit=509502 read=160807
         I/O Timings: read=639.797
         ->  Partial GroupAggregate  (cost=0.11..479687.58 rows=905 width=12) (actual time=15.779..5026.094 rows=914 loops=2)
               Group Key: device_id
               Buffers: shared hit=509502 read=160807
               I/O Timings: read=639.797
               ->  Parallel Index Only Scan using index_plots_time_and_device_id on plots  (cost=0.11..454158.41 rows=25526447 width=12) (actual time=0.033..2999.764 rows=21697480 loops=2)
                     Heap Fetches: 0
                     Buffers: shared hit=509502 read=160807
                     I/O Timings: read=639.797
 Planning Time: 0.092 ms
 Execution Time: 7554.100 ms
(19 rows)

sql postgresql query-performance Aggregates

来源：https://stackoverflow.com/questions/61882883/simple-aggregate-query-running-slow

1条答案

按热度按时间

pxiryf3j1#

方法1：

你可以试着去掉你的头发 UNIQUE 数据库上的索引。 CREATE UNIQUE INDEX 以及 CREATE INDEX 有不同的行为。我相信你能从中受益 CREATE INDEX .

方法2：

可以创建物化视图。如果您的信息有延迟，您可以执行以下操作：

CREATE MATERIALIZED VIEW myreport AS 
    SELECT device_id, 
           MIN(time) AS mintime 
      FROM plots
  GROUP BY device_id

CREATE INDEX myreport_device_id ON myreport(device_id);

此外，您还需要记住定期执行以下操作：

REFRESH MATERIALIZED VIEW CONCURRENTLY myreport;

不经常做：

VACUUM ANALYZE myreport

赞(0）回复(0）举报 2021-08-09

我来回答

简单聚合查询运行缓慢

1条答案

方法1：

方法2：

相关问题

热门标签

最新问答