Why COUNT(*) Is So Slow in PostgreSQL (And How to Fix It)

You have a users table with 20 million rows.

You just want to know how many users exist.

Simple, right?

SELECT COUNT(*) FROM users;

But PostgreSQL takes several seconds to return a single number.

Why does counting rows take so long?

The answer is hidden in how PostgreSQL handles transactions.

The Problem: PostgreSQL Can't Just Read a Counter

Imagine your database contains 20 million users.

While you're counting:

• One transaction inserts 100 users.
• Another transaction deletes 50 users.
• A third transaction hasn't committed its changes yet.

So, how many users actually exist?

It depends on which transaction is asking.

PostgreSQL uses something called MVCC (Multi-Version Concurrency Control).

In simple terms, different transactions can see different versions of the same data.

That means PostgreSQL cannot simply maintain one universal row count that is correct for every transaction.

Instead, an exact COUNT(*) must check which rows are visible to your query.

And that's where the performance cost comes from.

Why MySQL Can Be Faster

Here's something interesting.

MySQL's MyISAM storage engine maintains a row count, making an unfiltered COUNT(*) extremely fast.

But there's an important detail.

MySQL's default InnoDB engine also uses MVCC.

Just like PostgreSQL, InnoDB generally needs to scan an index to calculate an exact count.

So PostgreSQL isn't automatically slower than MySQL.

The real difference depends on the storage engine, indexes, table size, and query plan.

Solution 1: Use an Estimated Count

Here's a useful PostgreSQL trick.

If you don't need a perfectly accurate number, you can get an estimate without scanning the entire table.

SELECT reltuples::bigint AS estimated_count
FROM pg_class
WHERE oid = 'public.users'::regclass;

Instead of counting every row, PostgreSQL reads its stored table statistics.

Why is this faster?

Because it's a metadata lookup, not a full-table scan.

The catch?

It's an estimate.

If thousands of rows were recently inserted or deleted, the number might be outdated.

Running ANALYZE helps refresh the statistics:

ANALYZE users;

Even then, it's still an estimate, not an exact count.

Perfect for: Dashboard totals, approximate pagination counts, and analytics where a small difference is acceptable.

Solution 2: Maintain Your Own Counter

What if you need an exact count frequently?

You can maintain a separate counter table.

CREATE TABLE table_counts (
  table_name TEXT PRIMARY KEY,
  row_count BIGINT NOT NULL
);

Initialize it using an exact count:

INSERT INTO table_counts
VALUES ('users', (SELECT COUNT(*) FROM users));

Then update the counter whenever users are inserted or deleted.

You can automate this with PostgreSQL triggers.

Now reading the count is simple:

SELECT row_count
FROM table_counts
WHERE table_name = 'users';

No need to scan millions of users.

However, there's a trade-off.

Every insert or delete must update the counter, which adds write overhead. A single counter row can also become a bottleneck under heavy concurrent writes.

This approach works best when reads are frequent and writes are relatively low.

Solution 3: Don't Count Everything for Pagination

Here's a common mistake in web applications.

Every time someone opens a paginated list, the backend executes:

SELECT COUNT(*) FROM users;

Just to display:

Page 1 of 200,000

But do users really need to know the exact number of pages?

Often, they don't.

Instead, fetch one extra record:

SELECT *
FROM users
ORDER BY id
LIMIT 21;

If your page size is 20 and you receive 21 records, you know there's another page.

Return the first 20 records and show a Next button.

For large datasets, combine this with keyset pagination:

SELECT *
FROM users
WHERE id > 1000
ORDER BY id
LIMIT 21;

This avoids repeatedly counting the entire table and can also avoid the cost of large OFFSET values.

Which Approach Should You Use?

Requirement	Best approach
Exact count, occasionally	COUNT(*)
Fast approximate count	pg_class.reltuples
Exact count, frequently	Maintained counter, if write contention is manageable
Large paginated lists	Keyset pagination
Dashboard statistics	Estimated or cached counts

One More Thing: COUNT(*) Isn't Always a Full Table Scan

PostgreSQL can sometimes use an Index Only Scan instead of scanning the table itself.

But it still needs to examine index entries and may need heap visibility checks.

An index-only scan doesn't automatically make an exact count constant-time.

You can inspect what PostgreSQL is doing:

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

This shows the execution plan, actual execution time, and buffer usage.

It's always better to measure before optimizing.

Final Thoughts

COUNT(*) isn't slow because PostgreSQL is poorly designed.

It's the cost of providing an accurate result while supporting concurrent transactions.

The important lesson is simple:

Don't calculate an exact count when an estimate or a simple "has more" check is enough.

Sometimes the fastest database query is the one you don't need to execute.