Why Adding an Index Won't Fix Your Slow COUNT(*) in PostgreSQL

Ariel Bodan — Mon, 07 Sep 2026 03:15:00 +0000

COUNT(*) looks like a trivial operation:

SELECT COUNT(*)
FROM orders;

The query asks for a single number, but that doesn't mean PostgreSQL can produce it with a constant-time read from some internal counter.

When we need an exact count, PostgreSQL has to determine how many rows are actually part of the visible result set for that query. On large tables, that work can become a meaningful chunk of total execution time.

And the problem doesn't just go away by throwing an index at it.

The useful question isn't "do I have an index?" It's:

How many rows does PostgreSQL actually need to examine to compute this count — and can that work be reduced?

Why COUNT(*) Can Be Expensive in PostgreSQL

PostgreSQL uses MVCC — Multi-Version Concurrency Control — to manage concurrent access to data.

That's what lets multiple transactions work at the same time while each sees a consistent view of the database. But it also means row visibility depends on the snapshot the query is running under.

That's why PostgreSQL can't answer:

SELECT COUNT(*)
FROM orders;

by simply reading an exact counter stored somewhere in the table's metadata.

To return an exact result, it has to process the rows — or an index structure representing those rows — and determine which ones are part of the visible result.

On a small table, that cost is invisible.

On a table with millions of rows, the amount of work starts to matter.

Which leads to an important distinction:

returning a single row from COUNT(*) does not mean processing a single row.

How to Analyze a COUNT with EXPLAIN ANALYZE

Before reaching for an index, it's worth looking at what PostgreSQL is actually doing.

Say we have this query:

SELECT COUNT(*)
FROM orders
WHERE status = 'completed';

We can analyze it with:

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM orders
WHERE status = 'completed';

The goal isn't to hunt for an Index Scan by default. Worth checking instead:

the scan type
estimated rows vs. actual rows processed
rows discarded by the filter
buffer activity
total execution time

PostgreSQL might well choose:

Seq Scan on orders

if it estimates that a sequential read is cheaper than using an index. That doesn't mean the planner made a bad call — it depends entirely on how many rows match the condition.

When an Index Can Actually Improve a COUNT

Say we have 10 million orders, but only 30,000 are pending:

SELECT COUNT(*)
FROM orders
WHERE status = 'pending';

Here, the filter shrinks the working set meaningfully. An index on status could let PostgreSQL skip most of the table:

CREATE INDEX idx_orders_status
ON orders(status);

Depending on statistics, value distribution, and page state, PostgreSQL might use an index-based access path — and under the right conditions, an Index Only Scan.

But it's important not to treat that as a guarantee:

having an index does not guarantee an Index Only Scan.

The planner picks whatever it estimates as the cheapest plan.

Index Only Scan and the Visibility Map

An Index Only Scan can skip a lot of table visits because the values needed to answer the query already live in the index itself.

For a count like:

SELECT COUNT(*)
FROM orders
WHERE status = 'pending';

an index on status holds what's needed to locate the relevant entries. But PostgreSQL still has to respect MVCC's visibility rules — and that's where the visibility map comes in.

PostgreSQL tracks which pages contain only rows that are visible to every relevant transaction. When a page is marked all-visible, an Index Only Scan can skip visiting the heap to check row visibility individually.

This is why it's wrong to think VACUUM "updates the index." The index already stays current as the table changes. What VACUUM actually helps maintain is the visibility information that lets PostgreSQL avoid certain heap visits.

You can check this directly in the plan by looking at:

Heap Fetches: 0

A high Heap Fetches count means PostgreSQL had to check table pages to confirm visibility, even while using an Index Only Scan.

The Real Problem with COUNT and Filters: Selectivity

Now flip the scenario. Say 90% of orders are completed:

SELECT COUNT(*)
FROM orders
WHERE status = 'completed';

In that case, an index on status buys you a lot less. Why? Because even if PostgreSQL can quickly locate every entry where status = completed, those entries make up almost the entire table.

The index isn't eliminating enough work.

That's why PostgreSQL might still prefer a Seq Scan even when there's an index that appears to match the WHERE clause.

The right question was never:

Does an index exist?

It's:

How much does that index actually shrink the set of data PostgreSQL has to process?

Selectivity matters more than the mere existence of an index.

When a Partial Index Makes Sense

Partial indexes shine when you're frequently querying a small, well-defined subset of data.

Say only a small fraction of orders are pending:

SELECT COUNT(*)
FROM orders
WHERE status = 'pending';

We could create:

CREATE INDEX idx_orders_pending
ON orders(id)
WHERE status = 'pending';

That index doesn't contain every order — only the ones matching status = 'pending'. If pending is a small subset, this index can end up considerably smaller than one covering the whole table, and for queries using exactly that predicate, PostgreSQL works with a much leaner structure.

The math changes completely if you build a partial index around a value that represents 90% of the table — you'd still be maintaining an index covering nearly every row, so the potential savings shrink accordingly.

Partial indexes pay off when they mirror a real, sufficiently selective access pattern.

COUNT with Multiple Filters

Counts get more interesting once they combine several conditions:

SELECT COUNT(*)
FROM orders
WHERE account_id = 42
  AND status = 'pending'
  AND created_at >= DATE '2026-09-01';

A single-column index may not be enough here. Worth exploring a composite index:

CREATE INDEX idx_orders_account_status_created
ON orders(account_id, status, created_at);

But don't just copy that pattern without measuring. Column order should reflect how the data is actually filtered, its distribution, and the app's real query patterns.

The right workflow is still:

query
↓
EXPLAIN ANALYZE
↓
rows processed
↓
selectivity
↓
index design
↓
new EXPLAIN ANALYZE

Not:

slow query
↓
create index
↓
hope

When an Index Won't Fix the COUNT

There's a hard limit here. If you genuinely need to count a large chunk of a huge table, PostgreSQL has to process a substantial amount of data to produce an exact result.

An index can change how that data is accessed. It can't make the rows that belong in the count disappear.

If an application is constantly running:

SELECT COUNT(*)
FROM orders;

against a very large table and expects a near-instant response, it's worth asking whether running an exact count on every single request is even the right model. That's where other strategies come in — precomputed counters, approximate counts via pg_class.reltuples, and a practical checklist for diagnosing any slow COUNT.

I go through all of that — plus the full 10-step diagnostic checklist — in the complete write-up on my site.

Curious how others handle this in production: do you maintain precomputed counters, or just accept the cost of an exact COUNT when it matters? Would love to hear how you've dealt with this in your own stack.

DEV Community: Ariel Bodan