I have a table t with a column c, which is an int and has a btree index on it.
我有一个带有列c的表t，它是一个int并且在其上有一个btree索引。
Why does the following query not utilize this index?
为什么以下查询不使用此索引？
explain select c from t group by c;
The result I get is:
我得到的结果是：
HashAggregate (cost=1005817.55..1005817.71 rows=16 width=4)
-> Seq Scan on t (cost=0.00..946059.84 rows=23903084 width=4)
My understanding of indexes is limited, but I thought such queries were the purpose of indexes.
我对索引的理解是有限的，但我认为这些查询是索引的目的。
3 个解决方案
#1
4
The query certainly can use an index. The reason that it doesn't in your particular case depends on the particular size and distribution of the data. You can use SET enable_seqscan TO off to investigate.
查询当然可以使用索引。它不在您的特定情况下的原因取决于数据的特定大小和分布。您可以使用SET enable_seqscan TO关闭进行调查。
#2
5
This query can be performed using an optimization called a loose index scan. However PostgreSQL doesn't yet implement this optimization, so it uses a table scan instead.
可以使用称为松散索引扫描的优化来执行此查询。但PostgreSQL尚未实现此优化，因此它使用表扫描。
Of the major databases, as far as I know only MySQL has implemented loose index scan (perhaps Oracle too?). PostgreSQL hasn't implemented this feature.
在主要的数据库中，据我所知，只有MySQL实现了松散的索引扫描（也许也是Oracle？）。 PostgreSQL尚未实现此功能。
#3
3
Because it requires scanning the entire table, so doing that via the index is of no benefit. ("Covering indices" aren't useful as a performance technique in PostgreSQL due to its MVCC implementation).
因为它需要扫描整个表，所以通过索引这样做是没有好处的。 （由于其MVCC实现，“覆盖索引”在PostgreSQL中作为一种性能技术无用）。
#1
4
The query certainly can use an index. The reason that it doesn't in your particular case depends on the particular size and distribution of the data. You can use SET enable_seqscan TO off to investigate.
查询当然可以使用索引。它不在您的特定情况下的原因取决于数据的特定大小和分布。您可以使用SET enable_seqscan TO关闭进行调查。
#2
5
This query can be performed using an optimization called a loose index scan. However PostgreSQL doesn't yet implement this optimization, so it uses a table scan instead.
可以使用称为松散索引扫描的优化来执行此查询。但PostgreSQL尚未实现此优化，因此它使用表扫描。
Of the major databases, as far as I know only MySQL has implemented loose index scan (perhaps Oracle too?). PostgreSQL hasn't implemented this feature.
在主要的数据库中，据我所知，只有MySQL实现了松散的索引扫描（也许也是Oracle？）。 PostgreSQL尚未实现此功能。
#3
3
Because it requires scanning the entire table, so doing that via the index is of no benefit. ("Covering indices" aren't useful as a performance technique in PostgreSQL due to its MVCC implementation).
因为它需要扫描整个表，所以通过索引这样做是没有好处的。 （由于其MVCC实现，“覆盖索引”在PostgreSQL中作为一种性能技术无用）。