postgresql之ctid的淺談

標籤：

   ctid： 表示資料記錄的物理行當資訊，指的是 一條記錄位於哪個資料區塊的哪個位移上面。 跟oracle中偽列 rowid 的意義一樣的；只是形式不一樣。   例如這有個一表test；查看每行記錄的ctid情況

mydb=> select ctid,* from test; ctid  |  id  |  name  -------+------+-------- (0,1) | 1001 | lottu (0,2) | 1002 | rax (0,3) | 1003 | xuan (0,4) | 1004 | li0924 (0,5) | 1001 | ak

    格式(blockid,itemid)：拿其中(0,1)來說；0表示塊id；1表示在這塊第一條記錄。   1. 去重：  我們知道rowid在oracle有個重要的作用；被用作表記錄去重；同理 ctid在postgresql裡面同樣可以使用。例如test表id為1001有兩條記錄；現在示範下；

mydb=> delete from test where ctid not in (select min(ctid) from test group by id);DELETE 1mydb=> select ctid,* from test; ctid  |  id  |  name  -------+------+-------- (0,1) | 1001 | lottu (0,2) | 1002 | rax (0,3) | 1003 | xuan (0,4) | 1004 | li0924(4 rows)

剛剛我們刪除了(0,5)這條記錄； 現在我們把這條記錄插入下；看下；

mydb=> insert into test values (1001,‘ak‘);INSERT 0 1mydb=> select ctid,* from test; ctid  |  id  |  name  -------+------+-------- (0,1) | 1001 | lottu (0,2) | 1002 | rax (0,3) | 1003 | xuan (0,4) | 1004 | li0924 (0,6) | 1001 | ak(5 rows)

奇怪了；為什麼不是(0,5),而是(0,6)這個跟postgresql多版本事務有關；跟偽列cmin，cmax有關；跟本文講的ctid沒點關係；這是postgresql的特性；也就是這樣；postgresql裡面沒有復原段的概念；那怎麼把(0,5)在顯示呢；想這塊(0,5)的空間再存放資料；postgresql裡面有AUTOVACUUM進程；當然我們也可以手動回收這段空間；

mydb=> delete from test where name = ‘ak‘;DELETE 1mydb=> vacuum test;          VACUUMmydb=> insert into test values (1001,‘ak‘);INSERT 0 1mydb=> select ctid,* from test; ctid  |  id  |  name  -------+------+-------- (0,1) | 1001 | lottu (0,2) | 1002 | rax (0,3) | 1003 | xuan (0,4) | 1004 | li0924 (0,5) | 1001 | ak(5 rows)

2. 我們剛剛說道 0表示塊id； test資料太少了；不好解釋；建立一個表test2

mydb=> drop table test2;DROP TABLEmydb=> create table test2 (id int primary key, name varchar(10));CREATE TABLEmydb=> insert into test2 select generate_series(1,1000),‘lottu‘ || generate_series(1,1000);INSERT 0 1000

 我們看下id=1000的ctid的blockid是多少；答案是5；意思是說該表的記錄記錄到第6個塊；（因為是從0開始的）

mydb=> select ctid,* from test2 where id = 1000;  ctid  |  id  |   name    --------+------+----------- (5,75) | 1000 | lottu1000(1 row)

當然這樣查表記錄佔了幾個block；假如我這是隨機插入的；那id=1000；就不一定是在第6塊；我們可以藉助系統檢視表pg_class；其中relpages,reltuples分別代表塊數，記錄數！

mydb=> analyze test2;ANALYZEmydb=> select relpages,reltuples from pg_class where relname = ‘test2‘; relpages | reltuples ----------+-----------        6 |      1000(1 row)

總結： ctid存在的意義：表示資料記錄的物理行當資訊，指的是 一條記錄位於哪個資料區塊的哪個位移上面。 跟oracle中偽列 rowid 的意義一樣的；只是形式不一樣。vacuum: 回收未顯示的物理位置；標明可以繼續使用。generate_series: 為一個序列函數；例如1-100；就是generate_series(1,100);0-100直接的偶數generate_series(0,100,2)                          其中的0表示序列開始位置；100代表結束位置；2為位移量。

postgresql之ctid的淺談