Using pg_prewarm to load X number of latest rows into cache

Question

We have a large query that is extra slow when customers "run it the first time, early in the morning..."

So, I found pg_prewarm that I would like to use load into PG's buffer cache a certain amount or most recently accessed rows (inserted, updated, or deleted) from several tables that are used in the said query.

Furthermore, I need to make sure that 'warm up' does not exceed PG's cache (shared_buffers setting I believe, or am I wrong?) To prewarm last 1000 pages of a single table I can do:

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

Question #1: Does this approach makes sense?

The trick is that pg_prewarm can only load a certain number of pages, so I need to calculate "how many live rows are in page for certain table"

-- 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

Question #2: Is my query above correct? Is this the proper way to get "live rows per page"? Question #3: the live_row_size I get from above query is not the same as the result mentioned in this answer (by Erwin). Am I doing something wrong?

Based on live_rows_per_page I can then modify pg_prewarm to load enough last XXXX pages that contain 10,000 rows (6.37 x 10,000)

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
    )
);

Update #1 Regarding the Question #3, I get following when I run the query for mytable... The output of pgstatuple is is different, probably because it is not listing the same items, but I am not sure...

"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>

Answer 1

Addressing Question #3

the live_row_size I get from above query is not the same as the result mentioned in this answer (by Erwin). Am I doing something wrong?

You have:

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

The manual defines:

tuple_len ... Total length of live tuples in bytes
tuple_count ... Number of live tuples

You compute the medium length of live tuples. It seems like you expect Postgres to extract only live tuples when fetching data pages into RAM, but that's not how it works. Postgres just reads full pages into RAM, including any dead tuples that may be contained.

Hence, your next expression does not compute "live rows per page":

8192.00 / (tuple_len / tuple_count) AS live_rows_per_page

It computes "hypothetical maximum of live rows per data page with only live tuples", which is nice to know, but otherwise useless for your task.

Addressing Update #1

Why different sizes? In the referenced answer, I am leading with this:

This is going to demonstrate that the various methods to measure the "size of a row" can lead to very different results. It all depends what you want to measure exactly.

Your findings seem to confirm that - though I am not sure what you compare exactly.

The referenced answer is a bit outdated. I improved some details and added numbers from pgstattuple for comparison. You need to install the module once per database (well you obviously have it, but maybe other readers):

CREATE EXTENSION pgstattuple;

Then:

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);

Simple solution?

If your query does not have side effects, the most efficient method should be to just execute it to fetch relevant data pages into RAM.