The Effectiveness of effective_cache_size

Having reported the methods for finding the size of the kernel cache on
Linux, I wish to highlight the importance of the postgresql.conf setting
effective_cache_size.

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.

Continue Reading »