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

On 29 Feb 2016 22:47, "Kevin Grittner" <kgrittn(at)gmail(dot)com> wrote:
>
> 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

Perhaps I'm not being clear. Index 1 has field a and is used in the join no
matter how small I set effective_cache_size (even 32mb). Index 2 has fields
a,b but will not be used at ecs of 3gb, 6gb, whatever up til 8gb, when it's
suddenly used.

For the variation in size of the two indexes (say 20%?) that just doesn't
add up.

Geoff