How does PG know if data is in memory?

From: Fabrício dos Anjos Silva <fabricio(dot)silva(at)linkcom(dot)com(dot)br>
To: pgsql-performance(at)postgresql(dot)org
Subject: How does PG know if data is in memory?
Date: 2010-09-29 16:31:20
Message-ID: AANLkTin5G0jXUbeNdC+1s9LQpk1ZpvDuX-6n90oonen3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

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"?

I appreciate if someone could clear this out.

Thanks!

Fabrício dos Anjos Silva
LinkCom Soluções em T.I.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kevin Grittner 2010-09-29 16:36:45 Re: How does PG know if data is in memory?
Previous Message Tobias Brox 2010-09-29 12:09:44 Re: Memory usage - indexes