關於 postgresql page size 疑問 about blog HOT 4 CLOSED

pg会自动对超出2000字节的字段进行压缩, 你可以看一下是不是存储到toast切片表里面了

from blog.

感謝 @digoal 德哥回答 我來看一下 toast 不壓縮是否大小對的上
另外請問一下德哥 是否有辦法查看 複合式Index 的 most_common_vals，most_common_freqs.

在SQL Server是對於Index來建立不同的統計資訊跟資料分佈 直方圖

例如此圖 我網路上找的範例圖

可以查看到 複合式Index 組合的密度 還有 下方的資料分佈統計資訊

• pg_stats：存放資料表統計資訊（每個欄位）
• pg_class：統計資料表 Index還有source table目前存儲資訊 (有多少block,tuples...)

想請問是否有辦法查詢到複合式Index 資料分佈的統計資訊 (most_common_vals，most_common_freqs)

我查了許多資料都說透過 pg_stats 查看欄位資料分佈並沒有涉及到Index統計分佈

如果此題需要另外開 Issue 我可以再另外處理 謝謝

from blog.

pg会自动对超出2000字节的字段进行压缩, 你可以看一下是不是存储到toast切片表里面了

@digoal 德哥 我查看 t8 資料的確會存在 toast 如果改成 external 就會存在 page 中size也可以對的起來.

但我還是不了解 為什麼 t9 資料表 會使用超過 10 個 page.

因為裡面有 4000 筆 int 資料，理論上是 4000 * 4 byte = 16kb，
大約等於 16kb = 8kb * 2

所以 page 應該只有2個左右 但我看 t9 並沒有存放在 toast 上

select relpages,reltuples,relkind,oid,relfilenode,reltoastrelid  
from pg_class where relname in ('t9','ix_t9');

利用上面語法查詢如下圖

CREATE TABLE t9 (id int);
CREATE UNIQUE INDEX  ix_t9 on t9(id);

insert into t9 select generate_series(1,2000);
insert into t9 select generate_series(2001,4000);

analyze t9;
select relpages,reltuples,relkind,oid  from pg_class where relname in ('t9','ix_t9');

                             Table "public.t9"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              |
Indexes:
    "ix_t9" UNIQUE, btree (id)

經查詢 http://www.postgres.cn/docs/9.5/pageinspect.html

我透過 bt_page_items 跟 heap_page_items 查看資料存儲資訊 發現每個block 到 row 226 就結束了 我看明明還有空間可以儲存 但資料卻存在另一個 block 中

select *
from heap_page_items(get_raw_page('t9',0));

SELECT * 
FROM bt_page_items('ix_t9',1);

from blog.

pg会自动对超出2000字节的字段进行压缩, 你可以看一下是不是存储到toast切片表里面了

@digoal 德哥 我查看 t8 資料的確會存在 toast 如果改成 external 就會存在 page 中size也可以對的起來.

但我還是不了解 為什麼 t9 資料表 會使用超過 10 個 page.

因為裡面有 4000 筆 int 資料，理論上是 4000 * 4 byte = 16kb，
大約等於 16kb = 8kb * 2

所以 page 應該只有2個左右 但我看 t9 並沒有存放在 toast 上

select relpages,reltuples,relkind,oid,relfilenode,reltoastrelid  
from pg_class where relname in ('t9','ix_t9');

利用上面語法查詢如下圖

CREATE TABLE t9 (id int);
CREATE UNIQUE INDEX  ix_t9 on t9(id);

insert into t9 select generate_series(1,2000);
insert into t9 select generate_series(2001,4000);

analyze t9;
select relpages,reltuples,relkind,oid  from pg_class where relname in ('t9','ix_t9');

                             Table "public.t9"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              |
Indexes:
    "ix_t9" UNIQUE, btree (id)

經查詢 http://www.postgres.cn/docs/9.5/pageinspect.html

我透過 bt_page_items 跟 heap_page_items 查看資料存儲資訊 發現每個block 到 row 226 就結束了 我看明明還有空間可以儲存 但資料卻存在另一個 block 中

select *
from heap_page_items(get_raw_page('t9',0));

SELECT * 
FROM bt_page_items('ix_t9',1);

@digoal 德哥 這個問題 我找到原因了

我想應該是 Database Page Layout

每個 tuples 都有 HeapTupleHeaderData 導致.

我想原因應該是 樂觀鎖 需要紀錄 row version 狀態，而sqlserver是悲觀鎖 所以不用每筆資料都有metadata.

詳細資料 我有寫一篇文章 整理

https://isdaniel.github.io/postgresql-page-deepknow/

from blog.