使用 pg_prewarm 將 X 個最新行載入到記憶體中

我們有一個大型查詢，當客戶“第一次執行它，清晨……”時，它的速度特別慢

因此，我發現pg_prewarm我想使用從所述查詢中使用的幾個表中載入一定數量或最近訪問的行（插入、更新或刪除）到 PG 的緩衝區記憶體中。

此外，我需要確保“預熱”不超過 PG 的記憶體（shared_buffers 設置我相信，還是我錯了？）要預熱單個表的最後 1000 頁，我可以這樣做：

SELECT pg_prewarm(
   'mytable',
   -- "pre warm" last 1000 pages
   first_block := ( 
       SELECT pg_relation_size('mytable') / current_setting('block_size')::int4 - 1000
   )
);

問題#1：這種方法有意義嗎？

訣竅是 pg_prewarm 只能載入一定數量的頁面，所以我需要計算“某個表的頁面中有多少活動行”

-- show some settings
SELECT current_setting('block_size')::int4 AS page_size_bytes; -- 8192
SHOW shared_buffers; -- 512 MB


-- https://www.postgresql.org/docs/current/static/pgstattuple.html
--CREATE EXTENSION pgstattuple;
-- find out live row size and live rows per page
SELECT 'mytable'AS table_name, pg_size_pretty(tuple_len / tuple_count) AS live_row_size, 8192.00 / (tuple_len / tuple_count) AS live_rows_per_page, * FROM pgstattuple('public.mytable')

--"table_name","live_row_size","live_rows_per_page","table_len","tuple_count","tuple_len","tuple_percent","dead_tuple_count","dead_tuple_len","dead_tuple_percent","free_space","free_percent"
--"studies","1286 bytes",6.3701399688958009,652697600,462123,594431269,91.07,0,0,0,52329672,8.02

問題 #2：我上面的查詢是否正確？這是獲得“每頁實時行”的正確方法嗎？ 問題 #3：我從上面的查詢中得到的 live_row_size 與這個答案中提到的結果不同（由 Erwin 提供）。難道我做錯了什麼？

然後基於 live_rows_per_page 我可以修改 pg_prewarm 以載入足夠的包含 10,000 行 (6.37 x 10,000) 的最後 XXXX 頁

SELECT pg_prewarm(
   'mytable',
   -- "pre warm" pages of the last 10,000 rows for 'mytable'
   first_block := ( 
       SELECT pg_relation_size('mytable') / current_setting('block_size')::int4 - 63700
   )
);

更新#1 關於問題#3，當我執行查詢時，我得到以下資訊mytable… pgstatuple 的輸出不同，可能是因為它沒有列出相同的項目，但我不確定…

"what","bytes/ct","bytes_pretty","bytes_per_row"
"core_relation_size",652697600,"622 MB",1412
"visibility_map",16384,"16 kB",0
"free_space_map",180224,"176 kB",0
"table_size_incl_toast",1101955072,"1051 MB",2384
"indexes_size",508289024,"485 MB",1099
"total_size_incl_toast_and_indexes",1610244096,"1536 MB",3484
"live_rows_in_text_representation",1138946462,"1086 MB",2464
"------------------------------",<NULL>,"<NULL>",<NULL>
"row_count",462123,"<NULL>",<NULL>
"live_tuples",0,"<NULL>",<NULL>
"dead_tuples",3,"<NULL>",<NULL>

解決問題 #3

我從上面的查詢中得到的 live_row_size 與這個答案（由 Erwin）中提到的結果不同。難道我做錯了什麼？

你有：

SELECT pg_size_pretty(tuple_len / tuple_count) AS live_row_size
FROM pgstattuple('public.mytable');

手冊定義：

tuple_len… 活動元組的總長度（以字節為單位

tuple_count） … 活動元組的數量

您計算活動元組的中等長度。您似乎希望 Postgres 在將數據頁提取到 RAM 時僅提取活動元組，但這不是它的工作原理。Postgres 只是將整頁讀入 RAM，包括可能包含的任何死元組。

因此，您的下一個表達式不會計算“每頁的實時行數”：

8192.00 / (tuple_len / tuple_count) AS live_rows_per_page

它計算*“只有活動元組的每個數據頁的假設最大活動行數”*，這很高興知道，但對您的任務沒有用處。

解決更新 #1

為什麼大小不一樣？在引用的答案中，我以這個為首：

這將證明測量“行大小”的各種方法會導致非常不同的結果。這完全取決於您要準確測量的內容。

你的發現似乎證實了這一點——儘管我不確定你到底比較了什麼。

引用的答案有點過時了。我改進了一些細節並添加了來自pgstattuple的數字以進行比較。您需要為每個數據庫安裝一次模組（您顯然擁有它，但可能是其他讀者）：

CREATE EXTENSION pgstattuple;

然後：

SELECT l.what, l.nr AS "bytes/ct"
    , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
    , CASE WHEN is_size THEN nr
     / CASE part WHEN 1 THEN NULLIF(x.ct, 0)
                 WHEN 2 THEN NULLIF(st.tuple_count, 0)
                 WHEN 3 THEN NULLIF(st.dead_tuple_count, 0)
                 WHEN 4 THEN NULLIF(st.tuple_count + st.dead_tuple_count, 0) END
           END AS bytes_per_row
FROM  (
  SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
       , count(*)             AS ct
       , sum(length(t::text)) AS txt_len  -- length in characters
  FROM   public.big t                     -- provide table here *once* !!!
  ) x
, LATERAL pgstattuple(tbl) st             -- also get numbers from pgstattuple 
, LATERAL (
  VALUES
     (1, false, 'row_count'                           , ct)
   , (1, false, ' ------ DB_obj_size_func -------'    , NULL)
   , (1, true , 'core_relation_size'                  , pg_relation_size(tbl))
   , (1, true , 'visibility_map'                      , pg_relation_size(tbl, 'vm'))
   , (1, true , 'free_space_map'                      , pg_relation_size(tbl, 'fsm'))
   , (1, true , 'table_size_incl_toast'               , pg_table_size(tbl))
   , (1, true , 'indexes_size'                        , pg_indexes_size(tbl))
   , (1, true , 'total_size_incl_toast_and_indexes'   , pg_total_relation_size(tbl))
   , (1, true , 'live_rows_in_text_representation'    , txt_len)
   , (2, false, ' ------ pgstattuple ------------'    , NULL)
   , (2, false, 'live_tuples'                         , st.tuple_count)
   , (2, false, 'dead_tuples'                         , st.dead_tuple_count)
   , (2, true , 'total table / live tuples'           , st.table_len)
   , (2, true , 'live table / live tuples'            , st.tuple_len)
   , (3, true , 'dead table / dead tuples'            , st.dead_tuple_len)
   , (4, true , 'total table / all tuples'            , st.table_len)
  ) l(part, is_size, what, nr);

簡單的解決方案？

如果您的查詢沒有副作用，最有效的方法應該是執行它以將相關數據頁提取到 RAM 中。

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