Friday, May 4, 2012
Unlike other memory settings that control how memory is allocated, effective_cache_size tells the optimizer how much cache is present in the kernel. This is important for determining how expensive large index scans will be. The optimizer knows the size of shared_buffers, but not the kernel cache size, which affects the probability of expensive disk access.
The kernel cache size changes frequently, so run free during a period of normal system load and use that value to set effective_cache_size. The value doesn't have to be perfect, but just a rough estimate of how much kernel memory is acting as secondary cache for the shared buffers.
Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value of Postgres advances and new technology enhancements. He is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.