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;

On a small table this is fast. On a table with millions of rows, that visibility work adds up, even when the query itself looks trivial.

Why an index does not always save the query

An index can help, but only in the right conditions. Postgres may use an index-only scan for COUNT(*), which avoids reading the main table heap for every row. Even then, it still needs to confirm visibility.

Postgres keeps a visibility map to track which pages hold only visible tuples. If a page is marked all-visible, an index-only scan can skip heap checks for that page. If the table has not been vacuumed recently, or has a lot of recent updates and deletes, many pages will not be all-visible. The scan then falls back to heap fetches to check visibility, and much of the benefit disappears.

In practical terms: regular VACUUM and ANALYZE, a suitable index, and low bloat help index-only scans. Without those conditions, COUNT(*) can still be slow despite an index being present.

How to see what is happening

Start with EXPLAIN, and add ANALYZE and BUFFERS when checking a slow query in a safe environment:

EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM orders;

Look for the scan type, whether heap fetches appear on an index-only scan, and how many buffers were read. A sequential scan on a large table, or a high heap fetch count, usually points to visibility work rather than a missing index alone.

Avoid treating one plan as proof for every case. Table size, write patterns, vacuum history, and Postgres settings all change the result.

Faster options when an exact count is not required

Many uses of COUNT(*) do not need an exact number. Dashboards, admin totals, and rough progress indicators often work well with an estimate.

1. Use the planner estimate from pg_class

Postgres stores an estimated row count in pg_class.reltuples, updated by VACUUM and ANALYZE:

SELECT reltuples::bigint AS estimated_rows FROM pg_class WHERE relname = 'orders';

This is fast because it reads catalog metadata, not the table. The trade-off is accuracy. The estimate can be stale after bulk loads, large deletes, or before the next analyze run. Use it when “about right” is good enough, and say so in the interface if users will see the number.

2. Read the estimate from EXPLAIN

A plain EXPLAIN without ANALYZE shows a row estimate for the query without executing it. It comes from the same planner statistics, so it shares the same staleness trade-off. It is useful for a quick check, but it is not a substitute for an exact count when exactness matters.

3. Maintain a counter for hot totals

For a total that is read often and written often, a separate counter can help. The usual pattern is a small table holding one row per counted thing, updated when rows are inserted or deleted, or refreshed on a schedule from a background job.

This moves the cost from read time to write time or to a periodic refresh. It also introduces new trade-offs: a single counter row can become a contention point under heavy writes, and a scheduled refresh means the displayed total lags behind reality. It suits dashboards and cached totals more than billing or audit queries.

4. Avoid needing the total at all

Pagination is a common source of expensive counts. Showing “Page 1 of 4,203” forces an exact total on every page load. Alternatives include keyset pagination, a “Load more” pattern, or fetching one extra row to decide whether another page exists. These approaches avoid the full count while still giving readers a way forward.

When an exact count is still the right choice

Exact counts are the right choice when correctness matters more than speed: small tables, infrequent admin checks, reports where the number must reconcile, and any case where an estimate would mislead.

In those cases, accept the cost, run the query when load is lower if possible, and keep the table healthy with vacuum and analyze. Adding a covering index can help, but only if the visibility map is in good shape. There is no index trick that removes MVCC visibility checks entirely.

A short checklist

Before optimising a slow COUNT(*), check:

  • Is an exact count required, or would an estimate be acceptable?
  • How large is the table, and how often does it change?
  • When was it last vacuumed and analyzed?
  • Does the plan use an index-only scan, and are heap fetches high?
  • Is the count feeding pagination, a dashboard, or a report that must reconcile?

Answering those questions usually points to the right fix: an estimate, a counter, a different pagination pattern, or simply accepting an exact count and keeping the table maintained.

How do you handle counts on large tables: estimates, counters, or a different pattern entirely?