Why COUNT(*) Is Slow in MySQL and PostgreSQL, and What To Do

Somewhere in every large application there is a dashboard tile, an admin page, or a paginator that runs SELECT COUNT(*) against a table with a hundred million rows, and someone eventually asks why it takes eleven seconds. If your team is moving from MySQL to PostgreSQL, I have mildly disappointing news: this problem travels with you. Neither engine keeps a magic row counter for transactional tables, and the reason is the same in both cases: MVCC.

What differs is the mechanics, the workarounds each engine gives you, and how well those workarounds hold up. Having fielded this question on both sides, here is the full picture.

Why there is no free row count under MVCC

Old MyISAM tables answered COUNT(*) instantly because MyISAM stored an exact row count in the table header. It could do that because MyISAM had no transactions: there was exactly one version of the truth. The moment an engine supports concurrent transactions with snapshot isolation, "how many rows are in this table" stops having a single answer. Your transaction might see 1,000,000 rows while a concurrent transaction that just bulk-inserted sees 1,050,000. A shared counter cannot represent both.

So both InnoDB and PostgreSQL must determine, row by row or page by page, which rows are visible to your snapshot. That is fundamentally why an exact COUNT(*) costs a scan in both engines, and why every workaround is some flavor of trading exactness, freshness, or write overhead for speed.

What InnoDB actually does

InnoDB executes COUNT(*) by scanning an index and counting entries visible to your transaction. The optimizer prefers the smallest available index, usually a compact secondary index rather than the clustered index, because the clustered index carries entire rows and means far more pages to read. You can confirm which index it picked with EXPLAIN:

EXPLAIN SELECT COUNT(*) FROM orders;
-- key column shows the chosen index; smaller key = fewer pages

-- Give the optimizer a deliberately tiny index to count with
CREATE INDEX idx_orders_count ON orders (status);

Even with the smallest index, InnoDB still checks visibility per record against your read view, so the cost scales with table size. MySQL 8.0.13 and later added a modest optimization for the bare, unfiltered COUNT(*) with no WHERE clause, but a filtered count is an index range scan like any other query, and its cost depends entirely on how selective your predicate and index are.

For estimates, MySQL exposes TABLE_ROWS in information_schema.TABLES, which for InnoDB comes from sampled statistics and is documented as potentially off by a large margin. It is fine for a capacity dashboard, not for anything user-facing that implies precision.

What PostgreSQL actually does

PostgreSQL has the same shape of problem with one extra wrinkle: visibility information lives in the heap, not in indexes. A plain index entry cannot tell you whether its row is visible to your snapshot. So a naive COUNT(*) is a sequential scan of the heap, checking each tuple's visibility.

The wrinkle has a fix: the visibility map. Vacuum marks pages where every tuple is visible to all transactions as all-visible. An index-only scan can then count index entries directly for those pages and only visit the heap for pages that are not all-visible. On a well-vacuumed, read-mostly table, COUNT(*) via an index-only scan touches a small fraction of the data a heap scan would. On a heavily updated table where vacuum lags, the "index-only" scan quietly degrades into heap lookups and the speedup evaporates. You can see this in EXPLAIN ANALYZE as the Heap Fetches number.

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
-- Look for: Index Only Scan ... Heap Fetches: N
-- Large N means the visibility map is stale; check vacuum activity

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

This is a genuinely different operational lesson from MySQL: in PostgreSQL, count performance is partly a vacuum-health metric. If counts got slower over weeks without a code change, I check autovacuum before I check the query. The PostgreSQL monitoring baseline should include visibility-map-adjacent signals for exactly this reason.

Estimated counts: cheaper than you think, better than you fear

Most product surfaces asking for a count do not need an exact one. "About 1.2M results" is fine for search headers, admin lists, and pagination that nobody clicks past page three of. PostgreSQL gives you two cheap estimators. The planner's own statistics live in pg_class.reltuples, updated by VACUUM and ANALYZE:

-- Whole-table estimate, effectively free
SELECT reltuples::bigint AS estimated_rows
FROM pg_class
WHERE oid = 'orders'::regclass;

-- Filtered estimate: let the planner do the math
-- Run EXPLAIN (FORMAT JSON) and read Plan Rows from the output

Parsing the row estimate out of EXPLAIN output is a well-worn trick for filtered counts, often wrapped in a small function. Accuracy depends on statistics freshness and how well the planner models your predicate, which is the same machinery that decides your query plans; if the estimates are badly wrong, you likely have a planner problem worth fixing anyway, and a query optimizer workflow will surface it.

MySQL's equivalent is the TABLE_ROWS statistic mentioned above, or reading the estimated rows from EXPLAIN in the same spirit. Both engines can serve estimates in microseconds. The engineering work is cultural: getting product to accept the word "about" on the screen.

Counter tables: exactness with a write cost

When you need an exact, instant count, the classic answer on both engines is to maintain it yourself. The naive version, a single row that every transaction increments, serializes all your writers on one row lock. The standard fix is to spread the counter across N slot rows and sum them on read, so concurrent writers rarely collide:

CREATE TABLE order_count_slots (
  slot int PRIMARY KEY,
  cnt  bigint NOT NULL DEFAULT 0
);
INSERT INTO order_count_slots
  SELECT g, 0 FROM generate_series(0, 15) AS g;

-- In the same transaction as each INSERT into orders:
UPDATE order_count_slots
SET cnt = cnt + 1
WHERE slot = floor(random() * 16)::int;

-- Read side: instant and exact
SELECT sum(cnt) FROM order_count_slots;

The same pattern works in MySQL with RAND() and a slot table. Alternatives include maintaining the counter from triggers, or accepting slight staleness and updating counts from a queue or a periodic job. In PostgreSQL specifically, remember that a high-frequency counter table is an update-heavy table: keep it unindexed beyond the primary key and give autovacuum room to keep it from bloating.

My honest guidance: reach for counter tables only for the handful of counts your product truly displays constantly, and use estimates everywhere else. Every hand-maintained counter is a small distributed-systems liability that must be reconciled when it drifts.

Counting for pagination is usually the wrong question

A large share of COUNT(*) traffic exists only to render "Page 1 of 40,318." If that is your situation, the better fix is to stop counting: switch to keyset pagination and show "next page" instead of a total. That removes both the count query and the deep-OFFSET scan in one move. I wrote up the patterns for both engines separately, and the short version is that neither engine rewards page-number pagination at scale.

If a slow count is showing up in your top queries, treat it like any other regression: confirm it is actually needed, then pick the cheapest tool that satisfies the requirement. On PostgreSQL, slow query monitoring will tell you quickly whether counts are a real cost center or just an occasional annoyance.

How MonPG helps once you are on PostgreSQL

MonPG is PostgreSQL-only, and count problems on PostgreSQL are exactly the kind of slow-drift issue it is built to catch. Query-family history from pg_stat_statements shows when a COUNT(*) family starts dominating total execution time. Table statistics show dead tuples piling up and autovacuum falling behind, which is the hidden reason index-only counts degrade. Index usage data shows whether the covering index you added for counting is actually being used. Instead of rediscovering the visibility map from first principles during an incident, you see the trend as it develops. Start with the PostgreSQL monitoring guide to see how query history and vacuum health fit into one workflow.