O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

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

Quick estimate of the number of rows in a table

We don't always want an exactly accurate count of rows, especially on a large table, since 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 by using roughly the same calculation that the Postgres optimizer uses:

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

which gives:

estimated_count
─────────────────
			293
(1 row)

which 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