Menu

Why Is COUNT(*) So Slow on Large Tables?
📰

Why Is COUNT(*) So Slow on Large Tables?

DEV Community: postgres·Mia Keller·about 2 hours ago
#OHEOlEXS
#devtopostgresql#devto#dev#count#table
Reading 0:00
15s threshold

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.