显示所有非空表格的Postgres

Question

有没有一种简单的 PostgreSQL 或者 SQL 方法可以列出空/非空表格？

P.S.：我正在分析一个包含数百个表格的数据库，并希望检测“死代码”。 我假设当表格在某些月份之后仍然为空时，它就没有被使用。

编辑：已解决

谢谢大家！最终，这个语句似乎输出了我可以使用的统计信息：

select schemaname, relname, n_tup_ins from pg_stat_all_tables WHERE schemaname = 'public' ORDER BY n_tup_ins 

- Valentin H

---

你想查看特定日期未使用的表格或空表格吗？ - stylishCoder

如果因为合理的原因删除了表中的所有行，会发生什么？比如有任务，所有任务都成功完成，然后从表中删除。如果您删除了表并且有人尝试创建新任务，会发生什么？ - Lajos Arpad

@user1474602：空表是指那些从未被写入过的表。 - Valentin H

@LajosArpad：我没有说过我会删除表格。只是想知道应该关注哪些表格。 - Valentin H

是的，从 pg_stat_all_tables 获取信息似乎是一个不错的方法：PostgreSQL 的统计收集器是一个子系统，支持收集和报告有关服务器活动的信息。目前，该收集器可以计算磁盘块和单个行术语中对表和索引的访问次数。它还跟踪每个表中的总行数，以及每个表的清理和分析操作的信息...https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW - kenichi

Answer 1

你可以使用 PostgreSQL 的系统目录，例如：

SELECT n.nspname, c.relname
FROM pg_class c
INNER JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.reltuples = 0 AND c.relkind = 'r';

根据文档，行数是一个估计值。

如果您的表中有列从序列获得默认值，您可以列出它们并使用nextval检查它们的值。(不幸的是，currval返回一个会话相关的值，因此您必须确保没有其他人在使用该数据库，并同时使用nextval和setval。)

SELECT n.nspname, c.relname
FROM pg_class c
INNER JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind = 'S';

很遗憾，我还没有找到确定哪个序列属于哪个表格的方法。显然这将非常有用。但是，您可以使用pg_class.relnamespace来缩小结果范围。

详情请参阅http://www.postgresql.org/docs/9.3/interactive/catalogs-overview.html。

- tsnorri

Answer 2

检查行数可能会导致错误的结果。假设一个表被用作暂存表：行被插入（例如从平面文件中），处理并删除。如果您检查该表中的行数，如果您没有在处理过程进行查询，则很可能认为它从未被使用。

另一种检测“未使用”表的方法是监视对表进行的IO和更改。

统计视图pg_stat_user_tables记录系统中每个表的更改（删除、插入、更新）。统计视图pg_statio_user_tables记录针对表执行的IO。

如果您定期对这些表进行快照，则可以计算值之间的差异，并查看是否根本使用了表。

您可以使用pg_stat_reset()将所有值重置为零，然后从那里开始。

- user330315

Answer 3

你可以做到这一点

CREATE OR REPLACE FUNCTION fn_table()
  RETURNS TABLE(name text,count int) AS
$BODY$
DECLARE
    data record;
    v_sql text;
BEGIN
    DROP TABLE IF EXISTS demo;
    CREATE TEMP TABLE demo (name text,count int);
    FOR data in (SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'public')    LOOP
        v_sql := 'INSERT INTO demo SELECT '''||data.table_name||''', COUNT(*) FROM '||data.table_name;
        RAISE INFO 'v_sql:%',v_sql;
        EXECUTE v_sql;
    END LOOP;
    RETURN QUERY (SELECT * FROM demo);
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

输出：

"child";0
"location_master";6
"location";5
"tmp";5
"a1";7
"b1";3
"master";0
"child2";0
"child1";0

这里的child、master、child1、child2表的计数为0，表示它们是空表。

- Ilesh Patel

---

谢谢。但是很不幸，没有找到任何行。我刚刚意识到我做了一件非常愚蠢的事情。在生产数据库上运行包含创建/删除的查询。我希望我没有破坏任何东西。 - Valentin H

不，这会创建一个临时表并删除它。没有其他需要担心的事情。 - Ilesh Patel

Answer 4

请试一试，希望能对你有帮助：

Running the query below will give you a list of tables and indexes that have not been used since SQL Server was last restarted. Once you have a list of tables, you can do a dependency check to get a list of stored procedures that use each table. Then you can search your C# source code for thos SPs and table names.



-- Unused tables & indexes. Tables have index_id’s of either 0 = Heap table or 1 = Clustered Index

SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID

FROM SYS.INDEXES AS I

INNER JOIN SYS.OBJECTS AS O

ON I.OBJECT_ID = O.OBJECT_ID

WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

AND I.INDEX_ID

NOT IN (SELECT S.INDEX_ID

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

WHERE S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = DB_ID(db_name()))

ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC

- stylishCoder

---

谢谢您的快速回复。不幸的是，我收到了“错误：模式“sys”不存在”的消息。 - Valentin H

需要稍微优化一下代码，我觉得是这样的，因为我用了这个，所以才发布了相同的内容。 - stylishCoder

@ValentinHeinitz 我已经检查过了，它完美地工作着。请你也检查一下，包括其他数据库。 - stylishCoder

该查询不适用于Postgres（看起来像是SQL Server / T-SQL查询） - user330315

这是一个“SQL Server”查询。SQL只是一种查询语言，它不是DBMS产品。标签sql-server用于SQL Server特定的查询。标签sql仅指一般的SQL查询语言。 - user330315