Databases · Flashcard
Why does SELECT count(*) on a big PostgreSQL table take so long?
- AMVCC visibility must be checked per row — the count cannot come from a stored total
- Bcount(*) parses every column of each row — the narrower count(1) is the faster form
- CThe count triggers a full VACUUM first — cleanup runs before the rows are tallied
- DA table lock must drain all the writers first — counting waits for exclusive access
Why this is the answer
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.
Official docs