How do you make sure a data page can only have 3 rows
2
votes
1
answer
69
views
For a table with a setting of
(fillfactor = 75)
I am trying to make sure that each data page can only have 3 rows.
select ((8192 * 0.75 - 24) / 3)- 4 - 24; -- returns 2012.
First 24 is page header meta byte.
Last 24 is t_hoff (row meta bytes for every row)
Last 4 is the line pointer for each inserted row.
2012 % 8 = 4. So I guess to make sure one page has only 3 rows I can only use 2008 byte?
~~~pgsql
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));
~~~
returns
~~~pgsql
lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+--------+------------------+-------------
1 | 6160 | 2032 | 24 | (0,1) | 0000100000000010 | 2
(1 row)
~~~
My guess of why **lp_len = 2032**. 2032 = 8 + 2000 + 24.
first 4 byte int paddling it to 8 bytes. 2000 is for column s
, the last 24 bytes are row meta page bytes.
For the following table I expected lp_len
to be 2032. But it's not. What did I miss?
~~~pgsql
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));
~~~
returns
~~~pgsql
lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+--------+------------------+-------------
1 | 8128 | 59 | 24 | (0,1) | 0000100000000010 | 3
(1 row)
~~~
Update:
~~~pgsql
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));
~~~
returns
~~~pgsql
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
So I guess varlena text needs 4 bytes of padding?
~~~pgsql
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));
~~~
returns
~~~pgsql
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)
~~~
~~~pgsql
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));
~~~
returns
~~~pgsql
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)
~~~
Seems for char(x)
, if x 126 then padding is 4 byte. Tested, also applies to text
data type.
Asked by jian
(487 rep)
Apr 27, 2023, 05:18 AM
Last activity: Apr 28, 2023, 11:59 PM
Last activity: Apr 28, 2023, 11:59 PM