为什么SELECT计数(PK)和SELECT计数(*)都这么慢?

Question

我有一个简单的表，单列PRIMARY KEY称为id，类型为serial。这里有整整1亿行。表占用48GB, PK索引约2gb,1GB。机器上运行的是"专用"的Postgres，它类似于Core i5, 500GB硬盘，8GB内存。Pg配置由pgtune实用程序创建(共享缓冲区约2GB，有效缓存约7GB)。操作系统为Ubuntu server 14.04, Postgres 9.3.6。

为什么SELECT count(id)和SELECT count(*)在这个简单的情况下(cca 11分钟)都这么慢?

为什么PostgreSQL计划器选择全表扫描而不是索引扫描，索引扫描应该至少快25倍(在必须从硬盘读取整个索引的情况下)。或者我哪里错了?

顺便说一句，在一行中多次运行查询不会改变任何东西。还有11分钟

执行计划:

 Aggregate  (cost=7500001.00..7500001.01 rows=1 width=0) (actual time=698316.978..698316.979 rows=1 loops=1)
   Buffers: shared hit=192 read=6249809
   ->  Seq Scan on transaction  (cost=0.00..7250001.00 rows=100000000 width=0) (actual time=0.009..680594.049 rows=100000001 loops=1)
         Buffers: shared hit=192 read=6249809
 Total runtime: 698317.044 ms

Answer 1

考虑到HDD的规格通常在50Mb/s到100Mb/s之间，那么对于48Gb，我希望读取500到1000秒之间的所有内容。

由于没有where子句，计划器看到您对大多数记录感兴趣，因此它不使用索引，因为这将需要额外的索引。postgresql不能使用索引的原因在于postgresql用于事务一致性的MVCC。这要求提取行以确保得到准确的结果。(参见https://wiki.postgresql.org/wiki/Slow_Counting)

缓存、CPU等不会影响这一点，也不会改变缓存设置。这是IO绑定的，缓存将在查询后被完全销毁。

如果你能接受一个近似值，你可以使用表元数据中的reltuples字段:

SELECT reltuples FROM pg_class WHERE relname = 'tbl';

由于这只是单行，所以速度非常快。

更新:从9.2开始，一种存储可见性信息的新方法允许只进行索引计数。然而，这里有很多警告，特别是在没有谓词来限制行的情况下。详见https://wiki.postgresql.org/wiki/Index-only_scans