对于设置为的表,(fillfactor = 75)
我试图确保每个数据页只能有 3 行。
select ((8192 * 0.75 - 24) / 3)- 4 - 24; -- returns 2012.
前 24 位是页眉元字节。
最后 24 是 t_hoff(每行的行元字节)
最后 4 是每个插入行的行指针。
2012 % 8 = 4。所以我想确保一页只有 3 行我只能使用 2008 字节?
CREATE TABLE hot (hotid int,s char(2000))WITH (fillfactor = 75,autovacuum_enabled = OFF);
INSERT INTO hot VALUES (1, 'A');
SELECT
lp,lp_off,lp_len,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2
FROM
heap_page_items (get_raw_page ('hot', 0));
回报
lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+--------+------------------+-------------
1 | 6160 | 2032 | 24 | (0,1) | 0000100000000010 | 2
(1 row)
我对为什么lp_len = 2032的猜测。2032 = 8 + 2000 + 24。
第一个 4 字节 int 将其填充到 8 字节。2000 是列s
,最后 24 个字节是行元页字节。
对于下表,我预计lp_len
是 2032。但事实并非如此。我错过了什么?
CREATE TABLE hot_test (
hotid int, hotid1 int,s char(2000)
)WITH (fillfactor = 75,autovacuum_enabled = OFF);
INSERT INTO hot_test VALUES (1, 1, 'A');
SELECT
lp,lp_off,lp_len,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2
FROM
heap_page_items (get_raw_page ('hot_test', 0));
回报
lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+--------+------------------+-------------
1 | 8128 | 59 | 24 | (0,1) | 0000100000000010 | 3
(1 row)
更新:
drop table hot_test;
CREATE TABLE hot_test (
hotid int, hotid1 int,s char(1999)
)WITH (fillfactor = 75,autovacuum_enabled = OFF);
ALTER TABLE hot_test ALTER s SET STORAGE PLAIN;
INSERT INTO hot_test VALUES (1, 1, 'A');
SELECT
lp,lp_off,lp_len,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2
FROM
heap_page_items (get_raw_page ('hot_test', 0));
回报
lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+--------+------------------+-------------
1 | 6152 | 2035 | 24 | (0,1) | 0000100000000010 | 3
(1 row)
2035 = 4 + 4 + 1999 + 4 + 24 所以我猜 varlena 文本需要 4 个字节的填充?
drop table paddling;
CREATE TABLE paddling (
hotid int, hotid1 int,s char(127)
)WITH (fillfactor = 75,autovacuum_enabled = OFF);
ALTER TABLE paddling ALTER s SET STORAGE PLAIN;
INSERT INTO paddling VALUES (1, 1, 'ASDFGHJKLQWERTYUI');
SELECT
lp,lp_off,lp_len,lp_len - 24 - 8,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2
FROM
heap_page_items (get_raw_page ('paddling', 0));
回报
lp | lp_off | lp_len | ?column? | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+----------+--------+--------+------------------+-------------
1 | 8024 | 163 | 131 | 24 | (0,1) | 0000100000000010 | 3
(1 row)
drop table paddling;
CREATE TABLE paddling (
hotid int, hotid1 int,s char(126)
)WITH (fillfactor = 75,autovacuum_enabled = OFF);
ALTER TABLE paddling ALTER s SET STORAGE PLAIN;
INSERT INTO paddling VALUES (1, 1, 'ASDFGHJKLQWERTYUI');
SELECT
lp,lp_off,lp_len,lp_len - 24 - 8,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2
FROM
heap_page_items (get_raw_page ('paddling', 0));
回报
lp | lp_off | lp_len | ?column? | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+----------+--------+--------+------------------+-------------
1 | 8032 | 159 | 127 | 24 | (0,1) | 0000100000000010 | 3
(1 row)
似乎对于char(x)
,如果 x <= 126,填充是一个字节。x > 126 则填充为 4 字节。经过测试,也适用于text
数据类型。
你忘了吐司。对于超过特定大小阈值的行(您在第二个示例中超过了该阈值),大属性将在存储前被压缩。
如果你真的想避免这种优化,请禁用 TOAST: