PHP实战中知识总结 / PgSQL - 索引之ctid解析

一、ctid

ctid表示数据行在它所处的表内的物理位置，ctid字段的类型是tid。

虽然ctid可以快速定位到数据行，但是在每次执行vaccum full命令之后，数据行在块内的物理位置就会发生变化，所以ctid并不能作为长期的数据行标识符，应该使用主键来标识一个逻辑行

// 查看ctid
// 格式(blockid,itemid)：如(0,1)；0表示块id；1表示在这块第一条记录
postgres=# select ctid,id from test limit 10;
 ctid | id
--------+----
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,5) | 5
(0,6) | 6
(0,7) | 7
(0,8) | 8
(0,9) | 9
(0,10) | 10
(10 rows)


二、ctid 的作用：

1、用于去重，相当于oracle中的伪列 rowid

postgres=# select ctid,* from test;
ctid | id | name | remark
-------+----+-------+-------
(0,1) | 2 | xx  | ss
(0,4) | 3 | dd  | ss
(0,5) | 4 | xs  | aa
(3 rows)
postgres=# delete from test where ctid not in (select min(ctid) from test group by remark);
DELETE 1
postgres=# select ctid,* from test;
ctid | id | name | remark
-------+----+-------+-------
(0,1) | 2 | xx  | ss
(0,5) | 4 | xs  | aa
(2 rows)
// 删除成功后，重新插入一条数据
postgres=# insert into test values(3,'aa','asd');
INSERT 0 1
postgres=# select ctid,* from test;
ctid | id | name | remark
-------+----+-------+-------
(0,1) | 2 | xx  | ss
(0,5) | 4 | xs  | aa
(0,6) | 3 | aa  | asd
(3 rows)


可以看到，在删除ctid为(0,4)后重新插入一条数据，新数据的ctid为(0,6)，而不是刚刚删除的(0,4)。这是因为pgsql的特性：删除元祖占据的存储空间并没有被回收，直到执行vaccum命令之后（postgresql里面有AUTOVACUUM进程；也可以手动回收）

2、使用系统表pg_class，查看表占用的总块数，其中relpages,reltuples分别代表块数，记录数！

postgres=# select relpages,reltuples from pg_class where relname = 'test';
relpages | reltuples
----------+-----------
   637 |  100000
(1 row)