PostgreSQL COUNT: why it can be slow and how to optimize it

PostgreSQL COUNT: why it can be slow and how to optimize it#

COUNT(*) looks harmless:

SELECT COUNT(*)
FROM orders;

The query returns a single number, but that does not mean PostgreSQL can answer it by reading a constant-time counter from table metadata.

When you need an exact count, PostgreSQL has to determine how many rows are visible to the current query. On large tables, that work can become a meaningful part of execution time.

The problem is not automatically solved by adding an index either.

The more useful question is:

How many rows does PostgreSQL need to examine to calculate this count, and can we reduce that work?

Why COUNT(*) can be expensive in PostgreSQL#

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

That allows multiple transactions to work at the same time while preserving a consistent view of the database, but it also means row visibility depends on the snapshot used by the current query.

Because of that, PostgreSQL cannot answer:

SELECT COUNT(*)
FROM orders;

by simply reading an exact row counter from metadata.

To return an exact result, it has to process rows, or an index structure representing those rows, and determine which entries belong to the visible result set.

On a small table, the cost may be negligible.

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

That leads to an important distinction:

*returning one row from COUNT() does not mean PostgreSQL processed one row.**

How to analyze a COUNT query with EXPLAIN ANALYZE#

Before creating indexes, inspect what PostgreSQL is actually doing.

Suppose we have this query:

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

Analyze it with:

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

The goal is not to automatically look for an Index Scan.

Instead, review:

• the scan type;
• estimated rows;
• actual rows processed;
• rows removed by the filter;
• buffer activity;
• total execution time.

For example, PostgreSQL may choose:

Seq Scan on orders

if it estimates that reading the table sequentially is cheaper than using an index.

That does not mean the planner made a bad decision.

It depends on how many rows match the condition.

When an index can improve COUNT#

Suppose we have 10 million orders, but only 30,000 of them are pending.

The query is:

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

In this case, the filter narrows the data significantly.

An index on status may allow PostgreSQL to avoid scanning most of the table:

CREATE INDEX idx_orders_status
ON orders(status);

Depending on statistics, value distribution, and page visibility, PostgreSQL may choose an index-based access path.

In some cases, it may also use an Index Only Scan.

But this should not be treated as a rule:

having an index does not guarantee an Index Only Scan.

The planner still chooses the path it estimates to be cheapest.

Index Only Scan and the visibility map#

An Index Only Scan can avoid many heap visits because the values needed by the query are available directly from the index.

For a count such as:

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

an index on status contains the information required to locate matching entries.

PostgreSQL still has to respect MVCC visibility rules.

That is where the visibility map matters.

PostgreSQL tracks whether a heap page contains only tuples that are visible to all relevant transactions. When a page is marked all-visible, an Index Only Scan can avoid visiting that heap page to verify row visibility.

This is why it is misleading to say that VACUUM “updates the index.”

The index is already maintained as the table changes.

What VACUUM helps maintain is visibility information that can allow PostgreSQL to skip heap lookups.

You can inspect this in the execution plan through values such as:

Heap Fetches: 0

A high number of heap fetches means PostgreSQL still had to visit table pages to verify visibility even while using an Index Only Scan.

The real problem with filtered COUNT queries: selectivity#

Now change the scenario.

Suppose 90% of the rows have:

status = completed

and the query is:

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

An index on status may help much less.

Why?

Because even if PostgreSQL can quickly locate entries with status = 'completed', those entries represent almost the entire table.

The index does not eliminate enough work.

This is why PostgreSQL may still prefer a Seq Scan even when a seemingly relevant index exists.

The right question is not:

Does an index exist?

It is:

How much does the index reduce the amount of data PostgreSQL has to process?

Filter selectivity matters more than the mere presence of an index.

When a partial index makes sense#

Partial indexes are especially useful when the application frequently queries a small, well-defined subset of rows.

Suppose only a small percentage of orders are pending:

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

You could create:

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

This index does not contain every order.

It only contains rows where:

status = 'pending'

If pending represents a small subset of the table, the index can also be much smaller than one covering all rows.

For queries using that exact predicate, PostgreSQL may be able to work with a much smaller structure.

The situation is very different if you create a partial index for a value that represents 90% of the table.

In that case, the index still contains almost every row, so the potential benefit is much smaller.

Partial indexes make sense when they represent a real and selective access pattern.

COUNT with multiple filters#

Count queries become more interesting when several conditions are involved:

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.

You might consider a composite index:

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

But this index should not be copied blindly.

Column order should reflect the real filtering pattern, data distribution, and workload.

The process should remain:

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

Not:

slow query
↓
create index
↓
hope

When an index will not solve COUNT#

There is a fundamental limit.

If you genuinely need to count a large portion of a very large table, PostgreSQL still has to process a significant amount of information to produce an exact result.

An index can change how rows are accessed.

It cannot make matching rows disappear.

If an application repeatedly runs:

SELECT COUNT(*)
FROM orders;

against a large table and needs near-instant responses, it is worth asking whether executing an exact count on every request is the right model.

At that point, other strategies become relevant.

Maintaining a precomputed counter#

One option is to store counts separately.

For example:

order_counters
----------------
total_orders
pending_orders
completed_orders

The application updates these values as the underlying data changes.

This turns a large counting operation into a very small read.

The cost moves somewhere else.

Now the system has to keep the counter correct during writes and handle concurrency carefully.

Triggers can also be used to maintain counters, but that adds logic to the write path and should be designed with care.

This is not a free optimization.

It is a trade-off between read cost and maintenance complexity.

Approximate counts with pg_class.reltuples#

Sometimes you do not need to know that a table contains exactly:

10,042,817

rows.

It may be enough to know that it contains roughly 10 million.

PostgreSQL keeps an estimated row count in pg_class.reltuples.

You can query it with:

SELECT reltuples
FROM pg_class
WHERE oid = 'orders'::regclass;

This is not a real-time counter.

It is an estimate maintained through operations that update planner statistics and may differ from the exact row count.

That makes it useful for some dashboards, internal tools, or operational decisions where an approximation is sufficient.

But it does not replace:

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

because reltuples estimates the size of the whole relation, not the exact result of an arbitrary filter.

The right choice depends on the requirement.

If you need transactionally accurate counts, an estimate is not enough.

If the UI only needs to show:

approximately 10 million records

then performing an exact count every time may be unnecessary work.

What to check when COUNT is slow#

When a count query starts degrading, this is a practical starting point:

1. Run EXPLAIN (ANALYZE, BUFFERS).
2. Check how many rows PostgreSQL has to process.
3. Review filter selectivity.
4. Check whether the index actually reduces the search space.
5. Do not assume an index should produce an Index Only Scan.
6. If there is an Index Only Scan, inspect Heap Fetches.
7. Consider partial indexes for small, frequently queried subsets.
8. Consider composite indexes when several filters are involved.
9. Decide whether the result must be exact.
10. If the count runs constantly, consider precomputing it.

The question behind all of these steps is simple:

How much work does PostgreSQL need to do to produce this number?

Conclusion#

Optimizing COUNT in PostgreSQL is not about adding indexes until an Index Only Scan appears.

The cost depends on how many rows belong to the result, how selective the filter is, what the planner knows about the data, and how much work an index can actually avoid.

An exact COUNT(*) on a large table can remain expensive because PostgreSQL still needs to determine which rows belong to the visible result set. When the filter is selective, an index can reduce that work. When the filter matches most of the table, the benefit may be much smaller.

And when the product does not require exact counts on every request, it is worth considering alternatives: precomputed counters or approximate estimates may be more appropriate than repeatedly scanning large datasets.

The process remains the same:

measure → understand how much data is processed → reduce work where possible → measure again.

For a broader diagnostic workflow, see our PostgreSQL query optimization guide, where we cover execution plans, planner statistics, and index design in more depth.