Postgres Count(*) Made Fast

Sometimes the best solution is to look for an alternative.

Often an approximation is good enough and you don’t need the exact count. In that case you can use the estimate that PostgreSQL uses for query planning:

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';

This value is updated by both autovacuum and autoanalyze, so it should never be much more than 10% off. You can reduce <span style="color: #282f3a;">autovacuum_analyze_scale_factor</span> for that table so that autoanalyze runs more often there.

Estimate

Obviously the only way to get an exact answer to this is to execute the query. But if an estimate is good enough, you can use PostgreSQL’s optimizer to get it for you.

The following simple function uses dynamic SQL and EXPLAIN to get the execution plan for the query passed as argument and returns the row count estimate:

1
2
3
4
5
6
7
8
9
CREATE FUNCTION row_estimator(query text) RETURNS bigint
<span style="color: #282f3a;">   </span>LANGUAGE plpgsql AS
$$DECLARE
<span style="color: #282f3a;">   </span>plan jsonb;
BEGIN
<span style="color: #282f3a;">   </span>EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
<span style="color: #282f3a;">   </span>RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;

Do not use this function to process untrusted SQL statements, since it is by nature vulnerable to SQL injection.