Using pg_prewarm to load X number of latest rows into cache
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 name *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.