PostgreSQL

Postgres Slow COUNT Query on Large Table, Fast Alternatives

A slow COUNT(*) on a large PostgreSQL table isn't a missing-index problem in the usual sense — it's a fundamental consequence of how PostgreSQL's MVCC (multi-version concurrency control) storage model works, which requires checking row visibility individually rather than reading a simple pre-computed total.

The Problem

A simple counting query takes far longer than expected on a large table:

SELECT COUNT(*) FROM orders;
-- takes 8+ seconds on a table with 50 million rows

Adding an index on the table doesn't meaningfully speed this up, which is often the first (and unsuccessful) thing people try, since it seems intuitive that an index should help with counting.

Why It Happens

Unlike some other databases, PostgreSQL doesn't maintain a readily available, always-accurate total row count for a table. Because of MVCC, different transactions can see different subsets of rows as "visible" at any given moment (due to concurrent inserts, updates, and deletes not yet committed or still visible to older transactions) — an index alone can't resolve this ambiguity, since PostgreSQL still needs to check each row's actual visibility to the current transaction individually. This means:

  • An exact COUNT(*) genuinely requires scanning through the relevant rows (or at least their visibility information) to determine, for the current transaction, exactly how many are actually visible right now
  • Adding a WHERE clause can help by using an index to narrow which rows need checking, but an unfiltered COUNT(*) over an entire large table has no such narrowing to take advantage of
  • Even an index-only scan (which can technically avoid touching the full table data) still needs to verify row visibility against the current transaction, which prevents it from being as fast as simply reading a pre-computed number

The Fix

If you need an exact, real-time count and the query genuinely must include a filtering condition, ensure that condition is well-indexed, which at least narrows the number of rows PostgreSQL has to individually check for visibility:

CREATE INDEX idx_orders_status ON orders(status);
SELECT COUNT(*) FROM orders WHERE status = 'pending';

For an unfiltered count of an entire large table where you don't need a perfectly exact, real-time number, use PostgreSQL's own internal statistics as a fast approximation instead:

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

This returns an estimate based on the table's statistics, updated whenever ANALYZE last ran (either manually or via autovacuum) — it's not perfectly exact if significant inserts/deletes happened since the last analyze, but it's dramatically faster since it involves no actual row scanning at all, just reading a pre-computed statistic.

If you need a genuinely exact count but can tolerate it being computed periodically rather than in real time, maintain a separate counter table updated via triggers, keeping an always-current count without needing to scan the full table on every read:

CREATE TABLE order_counts (status TEXT PRIMARY KEY, count BIGINT);

CREATE OR REPLACE FUNCTION update_order_count() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE order_counts SET count = count + 1 WHERE status = NEW.status;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE order_counts SET count = count - 1 WHERE status = OLD.status;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_count_trigger
AFTER INSERT OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_order_count();

This trades a small amount of write-time overhead (updating the counter on every insert/delete) for consistently fast reads of an always-current count, which is often a worthwhile tradeoff for a count that's read far more frequently than the underlying table is modified.

For pagination specifically — a very common reason people need a count in the first place, to show "page 1 of N" — consider whether an exact total is genuinely necessary at all, versus simply showing "next page" availability based on whether more rows exist beyond the current page, which avoids needing any count operation at all:

SELECT * FROM orders ORDER BY id LIMIT 21; -- fetch one extra row
-- if 21 rows returned, there's a next page; only display the first 20

Still Not Working?

If the approximate reltuples estimate is significantly inaccurate for your table (a strong sign that autovacuum isn't running frequently enough, or statistics are stale for another reason), run ANALYZE manually to refresh the statistics before relying on the estimate, and consider whether your autovacuum settings need tuning for this specific high-write table if staleness is a recurring issue:

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

You might also like