如何在特定架构中所有表的选择列表中获取总行数和最大(时间戳)列

Question

我们有基于Postgres的只读数据库。 其中，我们在一个模式下有 52 个表。

我们正在尝试为一个架构下的所有表输出行计数和最大（时间戳）列。

环境是 ：

PostgreSQL 8.2.15 （Greenplum Database 4.2.0 build 1） （HAWQ 1.3.0.2 build 14421） on x86_64-unknown-linux-gnu， 由 GCC gcc （GCC） 4.4.2 编译

我们试穿了：

SELECT 
  nspname AS schemaname,relname,reltuples,max(time)
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' 
ORDER BY reltuples DESC;

在此查询中，我们获得了行计数列，但仍未实现所有表的 max（时间戳）。

任何帮助将不胜感激？

Answer 1

使用此查询访问的是数据库统计信息，该统计信息不是 100% 准确，并且可能丢失或过时，具体取决于统计信息收集过程。

若要获取表列表的行计数，必须扫描其中每个表。但是，您可以使用pg_relation_size()来了解以字节为单位的表大小，并且此函数不需要扫描表。

如果您的表列表是静态的，则可以使用如下查询：

select 'table1', count(*), max(time) from table1
union all
select 'table2', count(*), max(time) from table2
union all
...
select 'table52', count(*), max(time) from table52;

此解决方案不灵活，因为如果表列表已更改，则需要重写查询。

第二个选项是生成此查询并手动执行它：

select string_agg(query, ' union all ') as query
    from (
        select 'select ''' || n.nspname || '.' || c.relname || ''', count(*), max(time) from ' || n.nspname || '.' || c.relname as query
            from pg_namespace as n, pg_class as c
            where n.oid = c.relnamespace
                and n.nspname = 'my_schema'
        ) as q;

这更灵活，但是第二个查询应手动执行。

最后是你的最后一个选择 - 为此编写一个函数：

create or replace function table_sizes (schemaname varchar) returns setof record as $BODY$
declare
    r record;
    t varchar;
begin
    for t in execute $$
        select n.nspname || '.' || c.relname
            from pg_namespace as n, pg_class as c
            where n.oid = c.relnamespace
                and c.relkind = 'r'
                and n.nspname = '$$ || schemaname || $$'$$
    loop
        execute 'select ''' || t || '''::varchar, count(*), max(time) from ' || t
            into r;
        return next r;
    end loop;
    return;
end;
$BODY$ language plpgsql volatile;
select * from table_sizes('public') t(tablename varchar, rowcount bigint, maxtime time);

Answer 2

以下是一些其他步骤：

在 psql 中执行以下步骤

o count_per_schema.sql
select 'select count(*)as '||c.relname||', max(time) from ' || n.nspname || '.' || c.relname || ';' as " " from pg_namespace as n, pg_class as c where n.oid = c.relnamespace and c.relkind='r' and n.nspname = 'schema_name';
o
i count_per_schema.sql

o会将结果重定向到您提供filename。例如，count_per_schema.sql 和 i 将从文件运行所有查询。

这是我在服务器中所做的。我没有选择最大（时间）。

yogesh=# o count_per_schema.sql
yogesh=# select 'select count(*)as '||c.relname||' from ' || n.nspname || '.' || c.relname || ';' as " " from pg_namespace as n, pg_class as c where n.oid = c.relnamespace and c.relkind='r' and n.nspname = 'public';
yogesh=# o
yogesh=# i count_per_schema.sql
 heap1
-------
 20000
(1 row)
 test
-------
     4
(1 row)
 users
-------
     0
(1 row)
 skew_demo
-------
 10609
(1 row)