Re: multicolumn index and setting effective_cache_size using human-readable-numbers

On Mon, Feb 29, 2016 at 2:10 PM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> I'm not really sure what changes I could make that would make one
> index that's ostensibly equivalent to the other not be attractive to
> the planner though. I can mess with those figures but as I said before
> the only one that flicks the switch is to change effective_cache_size
> to 8GB, which makes no sense to me.

effective_cache_size doesn't affect how memory is allocated, it
tells the optimizer what to assume about the combined cache space
(essentially shared_buffers + OS cache) so that it can estimate the
amount of random storage I/O needed to use an indexed plan. If you
tell it that you only have 64MB between those two types of cache,
it will assume that the index (particularly if it is deep and/or
wide) will be very expensive.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company