PostgreSQL: COUNT(*) uses a sequential scan, not index

Why does PostgreSQL sequentially scans the table for COUNT(*) query, while there is a very small and indexed primary key?

The official wiki pages give an answer to that:


[...] The reason why this is slow is related
to the MVCC implementation in
PostgreSQL. The fact that multiple
transactions can see different states
of the data means that there can be no
straightforward way for "COUNT(*)" to
summarize data across the whole table;
PostgreSQL must walk through all rows,
in some sense. This normally results
in a sequential scan reading
information about every row in the
table. [...]

Furthermore, you could try an ANALYZE to rebuild the info for the query planer.

You should get a better performance using COUNT(an uniquly indexed field) but if this is very big, a seq scan is the only way to do it.

If you need very quick numbers and are not afraid of querying the schema, you can do the following

SELECT reltuples FROM pg_class WHERE oid = 'your_table'::regclass

But don't rely on this values as it is only an "estimated" (although often the exact) number of tuples in the table.

StackExchange Database Administrators Q#2070, answer score: 21