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…
The Postgres optimizer can provide a quick estimate of the number of rows in a table, simply by using its statistics:
EXPLAIN SELECT * FROM mytable;
QUERY PLAN
----------------------------------------------------------------
Seq Scan on mytable (cost=0.00..2640.00 rows=100000 width=97)
(1 row)
You can see that the optimizer estimates 100,000 rows in output. We can directly compute a similar number using roughly the same calculation:
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&apos...
