Quickly estimating the number of rows in a table

We don't always need an accurate count of rows, especially on a large table that may take a long time to execute. Administrators often need to estimate how big a table is so that they can estimate how long other operations may take.

How to do it…

We can get a quick estimate of the number of rows in a table using roughly the same calculation that the Postgres optimizer uses:

SELECT (CASE WHEN reltuples > 0 THEN
pg_relation_size(oid)*reltuples/(8192*relpages)
ELSE 0
END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = 'mytable'::regclass;

This gives us the following output:

estimated_count
---------------------
             293
(1 row)

It returns a row count very quickly, no matter how large the table that we are examining is.

How it works…

We saw the pg_relation_size() function earlier, so we know it brings back an accurate value for the current size of the table.

When we vacuum a table in Postgres, we record two pieces of information in...