PostgreSQL表的行数统计

待我称王封你为后i 2022-08-09 18:57 366阅读 0赞

**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

DECLAREBEGINIFTGOP=′INSERT′THENEXECUTE′UPDATErowcountssetreltuples=reltuples\+1whererelname=′′′||TGRELNAME||′′′′;RETURNNEW;ELSIFTGOP=′DELETE′THENEXECUTE′UPDATErowcountssetreltuples=reltuples−1whererelname=′′′||TGRELNAME||′′′′;RETURNOLD;ENDIF;END;

LANGUAGE 'plpgsql';  
  
同样我也只写了一个函数来把所以的表上加上这个触发器。你可以不需要这样做。如果是这样的话，你可以  
写一个相同的函数来删除触发器。  
  
CREATE OR REPLACE FUNCTION add\_count\_trigs()  
RETURNS void AS

DECLARErecRECORD;qtext;BEGINFORrecINSELECTrelnameFROMpgclassrJOINpgnamespacenON(relnamespace=n.oid)WHERErelkind=′r′ANDn.nspname=′public′LOOPq:=′CREATETRIGGER′||rec.relname||′countBEFOREINSERTORDELETEON′;q:=q||rec.relname||′FOREACHROWEXECUTEPROCEDUREcounttrig()′;EXECUTEq;ENDLOOP;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

DECLARErecRECORD;crecRECORD;BEGINFORrecINSELECTrelnameFROMpgclassrJOINpgnamespacenON(relnamespace=n.oid)WHERErelkind=′r′ANDn.nspname=′public′LOOPFORcrecINEXECUTE′SELECTcount(∗)asrowsfrom′||rec.relnameLOOP−−nothinghere,movealongENDLOOP;INSERTINTOrowcountsvalues(rec.relname,crec.rows);ENDLOOP;RETURN;END;

LANGUAGE 'plpgsql';  
  
这个函数从pg\_class里边查找所有的表，对每个表使用count(\*)获取记录数。把上边的放在一块，下边  
列出来了操作顺序：  
  
创建记录行数的表。  
创建触发器函数。  
如果可能停止服务器的活动。  
Vacuum表。  
在一个事物里边，在表上添加触发器，初试化记录数。  
  
这样从次以后，你可以通过查询行记录数的表来或者相应表的记录数。