Question regarding the operation of the shared_buffers cache and implications of the pg_X_stat_tables|pg_X_stat_indexes stats.
( I am also aware that this is all complicated by the kernel cache behavior, however, if, for the purpose of these questions, you wouldn’t mind assuming that we don’t have a kernel cache, and therefore just focus on the behavior of the db cache as an isolated component, it will help – thanks in advance).
What is the procedure that postgres uses to decide whether or not a table/index block will be left in the shared_buffers cache at the end of the operation?
Are there any particular types of *table* access operations that will cause postgres to choose not to retain the table pages in shared_buffers at the end of the operation?
In particular, the activity tracked by:
- Seq_scan
- Seq_tup_read
- Idx_tup_read
- Idx_tup_fetch
Are there any particular types of *index* access operations that will cause postgres to choose not to retain the index pages in shared_buffers at the end of the operation?
In particular, the activity tracked by:
- idx_scan
- Idx_tup_read
- Idx_tup_fetch