主页 / dba / 问题 / 65964
Accepted
Jack Douglas
Asked: 2014-05-28 02:05:13 +0800 CST

如何将 ctid 分解为页码和行号？

  • 772

表中的每一行都有一个类型的系统列 ，表示该行的物理位置：ctidtid

create table t(id serial);
insert into t default values;
insert into t default values;

select ctid
     , id
from t;

ctid | ID
:---- | -：
(0,1) | 1
(0,2) | 2

dbfiddle在这里

ctid从最合适的类型（例如integer，bigint或）中获取页码的最佳方法是什么numeric(1000,0)？

我能想到的唯一方法是非常丑陋。

postgresql postgresql-9.4

1 个回答

  1. Best Answer
    SELECT (ctid::text::point)[0]::bigint AS block_number FROM t;

    db<>在这里摆弄

    @bma 在他的评论中提出了类似的建议。这里有一个 ...

    类型的基本原理

    ctid是类型tid（元组标识符），ItemPointer在C语言源代码中调用。手册：

    这是系统列的数据类型ctid。元组 ID 是一对（块编号，块内的元组索引），用于标识行在其表中的物理位置。

    大胆强调我的。和：

    ( ItemPointer，也称为CTID)

    在标准安装中，一个块为8 KB 。最大表大小为32 TB。从逻辑上讲，块号必须至少容纳最多（根据@Daniel 的评论固定）：

    SELECT (2^45 / 2^13)::int      -- = 2^32 = 4294967294

    这将适合 unsigned integer。经过进一步调查，我在源代码中发现......

    块按顺序编号，从 0 到 0xFFFFFFFE。

    大胆强调我的。这证实了第一个计算：

    SELECT 'xFFFFFFFE'::bit(32)::int8 -- max page number: 4294967294

    Postgres 使用有符号整数，因此短一位。但是，我无法确定文本表示是否被移动以适应有符号整数。在有人能解决这个问题之前，我会回退到bigint，这在任何情况下都有效。

    投掷

    Postgres 9.3中没有注册类型的tid类型转换（在 Postgres 13 中仍然如此）：

    SELECT *
FROM   pg_cast
WHERE  castsource = 'tid'::regtype
OR     casttarget = 'tid'::regtype;

 castsource | casttarget | castfunc | castcontext | castmethod
------------+------------+----------+-------------+------------
(0 rows)

    您仍然可以投射到text. Postgres 中的每种类型都有一个文本表示：

    另一个重要的例外是“自动 I/O 转换转换”，即使用数据类型自己的 I/O 函数与文本或其他字符串类型进行转换或从文本或其他字符串类型转换执行的转换，在 pg_cast.

    文本表示与一个点的表示相匹配，该点由两个float8数字组成，该转换是无损的。

    您可以访问索引为 0 的点的第一个数字。投射到bigint. 瞧。

    表现

    我在一张有 30k 行（最好的 5 行）的表上在 Postgres 9.4 中进行了快速测试，并想到了几个表达式，包括您的原始表达式：

    SELECT (ctid::text::point)[0]::int                              --  25 ms
     , right(split_part(ctid::text, ',', 1), -1)::int           --  28 ms
     , ltrim(split_part(ctid::text, ',', 1), '(')::int          --  29 ms
     , (ctid::text::t_tid).page_number                          --  31 ms
     , (translate(ctid::text,'()', '{}')::int[])[1]             --  45 ms
     , (replace(replace(ctid::text,'(','{'),')','}')::int[])[1] --  51 ms
     , substring(right(ctid::text, -1), '^\d+')::int            --  52 ms
     , substring(ctid::text, '^\((\d+),')::int                  -- 143 ms
FROM   tbl;

    int而不是bigint, 大部分与测试目的无关。bigint我最终在一张有 50k 行的表上重复了 Postgres 13 中的测试。结果大体相同！

    转换t_tid建立在用户定义的复合类型上，就像@Jake 评论的那样。
    它的要点：转换往往比字符串操作更快。正则表达式很昂贵。上述解决方案是最短和最快的。

    • 28

相关问题