我正在寻找一种方法,为我在Postgres中所有的表找到行数。我知道我可以一次做一张表:
SELECT count(*) FROM table_name;
但我想看看所有表的行数,然后按它排序,以了解所有表的大小。
我正在寻找一种方法,为我在Postgres中所有的表找到行数。我知道我可以一次做一张表:
SELECT count(*) FROM table_name;
但我想看看所有表的行数,然后按它排序,以了解所有表的大小。
当前回答
下面的查询将给出每个表的行数和大小
选择table_schema, table_name, pg_relation_size('“' | | table_schema | |”“。”“| | table_name | |”“”)/ 1024/1024 size_MB, (xpath('/row/c/text()', query_to_xml(format('select count(*) AS c from %I.)%I', table_schema, table_name), false, true, ")))[1]::text::int AS rows_n 从information_schema.tables order by size_MB
其他回答
我想从所有表的总数+表的列表与他们的计数。有点像绩效表,显示大部分时间都花在了哪里
WITH results AS (
SELECT nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
nspname NOT IN ('pg_catalog', 'information_schema') AND
relkind='r'
GROUP BY schemaname, relname, reltuples
)
SELECT * FROM results
UNION
SELECT 'all' AS schemaname, 'all' AS relname, SUM(reltuples) AS "reltuples" FROM results
ORDER BY reltuples DESC
当然,你也可以在这个版本的结果上加上一个LIMIT条款,这样你就可以得到最大的n个违例者以及总数。
需要注意的一点是,在大量进口后,您需要让它静置一段时间。我通过跨几个表向数据库中添加5000行(使用实际导入数据)来测试这一点。它显示了大约一分钟的1800条记录(可能是一个可配置的窗口)
这是基于https://stackoverflow.com/a/2611745/1548557的工作,所以感谢并认可在CTE中使用的查询
对于那些试图评估他们需要哪一个Heroku计划,又不能等待Heroku的慢行计数器刷新的人来说,一个简单实用的答案是:
基本上你想在psql中运行\dt,将结果复制到你最喜欢的文本编辑器中(它看起来像这样:
public | auth_group | table | axrsosvelhutvw
public | auth_group_permissions | table | axrsosvelhutvw
public | auth_permission | table | axrsosvelhutvw
public | auth_user | table | axrsosvelhutvw
public | auth_user_groups | table | axrsosvelhutvw
public | auth_user_user_permissions | table | axrsosvelhutvw
public | background_task | table | axrsosvelhutvw
public | django_admin_log | table | axrsosvelhutvw
public | django_content_type | table | axrsosvelhutvw
public | django_migrations | table | axrsosvelhutvw
public | django_session | table | axrsosvelhutvw
public | exercises_assignment | table | axrsosvelhutvw
),然后运行regex搜索并替换,如下所示:
^[^|]*\|\s+([^|]*?)\s+\| table \|.*$
to:
select '\1', count(*) from \1 union/g
这将会给你一个非常类似的结果:
select 'auth_group', count(*) from auth_group union
select 'auth_group_permissions', count(*) from auth_group_permissions union
select 'auth_permission', count(*) from auth_permission union
select 'auth_user', count(*) from auth_user union
select 'auth_user_groups', count(*) from auth_user_groups union
select 'auth_user_user_permissions', count(*) from auth_user_user_permissions union
select 'background_task', count(*) from background_task union
select 'django_admin_log', count(*) from django_admin_log union
select 'django_content_type', count(*) from django_content_type union
select 'django_migrations', count(*) from django_migrations union
select 'django_session', count(*) from django_session
;
(您需要删除最后一个联合,并手动在末尾添加分号)
在psql中运行它,就完成了。
?column? | count
--------------------------------+-------
auth_group_permissions | 0
auth_user_user_permissions | 0
django_session | 1306
django_content_type | 17
auth_user_groups | 162
django_admin_log | 9106
django_migrations | 19
[..]
您可以使用此查询生成所有表名及其计数
select ' select '''|| tablename ||''', count(*) from ' || tablename ||'
union' from pg_tables where schemaname='public';
上述查询的结果将是
select 'dim_date', count(*) from dim_date union
select 'dim_store', count(*) from dim_store union
select 'dim_product', count(*) from dim_product union
select 'dim_employee', count(*) from dim_employee union
您需要删除最后一个联合符,并在末尾添加分号!!
select 'dim_date', count(*) from dim_date union
select 'dim_store', count(*) from dim_store union
select 'dim_product', count(*) from dim_product union
select 'dim_employee', count(*) from dim_employee **;**
跑! !
摘自我在GregSmith的回答中的评论,使其更具可读性:
with tbl as (
SELECT table_schema,table_name
FROM information_schema.tables
WHERE table_name not like 'pg_%' AND table_schema IN ('public')
)
SELECT
table_schema,
table_name,
(xpath('/row/c/text()',
query_to_xml(format('select count(*) AS c from %I.%I', table_schema, table_name),
false,
true,
'')))[1]::text::int AS rows_n
FROM tbl ORDER BY 3 DESC;
感谢@ a_horis_with_no_name
下面是一个解决方案,它不需要函数来获得每个表的精确计数:
select table_schema,
table_name,
(xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
select table_name, table_schema,
query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
from information_schema.tables
where table_schema = 'public' --<< change here for the schema you want
) t
query_to_xml将运行传递的SQL查询并返回带有结果的XML(该表的行数)。外层xpath()将从该xml中提取计数信息并将其转换为数字
实际上并不需要派生表,但可以使xpath()更容易理解——否则整个query_to_xml()将需要传递给xpath()函数。