PostgreSQL 11 Administration Cookbook

How to do it…

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

SELECT (CASE WHEN reltuples > 0 THEN pg_relation_size(oid)*reltuples/(8192*relpages) ELSE 0END)::bigint AS estimated_row_countFROM pg_classWHERE 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. You may want to create a SQL function for the preceding calculation, so you won't need to retype the SQL code every now and then.

The following function estimates the total number of rows using a mathematical procedure called extrapolation. In other words, we ...