如何將 ctid 分解為頁碼和行號？

表中的每一行都有一個類型的系統列 ，表示該行的物理位置：ctid``tid

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)？

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

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 評論的那樣。

它的要點：轉換往往比字元串操作更快。正則表達式很昂貴。上述解決方案是最短和最快的。

引用自：https://dba.stackexchange.com/questions/65964