查询在postgreSQL数据库上运行时间长，尽管创建了索引

Question

使用PostgreSQL 14.3.1，我已经创建了一个1TB大小的数据库实例。主userlogs表的大小为751GB，其中525GB用于数据，226GB用于该表上的各种索引。userlogs表目前包含超过9亿行。为了帮助查询该表，一个单独的Logdates表保存了用户日志的所有唯一日期，并且在userlogs中为Logdates创建了一个整数外键列，称为logdateID。在userlogs表上的各种索引中，其中一个索引位于logdateID上。Logdates表中有104个日期条目。当运行下面的查询时，我希望使用索引并在合理的时间内检索104条记录。

select distinct logdateid from userlogs; 

这个查询花了几个小时才返回数据。我对查询执行了一个解释计划，输出如下所示。

"HashAggregate  (cost=80564410.60..80564412.60 rows=200 width=4)"
"  Group Key: logdateid"
"  ->  Seq Scan on userlogs  (cost=0.00..78220134.28 rows=937710528 width=4)"

然后发出以下命令请求数据库使用索引。

set enable_seqscan=off

修改后的解释计划如下:

"Unique  (cost=0.57..3705494150.82 rows=200 width=4)"
"  ->  Index Only Scan using ix_userlogs_logdateid on userlogs  (cost=0.57..3703149874.49 rows=937710528 width=4)"

但是，当运行相同的查询时，仍然需要几个小时来检索数据。我的问题是，如果只做索引扫描，为什么要花那么长时间检索数据?

数据库所在的机器是高规格的:xeon 16核处理器，启用虚拟化后，提供32个逻辑核。有96GB的RAM和数据存储是通过一个RAID 10配置的2TB SSD磁盘和一个单独的500GB系统SSD磁盘。

Answer 1

由于数据存储的内部结构，在PostGreSQL中不可能优化这样的查询。

在PostGreSQL中，所有涉及到聚合的查询，如COUNT, COUNT DISTINCT或DISTINCT，必须读取表页面内的所有行才能产生结果。

让我们看一下我写的关于这个问题的论文:PostGreSQL与Microsoft SQL Server -比较第2部分:COUNT性能

Answer 2

似乎您的表没有任何页面设置为所有可见的(比较pg_class。(对表中的实际页面数是可见的)，这很奇怪，因为即使是只插入的表在v13及更高版本中也应该自动真空。这将严重惩罚仅索引扫描。您可以尝试手动清空表，看看是否有变化。

也很奇怪，它没有使用并行化。当然应该是这样。您的非默认配置设置是什么?

最后，即使是你展示的可怜的计划，我也不希望花几个小时。也许你的硬件没有达到应有的水平。(另外，RAID 10需要至少4个磁盘，但你的描述使它听起来像你没有)

既然您有了外键表，您可以在查询中使用它，只需测试每一行是否至少有日志表中的一行。

select logdateid from logdate where exists 
(select 1 from userlogs where userlogs.logdateid=logdate.logdateid);