PostgreSQL表的行數統計

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