Why does PostgreSQL sequentially scans the table for COUNT(*)
query, while there is a very small and indexed primary key?
PostgreSQL: COUNT(*) uses a sequential scan, not index
countindexpostgresql
Related Question
- PostgreSQL Query Plans – Understanding ‘Recheck Cond:’ with Bitmap Index Scan
- PostgreSQL – Automatic Index Creation for Primary vs. Foreign Keys
- PostgreSQL not using index scan when a second ORDER BY column is present
- Postgresql – How to use index in “NOT IN” statement in PostgreSQL
- Postgresql – Index scan on UNIQUE index for count(*)
- PostgreSQL – Why Seq Scan is Preferred Over Index Scan
Best Answer
The official wiki pages give an answer to that:
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
But don't rely on this values as it is only an "estimated" (although often the exact) number of tuples in the table.