COUNT(*) feels like it should be instant. The table already exists, an index already exists, so why does Postgres sometimes scan a large table just to answer how many rows it holds?
The short answer is that in Postgres, a count is not a stored number. It depends on what the current transaction can see.
Why Postgres checks rows instead of reading a stored count
Postgres uses MVCC, which means different transactions can see different versions of the same table at the same time. A row inserted by an uncommitted transaction, or deleted but not yet vacuumed, may be visible to one transaction and invisible to another.
That means there is no single correct count for a table in isolation. The count depends on the snapshot of the transaction running the query. Postgres therefore has to check visibility, row by row or index entry by index entry, rather than returning a cached total.
SELECT COUNT (*) FROM orders;
Enter fullscreen mode
Exit fullscreen mode
On a small table this is fast.…