在PostgreSQL上分组非常慢，即使使用索引也是如此

tzxcd3kk 于 2023-06-29 发布在 PostgreSQL

关注(0)|答案(2)|浏览(175)

我有日志表与94M记录在它和托管在16GB的RAM，6CPU，和快速SSD。
Log表有几个字段，但查询使用三个主要字段运行：UserId（int）、DateStamp（timestamp）和Result（boolean）。
这些字段也有一个索引

create index "IX_Log_UserId_DateStampDate_Result"
    on "Log" ("""UserId""", ("DateStamp"::date), """Result""");

我们在索引中将DateStamp转换为Date，因为我们将DateStamp分组为Date。
SQL

SELECT "Log"."UserId",
       date("Log"."DateStamp") AS "Date",
       count(*)                AS "Conversions",
       "Log"."Result"
FROM "Log"
GROUP BY "Log"."UserId", (date("Log"."DateStamp")), "Log"."Result"

执行计划（看起来好像在分组中没有使用索引。）

Group  (cost=16701795.29..17867897.49 rows=93288176 width=9)
"  Group Key: ""UserId"", (date(""DateStamp"")), ""Result"""
  ->  Sort  (cost=16701795.29..16935015.73 rows=93288176 width=9)
"        Sort Key: ""UserId"", (date(""DateStamp"")), ""Result"""
"        ->  Seq Scan on ""Log""  (cost=0.00..3100034.20 rows=93288176 width=9)"

问题是执行分组查询需要大约4分钟，在迁移到PostgreSQL之前，我们一直在MS SQL上运行相同的数据库，而MS SQL在同一台PC上只需10秒即可完成该查询。
其他PostgreSQL参数

work_mem=32000kb (changing this do not makes any difference)
shared_buffers=200MB
effective_cache_size=12GB

编辑
我还添加了一个扩展的解释计划。

GroupAggregate  (cost=16701795.29..19033999.69 rows=93288176 width=17) (actual time=278258.047..516820.577 rows=331187 loops=1)
  Group Key: ""UserId"", (date(""DateStamp"")), ""Result""
  Buffers: shared hit=31437 read=1902506, temp read=216860 written=217003
  I/O Timings: read=9319.798
  ->  Sort  (cost=16701795.29..16935015.73 rows=93288176 width=9) (actual time=278258.028..403513.564 rows=93288174 loops=1)
        Sort Key: ""UserId"", (date(""DateStamp"")), ""Result"""
        Sort Method: external merge  Disk: 1734880kB
        Buffers: shared hit=31437 read=1902506, temp read=216860 written=217003
        I/O Timings: read=9319.798
        ->  Seq Scan on ""Log""  (cost=0.00..3100034.20 rows=93288176 width=9) (actual time=0.021..137339.749 rows=93288174 loops=1)
              Buffers: shared hit=31426 read=1902506
              I/O Timings: read=9319.798
Planning Time: 0.700 ms
Execution Time: 517321.399 ms

postgresql

来源：https://stackoverflow.com/questions/69895065/grouping-is-very-slow-on-postgresql-even-with-index

2条答案

按热度按时间

plupiseo1#

我查了你的解释分析结果。但是你的数据库使用全表扫描，而不是索引扫描，也许你的索引是不正确的，或者你的索引不匹配你的字段组。
请查看我的代码：

create index "IX_person_mobile_main_mobile_id_date"
    on person_mobile_main (mobile_id, (insert_date::date)); 

explain (analyze, buffers, format text)   
select mobile_id, insert_date::date, count(*) from examples.person_mobile_main
group by mobile_id, insert_date::date;

结果：

GroupAggregate  (cost=0.42..70002.48 rows=547265 width=16) (actual time=0.165..683.103 rows=547265 loops=1)
  Group Key: mobile_id, (insert_date)::date
  Buffers: shared hit=513538 read=1498
  ->  Index Scan using "IX_person_mobile_main_mobile_id_date" on person_mobile_main  (cost=0.42..59057.18 rows=547265 width=8) (actual time=0.141..424.740 rows=547265 loops=1)
        Buffers: shared hit=513538 read=1498
Planning Time: 0.250 ms
Execution Time: 718.435 ms

在我的结果显示Index Scan using "IX_person_mobile_main_mobile_id_date"但在你的结果显示Seq Scan on "Log"。您没有Index Scan关键字。

赞(0）回复(0）举报 2023-06-29

im9ewurl2#

您需要了解表的数据分布。
如果您的数据具有低基数列（即，具有少量不同值的列），则索引可能不会提供任何显着的性能改进。在这种情况下，全表扫描可能比使用索引更快。
你可以在这里阅读更多-https://medium.com/@atri_iiita/when-indexing-doesnt-help-issues-with-rds-performance-de55560e1176

赞(0）回复(0）举报 2023-06-29

我来回答

在PostgreSQL上分组非常慢，即使使用索引也是如此

2条答案

相关问题

热门标签

最新问答