1

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

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.

  • "Postgres just reads full pages into RAM, including any dead tuples that may be contained." - this is good to know and it makes sense. Haven't thought about it...I guess there is no way to find out row size per page then? Reliably? – zam6ak Nov 3 '16 at 12:47
  • As for Simple solution - this is what we settled on using. We have a job that kicks of few queries at the certain time. I just thought pg_prewarm may be better. – zam6ak Nov 3 '16 at 12:49
  • @zam6ak: There are various ways to find out about "row size" (reliably) - depending on what you want to measure exactly. Matters are complicated further since the same row can have a different size in RAM vs. on disk. – Erwin Brandstetter Nov 3 '16 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.