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';


  这个函数从pg_class里边查找所有的表,对每个表使用count(*)获取记录数。把上边的放在一块,下边

列出来了操作顺序:


   创建记录行数的表。

   创建触发器函数。

   如果可能停止服务器的活动。

   Vacuum表。

   在一个事物里边,在表上添加触发器,初试化记录数。


  这样从次以后,你可以通过查询行记录数的表来或者相应表的记录数。