PostgreSQL 查询所有表的记录数

今天药忘吃喽~ 2022-12-14 03:57 662阅读 0赞

### 方法一：（查询出来只是一个估计数值，与实际数据量不太一致） ###

select relname as 表名, reltuples as 记录数 
    from pg_class 
    where relkind = 'r' 
    and relnamespace = (select oid from pg_namespace where nspname='指定schema') order by 记录数 desc;

实例：

select relname as 表名, reltuples as 记录数 
    from pg_class 
    where relkind = 'r' 
    and relnamespace = (select oid from pg_namespace where nspname='npas') order by 记录数 desc;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70][]

但感觉查询出来的记录与实际相比不太一致

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70 1][]

![20201013103040544.png][]

![20201013103033842.png][]

43423432432

方法二：

SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables 
    where schemaname='npas'
    ORDER BY n_live_tup DESC;

参考资料：

[PostgreSQL 表 行数统计][PostgreSQL _]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70]: /images/20221123/202f0fbb943f42e8bac549fec9cce5ff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70 1]: /images/20221123/5d8eeb660f8f472d98f07b98d1c90a5e.png
[20201013103040544.png]: /images/20221123/a6b42d7512624e9bad96035029037b2f.png
[20201013103033842.png]: /images/20221123/bbea6cc1936a4c4892609d475a9c3e9b.png
[PostgreSQL _]: https://blog.csdn.net/londa/article/details/109046391