Sample Header Ad - 728x90

how to avoid the postgresql 14 shared buffer swap out

0 votes
0 answers
22 views
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.
Asked by Dolphin (939 rep)
May 20, 2025, 03:51 AM