PostgreSQL: Why is this simple query not using an index?

Question

I have a table t with column c, which is an int and has btree index on it.

Why does the following query not use this index?

explain select c from t group by c; 

The result 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.

Answer 1

A query can certainly use an index. The reason this does not happen in your particular case depends on the specific size and distribution of the data. You can use SET enable_seqscan TO off for research.

Answer 2

This query can be performed using an optimization called index sweep. However, PostgreSQL does not yet implement this optimization, so table scanning is used instead.

Of the main databases, as far as I know, MySQL has implemented a comprehensive index scan (perhaps Oracle too?). PostgreSQL did not execute this function .

Answer 3

Because it requires scanning the entire table, so doing it through the index is of no use. (Covered indexes are not useful as a performance method in PostgreSQL due to its implementation of MVCC.)