Re: SSD options, small database, ZFS

On 18 Listopad 2011, 17:17, Scott Marlowe wrote:
> On Fri, Nov 18, 2011 at 3:09 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
>> On 11/17/2011 10:44 PM, CSS wrote:
>>>
>>> Is there any sort of simple documentation on the query planner that
>>> might
>>> cover how things like increased RAM could impact how a query is
>>> executed?
>>
>> There is no *simple* documentation on any part of the query planner
>> that's
>> also accurate.  Query planning is inherently complicated.
>>
>> I think this point wasn't quite made clearly.  PostgreSQL has no idea
>> how
>> much memory is in your system; it doesn't try to guess or detect it.
>
> effective_cache_size tells the db how much memory you have. Since you
> have to set it, it can be anything you want, but if you've set it to
> something much higher on the new machine then it can affect query
> planning.

That's only half of the truth. effective_cache_size is used to estimate
the page cache hit ratio, nothing else. It influences the planning a bit
(AFAIK it's used only to estimate a nested loop with inner index scan) but
it has no impact on things like work_mem, maintenance_work_mem,
wal_buffers etc.

People often bump these settings up (especially work_mem) on new hw
without properly testing the impact. PostgreSQL will happily do that
because it was commanded to, but then the system starts swapping or the
OOM killer starts shooting the processes.

Tomas