MIME-Version: 1.0
References: <CAHJZqBA83fBGjqXqF+rVbErdy2N0_zDEiLWJMrDUSKjKQOZi3Q@mail.gmail.com>
 <CAH2-Wzmq+ZLkYFLq-WGJhd=xJ0UosQMuLCMbHXKcKucwsSL=eg@mail.gmail.com>
 <CAHJZqBBrXD5ca+BcoKSg0yBbehtos9Zt+zd0gN1J2L6xP+4jJg@mail.gmail.com>
 <CAH2-Wzki5sKPxi9OzS+ir+wHzygC5bM0+PZuTj-aXWNW1tnZiQ@mail.gmail.com>
 <CAHJZqBDhGmMCTp-7bDXDq4k-b625G0r00VXx1wH0=kLEcetDhQ@mail.gmail.com>
 <CAH2-WznOOfi5L9TmeyzFT+-Hs23edNmJerDzFaRWGE5X0rJD=g@mail.gmail.com> <CAHJZqBCgYwWns2cRsbk5NWnLHXmmQPdKn7A+dknbA2sWH5TCUQ@mail.gmail.com>
In-Reply-To: <CAHJZqBCgYwWns2cRsbk5NWnLHXmmQPdKn7A+dknbA2sWH5TCUQ@mail.gmail.com>
From: David Mullineux <dmullx@gmail.com>
Date: Wed, 30 Oct 2024 21:59:07 +0000
Message-ID: <CAGsyd8WXZr5-SaAnf2J7MiJheWfxrWTp9+fFhBgB8z93dPC_Vw@mail.gmail.com>
Subject: Re: Index Partition Size Double of its Table Partition?
To: Don Seiler <don@seiler.us>
Cc: Peter Geoghegan <pg@bowt.ie>, Postgres General <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003b2c5c0625b8d02b"
Archived-At: <https://www.postgresql.org/message-id/CAGsyd8WXZr5-SaAnf2J7MiJheWfxrWTp9%2BfFhBgB8z93dPC_Vw%40mail.gmail.com>
Precedence: bulk

--0000000000003b2c5c0625b8d02b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Are you able to cluster the table ? The idea is that rows ordered in the
same way  as the index might reduce it's size ?

On Wed, 30 Oct 2024, 16:29 Don Seiler, <don@seiler.us> wrote:

> On Wed, Oct 30, 2024 at 11:23=E2=80=AFAM Peter Geoghegan <pg@bowt.ie> wro=
te:
>
>>
>> If a substantial amount of the index was written by CREATE INDEX (and
>> not by retail inserts) then my theory is unlikely to be correct. It
>> could just be that you managed to absorb most inserts in one
>> partition, but not in the other. That's probably possible when there
>> are only relatively small differences in the number of inserts that
>> need to use of the space left behind by fillfactor in each case. In
>> general page splits tend to come in distinct "waves" after CREATE
>> INDEX is run.
>>
>
> What do you mean by "absorb" the inserts?
>
> It sounds like the answer will be "No", but: Would rebuilding the index
> after the month-end (when inserts have stopped on this partition) change
> anything?
>
> Don.
> --
> Don Seiler
> www.seiler.us
>

--0000000000003b2c5c0625b8d02b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<p dir=3D"ltr">Are you able to cluster the table ? The idea is that rows or=
dered in the same way=C2=A0 as the index might reduce it&#39;s size ? </p>
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Wed=
, 30 Oct 2024, 16:29 Don Seiler, &lt;<a href=3D"mailto:don@seiler.us">don@s=
eiler.us</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D=
"ltr"><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On W=
ed, Oct 30, 2024 at 11:23=E2=80=AFAM Peter Geoghegan &lt;<a href=3D"mailto:=
pg@bowt.ie" target=3D"_blank" rel=3D"noreferrer">pg@bowt.ie</a>&gt; wrote:<=
br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
If a substantial amount of the index was written by CREATE INDEX (and<br>
not by retail inserts) then my theory is unlikely to be correct. It<br>
could just be that you managed to absorb most inserts in one<br>
partition, but not in the other. That&#39;s probably possible when there<br=
>
are only relatively small differences in the number of inserts that<br>
need to use of the space left behind by fillfactor in each case. In<br>
general page splits tend to come in distinct &quot;waves&quot; after CREATE=
<br>
INDEX is run.<br>
</blockquote></div><div><br></div><div>What do you mean by &quot;absorb&quo=
t; the inserts?<br></div><div><br></div><div>It sounds like the answer will=
 be &quot;No&quot;, but: Would rebuilding the index after the month-end (wh=
en inserts have stopped on this partition) change anything?</div><div><br><=
/div><div>Don.<br></div><span class=3D"gmail_signature_prefix">-- </span><b=
r><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>Don Seil=
er<br><a href=3D"http://www.seiler.us" target=3D"_blank" rel=3D"noreferrer"=
>www.seiler.us</a></div></div></div></div>
</blockquote></div>

--0000000000003b2c5c0625b8d02b--