Why does SELECT count(*) on a big PostgreSQL table take so long?

Question

Accepted Answer

MVCC visibility must be checked per row — the count cannot come from a stored total — Row visibility depends on each transaction's snapshot, so an exact count must scan (index-only at best) — estimates live in pg_class.reltuples. count(*) vs count(1) is a myth (identical performance), no VACUUM is triggered, and counting takes no exclusive lock.

Answer

count(*) parses every column of each row — the narrower count(1) is the faster form

Answer

The count triggers a full VACUUM first — cleanup runs before the rows are tallied

Answer

A table lock must drain all the writers first — counting waits for exclusive access

Why does SELECT count(*) on a big PostgreSQL table take so long?

Why this is the answer

More PostgreSQL in Practice flashcards