如何用单条SQL查询PostgreSQL中Top10最大表的行数与表大小

可以用这条单条SQL语句实现需求：

WITH top_10_largest_tables AS (
    SELECT
        table_schema || '.' || table_name AS full_table_name,
        pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS table_size,
        pg_total_relation_size(table_schema || '.' || table_name) AS raw_size
    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema')
    ORDER BY raw_size DESC
    LIMIT 10
)
SELECT
    full_table_name AS table_name,
    table_size,
    (SELECT count(*) FROM (SELECT * FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname || '.' || c.relname = full_table_name) t) AS row_count
FROM top_10_largest_tables;

语句说明： #

• 筛选并排序最大的10张表：通过top_10_largest_tables公共表表达式（CTE），从information_schema.tables中过滤出用户自定义的基表，计算每张表的总大小（含索引和toast表），按大小倒序排列后取前10。这里用pg_total_relation_size获取完整表大小，pg_size_pretty将大小格式化为易读的单位（如MB、GB）。
• 统计每张表的精确行数：在主查询中，通过子查询调用count(*)对筛选出的10张表分别统计精确行数，关联pg_class和pg_namespace来定位对应的表。

如果不需要绝对精确的行数、追求更快的查询速度，可以用reltuples字段获取近似行数，修改后的主查询部分如下：

SELECT
    full_table_name AS table_name,
    table_size,
    (SELECT reltuples::bigint FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname || '.' || c.relname = full_table_name) AS approximate_row_count
FROM top_10_largest_tables;

内容的提问来源于stack exchange，提问作者Bill