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.)
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:
Name
Description
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 .
Categories
This query is classified under the following categories:
Name
Description
Data at the database logical level
Queries of this category provide information about data in base tables.