Estimated row count of every table (PostgreSQL)

PostgreSQLRead onlyScripts Gallery

Row counts of every table of a schema, read from PostgreSQL's statistics instead of counting each table.

Verification status. Written from the BroadSQL documentation and the PostgreSQL documentation. It has not been run by the BroadSQL team: run it first on a test database, and read its result critically.

When to use it

Size up an unfamiliar database in one query.
Spot a table that grew or emptied unexpectedly.
Keep a weekly snapshot with DUMP LIB.

The Script

Download pg-table-row-estimates.sql

-- @description: Estimated row count of every table of a schema (PostgreSQL statistics)
-- @tags: postgresql, diagnostics, size, rows
-- @status: draft
-- @access: read
-- @dialect: PostgreSQL
-- @param schema_name TEXT = 'public' "Schema to list"
SELECT relname AS table_name, n_live_tup AS estimated_rows, last_autoanalyze, last_analyze
  FROM pg_stat_user_tables
 WHERE schemaname = ${schema_name}
 ORDER BY n_live_tup DESC;

Parameters

Parameter	Type	Default	Meaning
`schema_name`	`TEXT`	`'public'`	Schema to list

Install and run it

Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/pg-table-row-estimates.sql.
Connect to the PostgreSQL database to inspect.
Check what it declares: LIB SHOW gallery/pg-table-row-estimates.sql;
Run it: @gallery/pg-table-row-estimates.sql schema_name='public';

To keep the result in a file: DUMP LIB gallery/pg-table-row-estimates.sql TO row_counts.WEEKLY AS XLSX MODE APPEND; (see Exporting Script results).

What you get

One row per table of the schema: its name, the estimated number of live rows (n_live_tup), and when it was last analyzed.

Limits

n_live_tup is an estimate kept by PostgreSQL's statistics, not an exact count: use CNT <table> for an exact one.
Only the tables of the given schema are listed.
Written from the PostgreSQL statistics documentation.

Found something wrong or unclear on this page? Tell us, with the page address.