Goal Find the base tables that belong to the top 5 in terms of the number of rows in the table. There should be test data in the tables.
Notes The query returns at most five rows. It does not count the number of actual rows but relies on the result of statistics collection process (see the ANALYZE statement). The side effect of running the test is that the statistics of the entire database will be refreshed and it could take some time.
Type General (Overview of some aspect of the database.)
License MIT (opens in new tab)
Data Source system catalog only
SQL Query 
ANALYZE;
WITH ranking_by_number_of_rows AS (SELECT n.nspname AS schema_name, c.relname AS table_name, c.reltuples AS number_of_rows, Dense_rank() OVER (ORDER BY reltuples DESC) AS rank
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_catalog.pg_authid u ON u.oid = c.relowner
WHERE c.relkind IN ('r')AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND (n.nspname = 'public' OR u.rolname <> 'postgres'))
SELECT schema_name, table_name, number_of_rows, rank
FROM ranking_by_number_of_rows 
WHERE rank<=5
ORDER BY number_of_rows DESC, schema_name, table_name;

Collections

This query belongs to the following collections:

Find problems by overview

Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

NameDescription
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Categories

This query is classified under the following categories:

Data at the database logical level

Queries of this category provide information about data in base tables.

NameDescription
Data at the database logical levelQueries of this category provide information about data in base tables.