1)postgresql默认存储的是堆表,数据按行存储在heap page中。行记录除了存储字段的值外还会存储对应的ctid即行号,表示在哪个页第几个记录。
2)进行select的时候也可以直接指定ctid进行扫描:heap handler中表访问方法的函数为table_tuple_fetch_row_version函数,它来完成通过ctid获取元组。
postgres=# select *from t where ctid='(0,2)'; id1 | id2 -----+----- 2 | b (1 row)
复制
3)根据ctid扫描的堆栈为
Breakpoint 4, table_tuple_fetch_row_version (rel=0xae9da164, tid=0xbfb2d4c6, snapshot=0x8c06364, slot=0x8c4b7f4) at ../../../src/include/access/tableam.h:1026 1026 return rel->rd_tableam->tuple_fetch_row_version(rel, tid, snapshot, slot); (gdb) s heapam_fetch_row_version (relation=0xae9da164, tid=0xbfb2d4c6, snapshot=0x8c06364, slot=0x8c4b7f4) at heapam_handler.c:190 190 BufferHeapTupleTableSlot *bslot = (BufferHeapTupleTableSlot *) slot; (gdb) bt #0 heapam_fetch_row_version (relation=0xae9da164, tid=0xbfb2d4c6, snapshot=0x8c06364, slot=0x8c4b7f4) at heapam_handler.c:190 #1 0x082f7e08 in table_tuple_fetch_row_version (rel=0xae9da164, tid=0xbfb2d4c6, snapshot=0x8c06364, slot=0x8c4b7f4) at ../../../src/include/access/tableam.h:1026 #2 0x082f86ee in TidNext (node=0x8c4b67c) at nodeTidscan.c:386 #3 0x082cbb1a in ExecScanFetch (node=0x8c4b67c, accessMtd=0x82f85bc <TidNext>, recheckMtd=0x82f8751 <TidRecheck>) at execScan.c:133 #4 0x082cbb70 in ExecScan (node=0x8c4b67c, accessMtd=0x82f85bc <TidNext>, recheckMtd=0x82f8751 <TidRecheck>) at execScan.c:183 #5 0x082f8784 in ExecTidScan (pstate=0x8c4b67c) at nodeTidscan.c:443 #6 0x082c9d5f in ExecProcNodeFirst (node=0x8c4b67c) at execProcnode.c:445 #7 0x082c10ef in ExecProcNode (node=0x8c4b67c) at ../../../src/include/executor/executor.h:239 #8 0x082c319f in ExecutePlan (estate=0x8c4b554, planstate=0x8c4b67c, use_parallel_mode=false, operation=CMD_SELECT, sendTuples=true, numberTuples=0, direction=ForwardScanDirection, dest=0x8b941dc, execute_once=true) at execMain.c:1646 #9 0x082c1574 in standard_ExecutorRun (queryDesc=0x8bb3e34, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:364 #10 0x082c1411 in ExecutorRun (queryDesc=0x8bb3e34, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:308 #11 0x0846d38b in PortalRunSelect (portal=0x8bde6f4, forward=true, count=0, dest=0x8b941dc) at pquery.c:929 #12 0x0846d0b0 in PortalRun (portal=0x8bde6f4, count=2147483647, isTopLevel=true, run_once=true, dest=0x8b941dc, altdest=0x8b941dc, completionTag=0xbfb2d886 "") at pquery.c:770 #13 0x0846772a in exec_simple_query (query_string=0x8b922e4 "select *from t where ctid='(0,2)';") at postgres.c:1215 #14 0x0846b7e4 in PostgresMain (argc=1, argv=0x8bb7fec, dbname=0x8b8f824 "postgres", username=0x8bb7f14 "pg") at postgres.c:4236 #15 0x083db09d in BackendRun (port=0x8bb49b8) at postmaster.c:4431 #16 0x083da896 in BackendStartup (port=0x8bb49b8) at postmaster.c:4122 #17 0x083d6dff in ServerLoop () at postmaster.c:1704 #18 0x083d674d in PostmasterMain (argc=1, argv=0x8b8d808) at postmaster.c:1377 #19 0x0831d0f4 in main (argc=1, argv=0x8b8d808) at main.c:228
复制
4)而postgres=# set enable_tidscan=off;将这个参数关闭后,就不能通过TID快速进行行扫描了,会走全表扫描,即通过heap handler表方法方法heap_getnextslot函数获取元组。
Breakpoint 1, heap_getnextslot (sscan=0x8c4c21c, direction=ForwardScanDirection, slot=0x8c4b774) at heapam.c:1351 1351 HeapScanDesc scan = (HeapScanDesc) sscan; (gdb) bt #0 heap_getnextslot (sscan=0x8c4c21c, direction=ForwardScanDirection, slot=0x8c4b774) at heapam.c:1351 #1 0x082f26c2 in table_scan_getnextslot (sscan=0x8c4c21c, direction=ForwardScanDirection, slot=0x8c4b774) at ../../../src/include/access/tableam.h:875 #2 0x082f2764 in SeqNext (node=0x8c4b67c) at nodeSeqscan.c:80 #3 0x082cbb1a in ExecScanFetch (node=0x8c4b67c, accessMtd=0x82f26e7 <SeqNext>, recheckMtd=0x82f2774 <SeqRecheck>) at execScan.c:133 #4 0x082cbb9c in ExecScan (node=0x8c4b67c, accessMtd=0x82f26e7 <SeqNext>, recheckMtd=0x82f2774 <SeqRecheck>) at execScan.c:200 #5 0x082f27a7 in ExecSeqScan (pstate=0x8c4b67c) at nodeSeqscan.c:112 #6 0x082c9d5f in ExecProcNodeFirst (node=0x8c4b67c) at execProcnode.c:445 #7 0x082c10ef in ExecProcNode (node=0x8c4b67c) at ../../../src/include/executor/executor.h:239 #8 0x082c319f in ExecutePlan (estate=0x8c4b554, planstate=0x8c4b67c, use_parallel_mode=false, operation=CMD_SELECT, sendTuples=true, numberTuples=0, direction=ForwardScanDirection, dest=0x8b941dc, execute_once=true) at execMain.c:1646 #9 0x082c1574 in standard_ExecutorRun (queryDesc=0x8bb3e34, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:364 #10 0x082c1411 in ExecutorRun (queryDesc=0x8bb3e34, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:308 #11 0x0846d38b in PortalRunSelect (portal=0x8bde6f4, forward=true, count=0, dest=0x8b941dc) at pquery.c:929 #12 0x0846d0b0 in PortalRun (portal=0x8bde6f4, count=2147483647, isTopLevel=true, run_once=true, dest=0x8b941dc, altdest=0x8b941dc, completionTag=0xbfb2d886 "") at pquery.c:770 #13 0x0846772a in exec_simple_query (query_string=0x8b922e4 "select *from t where ctid='(0,2)';") at postgres.c:1215 #14 0x0846b7e4 in PostgresMain (argc=1, argv=0x8bb7fec, dbname=0x8b8f824 "postgres", username=0x8bb7f14 "pg") at postgres.c:4236 #15 0x083db09d in BackendRun (port=0x8bb49b8) at postmaster.c:4431 #16 0x083da896 in BackendStartup (port=0x8bb49b8) at postmaster.c:4122 #17 0x083d6dff in ServerLoop () at postmaster.c:1704 #18 0x083d674d in PostmasterMain (argc=1, argv=0x8b8d808) at postmaster.c:1377 #19 0x0831d0f4 in main (argc=1, argv=0x8b8d808) at main.c:228
复制
4)元组结构参考
https://blog.csdn.net/yanzongshuai/article/details/84195910
5)slot
a)内存中slot结构如上图所示。TupleTableSlot为存储行记录的结构。从数据页读取出记录后,会将其存储到slot中,返回上层。
b)BufferHeapTupleTableSlot结构中第一个成员为HeapTupleTableSlot,而HeapTupleTableSlot第一个成员为TupleTableSlot,为第一个成员方便进行类型强制转换。
c)将TupleTableSlot传到上层后,上层强制转换成HeapTupleTableSlot,进而解析出他的第二个成员tuple,这个tuple的结构为HeapTupleData,包括:t_len:t_data的长度,t_self:TID了,t_tableOid:所属表的OID,t_data:这个为记录头及其数据。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
评论
相关阅读
王炸!OGG 23ai 终于支持从PostgreSQL备库抽取数据了
曹海峰
339次阅读
2025-03-09 12:54:06
PostgreSQL 17.3、16.7、15.11、14.16 和 13.19 发布!
通讯员
212次阅读
2025-02-17 14:45:24
玩一玩系列——玩玩pg_duckdb(我的开源之旅)
小满未满、
197次阅读
2025-02-23 13:45:28
Pgpool-II 4.6 beta1 现已发布
通讯员
192次阅读
2025-02-20 10:20:51
PostgreSQL 17.4、16.8、15.12、14.17 和 13.20 版本发布!
通讯员
180次阅读
2025-02-21 10:40:49
玩一玩系列——玩玩pg_mooncake(PostgreSQL的高性能列存新贵)
小满未满、
156次阅读
2025-03-03 17:18:03
Product Hunt 发布2024年度金喵奖榜单:数据库 AI 工具 postgres.new 荣获“最佳开源产品奖”
通讯员
152次阅读
2025-02-19 14:07:02
PostgreSQL计划于 2025 年 2 月 20 日进行周期外发布
通讯员
131次阅读
2025-02-17 12:13:39
套壳论
梧桐
123次阅读
2025-03-09 10:58:17
玩一玩系列——玩玩postgres_fdw
小满未满、
107次阅读
2025-02-17 15:09:38