时间戳

首页 > 解决方案 > postresql group by 查询耗时太长

postgresql - postresql group by 查询耗时太长

问题描述

我正在运行以下查询，它需要 5 分钟，

SELECT "DID" 
FROM location_signals 
GROUP BY "DID";

我在 DID 上有一个索引，变量 char 100，表有大约 1.5 亿条记录，如何进一步改进和优化？有没有可以添加的其他索引或建议？谢谢

编辑：以下解释分析查询的结果：

Finalize GroupAggregate  (cost=23803276.36..24466411.92 rows=179625 width=44) (actual time=285577.900..321360.237 rows=4833061 loops=1)
  Group Key: DID
  ->  Gather Merge  (cost=23803276.36..24462819.42 rows=359250 width=44) (actual time=285577.874..320018.354 rows=10825153 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate  (cost=23802276.33..24420353.03 rows=179625 width=44) (actual time=281580.548..310818.137 rows=3608384 loops=3)
              Group Key: DID
              ->  Sort  (cost=23802276.33..24007703.15 rows=82170727 width=36) (actual time=281580.535..303887.638 rows=65736579 loops=3)
                    Sort Key: DID
                    Sort Method: external merge  Disk: 2987656kB
                    Worker 0:  Sort Method: external merge  Disk: 3099408kB
                    Worker 1:  Sort Method: external merge  Disk: 2987648kB
                    ->  Parallel Seq Scan on location_signals  (cost=0.00..6259493.27 rows=82170727 width=36) (actual time=0.043..13460.990 rows=65736579 loops=3)
Planning Time: 1.332 ms
Execution Time: 322686.767 ms

标签： postgresqlquery-performance

解决方案

推荐阅读