如何在PostgreSQL 9.5中获取特定模式中所有表的行数?我希望结果以表名 | 行数的形式呈现。请问如何使用查询实现此功能?
这可以通过一些XML魔法来实现:
select table_schema, table_name,
(xpath('/row/count/text()', query_to_xml('select count(*) from '||format('%I.%I', table_schema, table_name), true, true, '')))[1]::text::int as row_count
from information_schema.tables
where table_schema = 'public'
https://www.postgresql.org/docs/current/static/monitoring-stats.html
n_live_tup 估计的活动行数
t=# select relname,n_live_tup
from pg_stat_all_tables
where schemaname = 'public'
order by n_live_tup desc
limit 3;
relname | n_live_tup
------------+---------------------+------------
x_pricing | 96493977
x_forum | 57696510
x_uploading | 55477043
(3 rows)