count the rows of top 10 largest size tables in PostgreSQL

Question

in this post, I can get the row count from nominated table.

Fast way to discover the row count of a table in PostgreSQL

SELECT count(*) AS exact_count FROM myschema.mytable;

But how can I list the row count and table size for top 10 biggest tables?

idea is:

1. list all tables
2. get table size for each tables, order and limit to 10 tables with largest table size.
3. count row for these top 10 tables (if hard, can count row for each table)
4. output for table name, table size and its row count for top 10 only

But how can I do it in one sql command?

Answer 1

Just check the statistics about the tables, much easier and faster:

SELECT schemaname
     , relname
     , n_live_tup
     , pg_size_pretty(pg_relation_size(relid)) AS table_size
     , pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_stat_user_tables
ORDER BY pg_relation_size(relid) DESC, n_live_tup DESC
LIMIT 10;