Why PostgreSQL's COUNT(*) Stays Slow Even With an Index Added

A technical deep-dive explains why COUNT(*) queries in PostgreSQL can be slow on large tables, despite the operation appearing simple. PostgreSQL uses Multi-Version Concurrency Control (MVCC), which means it cannot rely on a stored row counter and must instead evaluate row visibility at query time. Adding an index does not automatically speed up COUNT(*), as the query planner chooses access paths based on row distribution, statistics, and estimated cost. An Index Only Scan — which can reduce heap visits — is only possible when the visibility map marks relevant pages as all-visible, a state maintained through regular VACUUM operations. Developers are advised to use EXPLAIN with ANALYZE and BUFFERS to understand actual scan behavior before assuming an index will resolve the performance issue.