3 回答
TA贡献1809条经验 获得超8个赞
有三种方法可以获得这种计数,每种方法都有自己的权衡。
如果需要真正的计数,则必须像对每个表使用的那样执行SELECT语句。这是因为PostgreSQL将行可见性信息保留在行本身,而不是其他任何地方,因此任何准确的计数只能与某个事务相关。您将获得该事务在执行时所看到的内容的计数。您可以自动执行此操作以针对数据库中的每个表运行,但您可能不需要那么高的准确度或者想要等待那么久。
第二种方法指出,统计信息收集器随时跟踪大约有多少行是“活动的”(未被更新后删除或废弃)。在重度活动下,这个值可能有点偏差,但通常是一个很好的估计:
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;
这也可以显示有多少行已死,这本身就是一个有趣的数字。
第三种方法是注意系统ANALYZE命令,它定期执行autovacuum进程,从PostgreSQL 8.3开始更新表统计信息,也可以计算行估计值。你可以像这样抓住那个:
SELECT nspname AS schemaname,relname,reltuplesFROM pg_class CLEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND relkind='r' ORDER BY reltuples DESC;
哪个查询更好用,很难说。通常我根据是否还有更多有用的信息做出决定,我也想在pg_class或pg_stat_user_tables中使用。出于基本的计数目的,只是为了看一般情况有多大,要么都应该足够准确。
TA贡献1811条经验 获得超4个赞
这是一个解决方案,不需要函数来获得每个表的准确计数:
select table_schema, table_name, (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_countfrom ( 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()
函数。
TA贡献1757条经验 获得超7个赞
要获得估算,请参阅Greg Smith的回答。
为了得到确切的数字,到目前为止的其他答案都存在一些问题,其中一些是严重的(见下文)。这是一个希望更好的版本:
CREATE FUNCTION rowcount_all(schema_name text default 'public') RETURNS table(table_name text, cnt bigint) as$$declare table_name text;begin for table_name in SELECT c.relname FROM pg_class c JOIN pg_namespace s ON (c.relnamespace=s.oid) WHERE c.relkind = 'r' AND s.nspname=schema_name LOOP RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I', table_name, schema_name, table_name); END LOOP;end$$ language plpgsql;
它将模式名称作为参数,或者public
如果没有给出参数。
要在不修改函数的情况下使用特定的模式列表或来自查询的列表,可以在查询中调用它,如下所示:
WITH rc(schema_name,tbl) AS ( select s.n,rowcount_all(s.n) from (values ('schema1'),('schema2')) as s(n))SELECT schema_name,(tbl).* FROM rc;
这将产生一个3列输出,其中包含模式,表和行数。
现在这里是这个函数避免的其他答案中的一些问题:
表格和模式名称不应在没有引用的情况下注入可执行SQL,无论是使用
quote_ident
更现代的format()
函数还是使用其%I
格式字符串。否则,一些恶意的人可能将他们的表命名tablename;DROP TABLE other_table
为完全有效的表名。即使没有SQL注入和有趣的字符问题,表名也可能存在于大小写不同的变体中。如果一个表被命名
ABCD
而另一个被命名abcd
,则SELECT count(*) FROM...
必须使用带引号的名称,否则它将跳过ABCD
并计数abcd
两次。该%I
格式的自动执行此操作。information_schema.tables
除表格外,还列出自定义复合类型,即使table_type为'BASE TABLE'
(!)。因此,我们无法进行迭代information_schema.tables
,否则我们会冒险select count(*) from name_of_composite_type
而且会失败。OTOHpg_class where relkind='r'
应该总能正常工作。COUNT()的类型
bigint
不是int
。可能存在超过21.5亿行的表(虽然对它们运行计数(*)是个坏主意)。不需要为函数创建永久类型以返回具有多个列的结果集。
RETURNS TABLE(definition...)
是一个更好的选择。
添加回答
举报