[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers
From:       Geoff Winkless <pgsqladmin () geoff ! dj>
Date:       2016-02-29 23:02:25
Message-ID: CAEzk6fcu8issevJHKvfdwj3+YCBOOPWYxeZzpey9S9201LBbuQ () mail ! gmail ! com
[Download RAW message or body]

On 29 Feb 2016 22:47, "Kevin Grittner" <kgrittn@gmail.com> wrote:
>
> On Mon, Feb 29, 2016 at 2:10 PM, Geoff Winkless <pgsqladmin@geoff.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

[Attachment #3 (text/html)]

<p dir="ltr"><br>
On 29 Feb 2016 22:47, &quot;Kevin Grittner&quot; &lt;<a \
href="mailto:kgrittn@gmail.com">kgrittn@gmail.com</a>&gt; wrote:<br> &gt;<br>
&gt; On Mon, Feb 29, 2016 at 2:10 PM, Geoff Winkless &lt;<a \
href="mailto:pgsqladmin@geoff.dj">pgsqladmin@geoff.dj</a>&gt; wrote:<br> &gt;<br>
&gt; &gt; I&#39;m not really sure what changes I could make that would make one<br>
&gt; &gt; index that&#39;s ostensibly equivalent to the other not be attractive \
to<br> &gt; &gt; the planner though. I can mess with those figures but as I said \
before<br> &gt; &gt; the only one that flicks the switch is to change \
effective_cache_size<br> &gt; &gt; to 8GB, which makes no sense to me.<br>
&gt;<br>
&gt; effective_cache_size doesn&#39;t affect how memory is allocated, it<br>
&gt; tells the optimizer what to assume about the combined cache space<br>
&gt; (essentially shared_buffers + OS cache) so that it can estimate the<br>
&gt; amount of random storage I/O needed to use an indexed plan.   If you<br>
&gt; tell it that you only have 64MB between those two types of cache,<br>
&gt; it will assume that the index (particularly if it is deep and/or<br>
&gt; wide) will be very expensive</p>
<p dir="ltr">Perhaps I&#39;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&#39;s \
suddenly used.</p> <p dir="ltr">For the variation in size of the two indexes (say \
20%?) that just doesn&#39;t add up.</p> <p dir="ltr">Geoff</p>



[prev in list] [next in list] [prev in thread] [next in thread]