来自https://github.com/postgres/postgres/blob/6ff2e8cdd410f70057cfa6259ad395c1119aeb32/src/test/regress/sql/test_setup.sql#L144的 Measure the size of a PostgreSQL table row
table tenk1的第一个查询
CREATE VIEW tenk1_storage_info AS
SELECT
l.metric,
l.nr AS bytes,
CASE WHEN is_size THEN
pg_size_pretty(nr)
END AS bytes_pretty,
CASE WHEN is_size THEN
nr / NULLIF (x.ct, 0)
END AS bytes_per_row
FROM (
SELECT
min(tableoid) AS tbl,
count(*) AS ct,
sum(length(t::text)) AS txt_len
FROM
public.tenk1 t) x
CROSS JOIN LATERAL (
VALUES (TRUE, 'core_relation_size', pg_relation_size(tbl)),
(TRUE, 'visibility_map', pg_relation_size(tbl, 'vm')),
(TRUE, 'free_space_map', pg_relation_size(tbl, 'fsm')),
(TRUE, 'table_size_incl_toast', pg_table_size(tbl)),
(TRUE, 'index_size', pg_indexes_size(tbl)),
(TRUE, 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl)),
(TRUE, 'live_rows_in_text_representation', txt_len),
(FALSE, '----------------------------------', NULL),
(FALSE, 'row_count', ct),
(FALSE, 'live_tuples', pg_stat_get_live_tuples(tbl)),
(FALSE, 'dead_tuples', pg_stat_get_dead_tuples(tbl))) l (is_size, metric, nr);
select * from tenk1_storage_info;
返回
metric | bytes | bytes_pretty | bytes_per_row
------------------------------------+---------+--------------+---------------
core_relation_size | 2826240 | 2760 kB | 282
visibility_map | 8192 | 8192 bytes | 0
free_space_map | 24576 | 24 kB | 2
table_size_incl_toast | 2859008 | 2792 kB | 285
index_size | 827392 | 808 kB | 82
total_size_incl_toast_and_indexes | 3686400 | 3600 kB | 368
live_rows_in_text_representation | 680800 | 665 kB | 68
---------------------------------- | | |
row_count | 10000 | |
live_tuples | 10000 | |
dead_tuples | 0 | |
(11 rows)
CREATE TABLE tenk1 (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name
);
\set filename '/home/jian/Desktop/pg_sources/main/postgres/src/test/regress/data/tenk.data'
COPY tenk1
FROM
:'filename';
VACUUM ANALYZE tenk1;
select ctid, * from tenk1
where (ctid::text::point)[0] = 0 and (ctid::text::point)[1] = 3 \gx
返回:
ctid | (0,3)
unique1 | 3420
unique2 | 2
two | 0
four | 0
ten | 0
twenty | 0
hundred | 20
thousand | 420
twothousand | 1420
fivethous | 3420
tenthous | 3420
odd | 40
even | 41
stringu1 | OBAAAA
stringu2 | CAAAAA
string4 | OOOOxx
SELECT
lp,
lp_off,
lag(lp_off) OVER () - lp_off AS gap_lapoff,
lp_flags,
lp_len,
t_hoff,
t_ctid,
t_infomask::bit(16),
t_infomask2
FROM
heap_page_items (get_raw_page ('tenk1', 0)) LIMIT 4;
返回:
lp | lp_off | gap_lapoff | lp_flags | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+------------+----------+--------+--------+--------+------------------+-------------
1 | 7920 | | 1 | 268 | 24 | (0,1) | 0000100100000000 | 16
2 | 7648 | 272 | 1 | 268 | 24 | (0,2) | 0000100100000000 | 16
3 | 7376 | 272 | 1 | 268 | 24 | (0,3) | 0000100100000000 | 16
4 | 7104 | 272 | 1 | 268 | 24 | (0,4) | 0000100100000000 | 16
hexdump -C -n 268 -s 7376 58105
返回:
00001cd0 58 22 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |X"..............|
00001ce0 03 00 10 00 00 09 18 00 5c 0d 00 00 02 00 00 00 |........\.......|
00001cf0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00001d00 14 00 00 00 a4 01 00 00 8c 05 00 00 5c 0d 00 00 |............\...|
00001d10 5c 0d 00 00 28 00 00 00 29 00 00 00 4f 42 41 41 |\...(...)...OBAA|
00001d20 41 41 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |AA..............|
00001d30 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00001d50 00 00 00 00 00 00 00 00 00 00 00 00 43 41 41 41 |............CAAA|
00001d60 41 41 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |AA..............|
00001d70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00001d90 00 00 00 00 00 00 00 00 00 00 00 00 4f 4f 4f 4f |............OOOO|
00001da0 78 78 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |xx..............|
00001db0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00001dd0 00 00 00 00 00 00 00 00 00 00 00 00 |............|
00001ddc
问题:
- 一行如何占用 268 个字节?如果整数列(从左到右)将一起填充为 8 字节,
name
数据类型为 64 字节。那么整体就是 6 * 8 + 8 + 64 * 3 = 248。 - 在 hexdump 中,* 是什么意思?或者我如何一般地解释 hexdump。to_hex(3420) 返回
d5c
。如果我0d 5c
在 hexdump 中阅读,这意味着从右到左阅读? - 最后一个查询:
gap_lapoff
!=lp_len
但在这里 [https://youtu.be/L-dw1yRFYVg?t=2236] 是一样的。想知道为什么。
要测量行的大小,让我们按照文档进行操作:
有 23 个字节的行标题
如果所有值都不为 NULL,则不会有 NULL 位掩码
将有一个字节的填充,因为实际的行数据必须从可被 8 整除的地址开始
如果所有值都不为 NULL,则表行由 13 个字节乘以 4 个字节加上 3 个字节乘以 64 个字节组成,总计为 244 个字节;中间没有填充,因为
name
对齐为 1 字节因此,没有 NULL 的表行的大小为 23 + 1 + 244 = 268 字节。这些行之间将有额外的 4 字节填充,因为每行的地址也是 8 的倍数。这说明了各行之间 272 字节的偏移量。