how to avoid the postgresql 14 shared buffer swap out

Question

I am facing a issue that the sql first time query was slow. I read the execution plan and found out the index are not in shared buffers the first time query. then I prewarm the index like this:

SELECT pg_prewarm('public.pk_w_03002_kt_h','buffer');

and check the index that exists in shared buffer:

SELECT
     c.relname,
     pg_size_pretty(count(*) * 8192) as buffered,
     round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer, 1) AS buffer_percent,
     round(100.0 * count(*) * 8192 / pg_table_size(c.oid), 1) AS percent_of_relation
 FROM pg_class c
 INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
 GROUP BY c.oid, c.relname
 ORDER BY 3 DESC
 LIMIT 10;

Now I facing a issue that the cached index will be swap out sometimes. Is it possible to keep the index always in the shared buffer? the index size was 60MB and the shared buffer config is 8GB. it still not solved the first time query slow issue.

how to avoid the postgresql 14 shared buffer swap out

0 Answers0