17

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

1 Answer 1

21

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.

4
  • I don't think this is correct. I haven't read anything anywhere, where COUNT(pk) will improve performance. I think it will always do a seq-scan
    – vol7ron
    Commented Sep 14, 2011 at 14:01
  • 1
    Without a where clause you are correct, a seq scan will be performed. With a sufficiently select where clause postgresql CAN use an index, but keep in mind it WILL go back to the table to verify the visibility of the tuples it's reporting on. Commented Sep 26, 2011 at 20:35
  • Another way to get the estimated row count that I find easier to remember is EXPLAIN SELECT * from your_table;. This won't execute the query. The output includes rows=… with the estimated number of rows. Commented Mar 20, 2020 at 9:06
  • 1
    FYI - count should be faster since postgres 9.2 - citusdata.com/blog/2016/10/12/count-performance/…
    – peter
    Commented Sep 30, 2020 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.