After reading lots of documentation, I still don't understand fully how PG knows if some needed data is in memory or in second storage.
While choosing the best query plan, the optimizer must take this into account. Does PG consider this? If so, how does it know?
I presume it checks its shared buffer. But if the data is not in the shared buffer, it could be on OS cache, and the plan would not be optimized.
Can PG check the OS cache? If not, considering a dedicated DB server, is it advised to raise the shared buffer size up to a maximum that does not influence the rest of the system? This way, the shared buffer check would have a higher probability of returning a correct answer.
When setting seq_page_cost and random_page_cost, do I have to consider the probability that data will be in memory? Or does seq_page_cost mean "sequential access on disk" and random_page_cost mean "random access on disk"?