Why is SELECT COUNT(*) so slow in PostgreSQL?

Do you need an exact number?

Usually not. "About 1.2 million results" is what a UI needs, and an estimate is free.

Whole-table estimate from the planner statistics:

SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE oid = 'public.events'::regclass;

It's updated by VACUUM, ANALYZE and CREATE INDEX, so it's usually within a few percent. On PostgreSQL 14+ it's -1 for a table that has never been vacuumed or analyzed.

Estimate for a filtered query by asking the planner:

CREATE FUNCTION count_estimate(query text) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE plan jsonb;
BEGIN
  EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
  RETURN (plan -> 0 -> 'Plan' ->> 'Plan Rows')::bigint;
END $$;

SELECT count_estimate('SELECT 1 FROM events WHERE type = ''click''');

Planner estimates can be off for complex filters, so it's fine for "≈ 48,000 results" but not for billing.

A hybrid that works well in UIs: show the exact count if it's small, otherwise the estimate:

SELECT count(*) FROM (SELECT 1 FROM events WHERE type = 'click' LIMIT 1001) s;
-- 1001 means "1,000+": fall back to the estimate

When you need exact and fast

Keep the count yourself:

CREATE TABLE row_counts (table_name text PRIMARY KEY, n bigint NOT NULL);

CREATE FUNCTION events_count() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE row_counts SET n = n + 1 WHERE table_name = 'events';
  ELSE
    UPDATE row_counts SET n = n - 1 WHERE table_name = 'events';
  END IF;
  RETURN NULL;
END $$;

CREATE TRIGGER events_count AFTER INSERT OR DELETE ON events
FOR EACH ROW EXECUTE FUNCTION events_count();

This is exact and transactional, but every insert now updates one hot row, which serializes concurrent writers. For high write rates, spread the counter across N slot rows (slot = random() * N) and sum them on read, or maintain counts asynchronously from a queue or statement-level triggers with transition tables.

For distinct counts at scale (unique visitors), consider HyperLogLog (the hll extension) for estimates with about 1–2% error in a few kilobytes.

Checklist

1. MVCC means there's no stored row count, so count(*) has to check visibility.
2. Keep the table well vacuumed so index-only scans can count without heap fetches.
3. Use pg_class.reltuples or EXPLAIN estimates when "about" is good enough.
4. Cap counts with LIMIT for UIs, and use counter tables only when exact and fast are both required.

EXPLAIN ANALYZE in PostgreSQL: reading plans without guessing

Learn to read PostgreSQL query plans node by node — estimates vs. actuals, loops, buffers — and turn them into index and query fixes.

#execution-plans#indexing#performance