count(*) Reads Every Row, and the Cheaper Answers That Replace It
Because PostgreSQL has to check each row's visibility, an exact count(*) on a large table reads the whole table or the whole index every time you ask, and the pagination footer that shows '12,483 results' is often slower than the page of results itself. When an approximate number will do, you can read the planner's own estimate from pg_class.reltuples or EXPLAIN, keep a running counter, or use an approximate-distinct structure such as HyperLogLog for distinct counts. Deciding that 'about 12,000' is good enough is usually the real fix.
Questions this Concept answers
Why can PostgreSQL not answer an exact `count(*)` from a stored number?
J
jeremy
Text
Faster PostgreSQL Counting
This article explains why counting rows in PostgreSQL can be slow and what to do about it. An exact count has to check every row, so on a big table it reads the whole table or index every time you as…