PostgreSQL表的行数统计

现在的位置: 首页 > 综合 > 正文

PostgreSQL表的行数统计

2013年06月30日 ⁄ 综合 ⁄ 共 3112字 ⁄ 字号小中大 ⁄ 评论关闭

PostgreSQL表的行数统计

在很多情况下我们需要知道一个表的记录数有多少。如果你发现你有这样的需求，你还应该问问这样的统计的精确度到底又多高。如果你在做会计报表，你需要非常的精确。如果你做一个网页的记数器，可能有一些误差也是允许的。

使用count(*)

传统的计算一个表的行数的方法是使用count(*),但是count(*)非常的慢，尤其是对于一个大表而言。
webstat=# select count(*) from rawlog;
    count
    ---------
     2058704
    (1 row)

Time: 7202.873 ms
从上边的查询可以看出，count(*)的速度是非常的慢的，因此你应当尽可能的避免使用count(*),
但是它仍然是最精确的一种方法。

使用系统表

count(*)的一种替代方法是通过查找pg_class系统表获取大致的行数。这个数值每次vacuum之后
变化。你统计的行数的误差在vacuum之间删除或者插入的行数，如果你统计的行数允许这样的差值，
这种方法就是你最佳的选择。记住，别使用这种方法在会计统计上。当你发出的vacuum越频繁，则
你所得到的行数就越精确。

这个数值存储在pg_class的reltuples字段里边，下边的查询语句列出了public模式下的所有表
的行数：

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

对象的类型是表类型('r')，模式是public。相应的如果我们想看一个表的行记录数，我们可以
使用如下的语句:

SELECT reltuples
FROM pg_class r
WHERE relkind = 'r' AND relname = 'mytable';

使用触发器

如果你必须得到精确的记录数，而又不想使用count(*)的话，那么你可以考虑使用触发器来维护
表的记录数。这个办法需要创建一个INSERT TRIGGER来增加数量以及一个DELETE TRIGGER 来减少
数量。具体的数量可以存储在一个单独的表中。

创建一个row_counts表，row_counts表包含一个表名称字段：relname，一个行记录数字段：
reltuples。首先你需要创建表，然后创建触发器，最后初试化表的记录数。

CREATE TABLE row_counts (
relname text PRIMARY KEY,
reltuples numeric);

我写了一个触发器函数来处理表的Insert和Delete事件，我们可以很容易的通过TG_OP来判断操作
类型，TG_RELNAME来获取表的名称。这两个都是触发器的特殊变量。

CREATE OR REPLACE FUNCTION count_trig()
RETURNS TRIGGER AS
$$
    DECLARE
    BEGIN
    IF TG_OP = 'INSERT' THEN
          EXECUTE 'UPDATE row_counts set reltuples=reltuples +1 where relname = ''' || TG_RELNAME || '''';
          RETURN NEW;
       ELSIF TG_OP = 'DELETE' THEN
          EXECUTE 'UPDATE row_counts set reltuples=reltuples -1 where relname = ''' || TG_RELNAME || '''';
          RETURN OLD;
       END IF;
    END;
$$
LANGUAGE 'plpgsql';

同样我也只写了一个函数来把所以的表上加上这个触发器。你可以不需要这样做。如果是这样的话，你可以
写一个相同的函数来删除触发器。

CREATE OR REPLACE FUNCTION add_count_trigs()
RETURNS void AS
$$
       DECLARE
          rec   RECORD;
          q     text;
       BEGIN
          FOR rec IN SELECT relname
                   FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid)
                   WHERE relkind = 'r' AND n.nspname = 'public' LOOP
             q := 'CREATE TRIGGER ' || rec.relname || '_count BEFORE INSERT OR DELETE ON ' ;
             q := q || rec.relname || ' FOR EACH ROW EXECUTE PROCEDURE count_trig()';
             EXECUTE q;
          END LOOP;
       RETURN;
       END;
$$
LANGUAGE 'plpgsql';

发出vacuum语句之后使用如下语句初试化表的记录数：

insert into row_counts select relname, reltuples from pg_class;

可能还会存在一些错误，如任何在vacuum和创建触发器之间完成的事物都将忽略掉，为了精确的统计，你需要
停止服务器上的所有活动。

即便你可以在每个表上运行vacuum，但是有的时候如果你不确定vacuum是否运行，你可以写个函数来完成相似
的功能。这个函数要比vacuum慢，而且如果你的数据库活动比较频繁的话也会有一些误差。

CREATE OR REPLACE FUNCTION init_row_counts()
RETURNS void AS
$$
       DECLARE
          rec   RECORD;
          crec RECORD;
       BEGIN
          FOR rec IN SELECT relname
                   FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid)
                   WHERE relkind = 'r' AND n.nspname = 'public' LOOP
             FOR crec IN EXECUTE 'SELECT count(*) as rows from '|| rec.relname LOOP
                -- nothing here, move along
             END LOOP;
             INSERT INTO row_counts values (rec.relname, crec.rows) ;
          END LOOP;

       RETURN;
       END;
$$
LANGUAGE 'plpgsql';