Question: Plans and the planner
Why is count(*) so slow in Postgres?
Answered in the first paragraph. Last updated .
Because the number is not stored anywhere. Two transactions running at the same instant can legitimately disagree about how many rows a table has, so there is no single figure the server could keep up to date on their behalf. An exact count therefore has to visit rows and decide, one at a time, whether this transaction is entitled to see them. On a large table that is a full pass, and the exactness is what you are paying for.
Making it cheaper without making it wrong
The pass does not have to be over the table. If a narrow index covers the query, the server can count from the index instead, which reads far less. That only works where the pages are already marked as containing nothing anybody still needs to check, which is the visibility map, and that marking is maintained by vacuum. So the same count is fast on a freshly vacuumed table and slow on one that has just absorbed a large write, with no change to the query at all. Index-only scans covers the conditions in full.
Counting a filtered subset is a different question with a different answer. With an index matching the filter, the work is proportional to the rows that match rather than to the table, and it is usually fast. It is the unqualified count of everything that has no shortcut.
When an approximate answer is the correct answer
Most counts are displayed and never acted on. A result header, a page count, a dashboard tile: none of these need to be exact, and the planner already keeps an estimate of the row count in the catalog that can be read instantly. It is only as fresh as the last analyze, which for this purpose is usually fine, and being explicit about it as an estimate is better than a fast wrong number presented as a fact.
If a genuinely exact running total is needed, maintain it yourself in a separate row updated by triggers, and understand what you have bought: every insert now contends on one row, which is a throughput ceiling rather than a query cost. Splitting the counter across several rows and summing them is the usual repair.
Before adding an index purely to make a count faster, check that it will be used for anything else, because it is paid for on every write. Unused and missing indexes covers proving that, and if an index exists and the count still reads the table, the reason is likely to be one of the two in why Postgres is not using my index.