作者
digoal
日期
2021-02-28
标签
PostgreSQL , page tid scan , tid range scan
背景
什么是TID?
可以参考PG的page layout, heap table是以page来组织的, 每个page里面若干tuple. TID 是tuple的寻址地址: (pageid, itemid), pageid即第几个数据块, itemid即这个page内的第几条记录.
例如pagesize=8k时, tid=(10,1)表示第11个数据块的第一条记录. (page从0开始, item从1开始)
postgres=# create table tidtest(id int, info text);
CREATE TABLE
postgres=# insert into tidtest select generate_series(1,10000), md5(Random()::text);
INSERT 0 10000
postgres=# select ctid,* from tidtest limit 1000;
ctid | id | info
---------+-----+----------------------------------
(0,1) | 1 | 37118b76c2b8c62f78e785d52403f173
(0,2) | 2 | 14ff83346ede4618d14b66822387c710
(0,3) | 3 | a208b625a31d192e5e39bdf26a76adbb
......
(0,119) | 119 | fcb98794a1a326382954f1faddf27386
(0,120) | 120 | 0f5616b4c4d620095f0fefc5e767fb22
(1,1) | 121 | 8d9300a963198f97bacdfb2850192557
(1,2) | 122 | 15d1e0f71388652b15fa1c20a440f0bb
....
PostgreSQL 14以前的版本支持tid scan, 即, 我们可以直接指定要查询的行号. 他只需要扫描一个page. 通过itemid offset直接拿到tuple, 所以速度非常快.
```
postgres=# select ctid,* from tidtest where ctid='(10,1)';
ctid | id | info
--------+------+----------------------------------
(10,1) | 1201 | 598591b1b45498bdbc05c5aea2cb09e3
(1 row)
postgres=# explain select ctid,* from tidtest where ctid='(10,1)';
QUERY PLAN
Tid Scan on tidtest (cost=0.00..1.11 rows=1 width=43)
TID Cond: (ctid = '(10,1)'::tid)
(2 rows)
```
那么有没有可能一次获取一个page的数据呢?
PostgreSQL 14可以了
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bb437f995d47405ecd92cf66df71f7f7e40ed460
```
Add TID Range Scans to support efficient scanning ranges of TIDs
author David Rowley drowley@postgresql.org
Sat, 27 Feb 2021 09:59:36 +0000 (22:59 +1300)
committer David Rowley drowley@postgresql.org
Sat, 27 Feb 2021 09:59:36 +0000 (22:59 +1300)
commit bb437f995d47405ecd92cf66df71f7f7e40ed460
tree 0ee50f8a501e1ecc30d5cfd0eeb6ed0bcd41e2b2 tree | snapshot
parent f4adc41c4f92cc91d507b19e397140c35bb9fd71 commit | diff
Add TID Range Scans to support efficient scanning ranges of TIDs
This adds a new executor node named TID Range Scan. The query planner
will generate paths for TID Range scans when quals are discovered on base
relations which search for ranges on the table's ctid column. These
ranges may be open at either end. For example, WHERE ctid >= '(10,0)';
will return all tuples on page 10 and over.
To support this, two new optional callback functions have been added to
table AM. scan_set_tidrange is used to set the scan range to just the
given range of TIDs. scan_getnextslot_tidrange fetches the next tuple
in the given range.
For AMs were scanning ranges of TIDs would not make sense, these functions
can be set to NULL in the TableAmRoutine. The query planner won't
generate TID Range Scan Paths in that case.
Author: Edmund Horner, David Rowley
Reviewed-by: David Rowley, Tomas Vondra, Tom Lane, Andres Freund, Zhihong Yu
Discussion: https://postgr.es/m/CAMyN-kB-nFTkF=VA_JPwFNo08S0d-Yk0F741S2B7LDmYAi8eyA@mail.gmail.com
```
select * from tidtest where ctid >= '(10,0)' and ctid < '(11,0)';
range tid scan有什么应用? 我暂时能想到的, 例如
- 1、应用层控制的并行全表数据处理.
- 2、随机的PAGE级别数据查询.
- 3、并行全表数据更新.
- 4、随机的行级数据返回.
建议先学习一下snapshot export技术:
《PostgreSQL 块级 snapshot (flash back) - postgrespro improvement》
《londiste3 copy table' snapshot & PostgreSQL logical replication's snapshot 不同之处》
《PostgreSQL 共享事务快照功能 - PostgreSQL 9.2 can share snapshot between multi transactions》
《PostgreSQL 并行逻辑备份与一致性讲解 - PostgreSQL 9.3 parallel pg_dump》
并行全表数据更新例子
1、main session:
启动rr事务:
postgres=# begin isolation level repeatable read ;
BEGIN
导出rr事务id:
```
postgres=*# select pg_export_snapshot();
pg_export_snapshot
00000003-00323878-1
(1 row)
```
查询被处理的表总共有多少个数据块:
```
postgres=*# select relpages from pg_class where oid='tidtest'::Regclass;
relpages
8400
(1 row)
```
处理0-99号PAGEID:
update tidtest set info='new'||info where ctid >= '(0,0)' and ctid < '(100,0)';
2、other parallel sessions:
每个session 启动rr事务:
postgres=# begin isolation level repeatable read ;
BEGIN
每个session 导入rr事务ID:
postgres=*# SET TRANSACTION SNAPSHOT '00000003-00323878-1';
SET
每个session 处理100个连续的pageid如100-199, 200-299, ... :
update tidtest set info='new'||info where ctid >= '(100,0)' and ctid < '(200,0)';
...
随机返回记录
1、可以使用随机采样方法:
《PostgreSQL 随机采样应用 - table sample, tsm_system_rows, tsm_system_time》
《PostgreSQL 随机记录返回 - 300倍提速实践 (随机数组下标代替order by random())》
2、也可以使用tid range scan(相比以上方法缺陷是某些PAGE可能没有有效记录, 例如都是dead tuple):
返回某一个或连续N个数据块的数据
select ctid,* from tidtest where ctid>='输入变量1' and ctid<'输入变量2';
返回N条数据, 并且要求这N条数据来自不同的数据块
select * from tidtest where ctid='输入变量1' or ctid='输入变量2' or ctid='输入变量3' ... ...;
返回同一个数据块的N条数据
select * from tidtest where ctid>='输入变量1' and ctid<'输入变量2' limit N;
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
