O'Reilly logo

PostgreSQL 9 Administration Cookbook - Second Edition by Simon Riggs, Gianni Ciolli, Hannu Krosing, Gabriele Bartolini

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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('mytable')*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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required