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表。
在一个事物里边,在表上添加触发器,初试化记录数。
这样从次以后,你可以通过查询行记录数的表来或者相应表的记录数。
原文地址:
http://www.varlena.com/varlena/GeneralBits/120.php
分享到:
相关推荐
PostgreSQL表分区和子表及删除所有的数据库表 最近需求要求统计DNS近7天每天的解析情况。数据量相对大,所以我这边对表进行分区。 对每天的数据进行分区存储。主表只存储近7天的数据,7天之前的数据删掉。所以我...
aida 运用postgresql进行数据统计
2014年PostgreSQL中国大会华为许中清分享的原生表分区方案
postgresql临时表postgresql临时表postgresql临时表postgresql临时表
PostgreSQL表分区功能演进 PostgreSQL表分区功能演进 PostgreSQL表分区功能演进 PostgreSQL表分区功能演进
项目中有需求要垂直分表,即按照时间区间将数据拆分到n个表中,PostgreSQL提供了分区表的功能。分区表实际上是把逻辑上的一个大表分割成物理上的几小块,提供了很多好处,比如: 1、查询性能大幅提升 2、删除历史...
PostgreSQL 提供各种统计数据,包括表,所以呢,函数和其他数据对象信息和使用方法。甚至还有更详细的统计数据,比如连接,当前查询和数据库操作 (INSERT/DELETE/UPDATE)。但是这些统计数据大部分是作为永久增长的...
使用postgresql 查下数据库中表大小和索引大小的函数。
一句SQL搞定 区间或分数段数统计,不需要做视图
PostgreSQL表分区功能演进_8
PostgreSQL表的继承和分区
postgresql数据库源码下载,版本为 9.4Beta,也可以到官网下载,官网有时速度会慢。
从这里可以看出PostgreSQL表分区很复杂,程序员要多写点代码才能实现表分区。比如要创建子表,继承父表。给子表增加约束。通过作用于主表的触发器,把主表的修改重定向到适当的分区表等工作,分区的创建和管理都需要...
pgsql-膨胀-估计用于测量 PostgreSQL 的 btree 索引和表中统计膨胀的查询。 应考虑三种不同类型的未使用空间: 对齐填充:根据类型,PostgreSQL 会向您的字段添加一些填充以在行中正确对齐它们。 这与一些 CPU 操作...
PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册 学习手册 (表的继承和分区 表的继承和分区 表的继承和分区 表的继承和分区 ) 10 一、表的继承: 一、表的继承: 一、表的继承: .10 PostgreSQL PostgreSQL ...
PostgreSQL分区表实践与思考.pptx
Navicat for PostgreSQL是一套专为PostgreSQL设计的强大数据库管理及开发工具。它可以用于任何版本 7.5 或以上的 PostgreSQL 数据库服务器,并支持大部份 PostgreSQL最新版本的功能,包括触发器、函数、管理用户等。...
PG定时增量统计事件数据.vsdx 使用PipelineDB统计的方式减少了不少SQL统计查询,程序端只需要根据业务场景进行组装使用就可以了。
实现轻松创建PostgreSQL的分区表DDL