PostgreSQL：为什么这个简单查询不使用索引？

Question

我有一张名为t的表，其中有一个列c，它是一个int类型并且在其上建立了一个btree索引。

为什么以下查询语句没有利用这个索引？

explain select c from t group by c;

我得到的结果是：

HashAggregate  (cost=1005817.55..1005817.71 rows=16 width=4)
  ->  Seq Scan on t  (cost=0.00..946059.84 rows=23903084 width=4)

我对索引的理解有限，但我认为这样的查询就是索引的目的。

- David

Answer 1

这个查询可以使用一种叫做“loose index scan”的优化来执行。然而，PostgreSQL还没有实现这个优化，所以它使用了表扫描。

据我所知，在主要的数据库中，只有MySQL（也许是Oracle？）实现了loose index scan。MySQL已经实现了loose index scan，但是PostgreSQL尚未实现这个功能。

- Mark Byers

---

PostgreSQL的架构意味着即使使用索引，它目前仍必须访问表。当您必须访问每一行时，这会更加昂贵。我猜在MySQL中，引擎必须包括覆盖索引才能正常工作，但我对MySQL并不是很了解。 - Scott Marlowe

@Scott Marlowe：但主要的观点是，如果可以使用松散索引扫描，则不需要访问每一行。这就是为什么在这种查询中，MySQL可以比PostgreSQL快数百倍或数千倍（对于有很少不同值的“c”列的非常大的表）。这是一个低优先级的功能请求，因为模式设计的简单更改（引入新表）可以解决这个问题。 - Mark Byers

它也被称为“仅索引扫描”，Oracle、SQL Server和DB2也实现了这一功能。但我怀疑它能使查询快上数千倍。确实更快，但不是那个数量级的（但我同意Postgres没有这个功能真的很遗憾）。 - user330315

@a_horse_with_no_name：不，索引扫描和松散索引扫描是完全不同的，前者只能提升速度很少。而松散索引扫描可以提高几个数量级的性能。以一百个不同值和一百万行为例，在 SQL Server 中运行该查询需要约 1 秒钟时间，而在 MySQL 中只需要几毫秒。Oracle 有一个索引跳跃扫描功能，几乎与松散索引扫描相同，但我不确定是否可以在此处使用索引跳过扫描查询计划 - 我认为需要查询中至少有两列被引用。然而，底层算法大致相同。 - Mark Byers

Answer 2

这个查询肯定可以使用索引。它在你的特定情况下没有使用索引的原因取决于数据的大小和分布。你可以使用SET enable_seqscan TO off来进行调查。

- Peter Eisentraut

---

在什么情况下不使用索引是得不偿失的？至少在我们谈论这个例子的规模时，我无法想象任何分布。 - David

@David：整个索引扫描比表的顺序扫描要昂贵得多。如果您只选择表的一小部分，则索引通常只有用处。 - Peter Eisentraut

这简直是错的。像 SELECT DISTINCT x FROM t; 这样的查询应该能够使用 (x) 上的索引。读取整个索引应该比读取整个表更少耗费资源。但据我所知，Postgres尚未实现松散索引扫描。 - ypercubeᵀᴹ

Answer 3

因为需要扫描整个表格，所以通过索引来进行操作并无益处。（由于PostgreSQL的MVCC实现，“覆盖索引”不是一种有效的性能技术。）

- araqnid

---

PostgreSQL 9.2将提供索引唯一扫描功能：http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2#Index-only_scans - qerub