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>
In-Reply-To: <CAH2-Wzki5sKPxi9OzS+ir+wHzygC5bM0+PZuTj-aXWNW1tnZiQ@mail.gmail.com>
From: Don Seiler <don@seiler.us>
Date: Wed, 30 Oct 2024 11:08:27 -0500
Message-ID: <CAHJZqBDhGmMCTp-7bDXDq4k-b625G0r00VXx1wH0=kLEcetDhQ@mail.gmail.com>
Subject: Re: Index Partition Size Double of its Table Partition?
To: Peter Geoghegan <pg@bowt.ie>
Cc: Postgres General <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002f85c30625b3ead1"
Archived-At: <https://www.postgresql.org/message-id/CAHJZqBDhGmMCTp-7bDXDq4k-b625G0r00VXx1wH0%3DkLEcetDhQ%40mail.gmail.com>
Precedence: bulk

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

On Wed, Oct 30, 2024 at 10:45=E2=80=AFAM Peter Geoghegan <pg@bowt.ie> wrote=
:

>
> It sounds like you have no updates and deletes. Right? So the only
> thing that could be different is the way that the pages are being
> split (aside from variations in the width of index tuples, which seems
> highly unlikely to be the only factor).
>

Correct, the table only sees inserts as far as DML goes.


> The heuristics used to trigger the relevant behavior are fairly
> conservative. I wonder if we should be more aggressive about it.
>
> > I should have also included that this is on PG 15 (currently 15.8 but w=
e
> created the indexes when it was still 15.7) on Ubuntu 22.04 LTS.
>
> That shouldn't matter, as far as this theory of mine is concerned.
> Anything after 12 could be affected by the issue I'm thinking of.
>

Why would last month's index be so much smaller?

Both indexes were created using CONCURRENTLY, as each was created during
its month when we started testing. The September index was created toward
the end of the month (Sep 26), whereas the October one was created Oct 1.
Both table partitions are getting regularly autovacuum/autoanalyze work.

Don.
--=20
Don Seiler
www.seiler.us

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

<div dir=3D"ltr"><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail=
_attr">On Wed, Oct 30, 2024 at 10:45=E2=80=AFAM Peter Geoghegan &lt;<a href=
=3D"mailto:pg@bowt.ie">pg@bowt.ie</a>&gt; wrote:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><br>
It sounds like you have no updates and deletes. Right? So the only<br>
thing that could be different is the way that the pages are being<br>
split (aside from variations in the width of index tuples, which seems<br>
highly unlikely to be the only factor).<br></blockquote><div><br></div><div=
>Correct, the table only sees inserts as far as DML goes.<br></div><div>=C2=
=A0<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
The heuristics used to trigger the relevant behavior are fairly<br>
conservative. I wonder if we should be more aggressive about it.<br>
<br>
&gt; I should have also included that this is on PG 15 (currently 15.8 but =
we created the indexes when it was still 15.7) on Ubuntu 22.04 LTS.<br>
<br>
That shouldn&#39;t matter, as far as this theory of mine is concerned.<br>
Anything after 12 could be affected by the issue I&#39;m thinking of.<br></=
blockquote><div><br></div><div>Why would last month&#39;s index be so much =
smaller?</div><div><br></div><div>Both indexes were created using CONCURREN=
TLY, as each was created during its month when we started testing. The Sept=
ember index was created toward the end of the month (Sep 26), whereas the O=
ctober one was created Oct 1.=C2=A0 Both table partitions are getting regul=
arly autovacuum/autoanalyze work.<br></div></div><div><br></div><div>Don.<b=
r></div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"lt=
r" class=3D"gmail_signature"><div dir=3D"ltr"><div>Don Seiler<br><a href=3D=
"http://www.seiler.us" target=3D"_blank">www.seiler.us</a></div></div></div=
></div>

--0000000000002f85c30625b3ead1--