MIME-Version: 1.0
References: <CAHJZqBA83fBGjqXqF+rVbErdy2N0_zDEiLWJMrDUSKjKQOZi3Q@mail.gmail.com>
 <CAH2-Wzmq+ZLkYFLq-WGJhd=xJ0UosQMuLCMbHXKcKucwsSL=eg@mail.gmail.com>
In-Reply-To: <CAH2-Wzmq+ZLkYFLq-WGJhd=xJ0UosQMuLCMbHXKcKucwsSL=eg@mail.gmail.com>
From: Don Seiler <don@seiler.us>
Date: Wed, 30 Oct 2024 10:39:28 -0500
Message-ID: <CAHJZqBBrXD5ca+BcoKSg0yBbehtos9Zt+zd0gN1J2L6xP+4jJg@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="0000000000008c755d0625b382dd"
Archived-At: <https://www.postgresql.org/message-id/CAHJZqBBrXD5ca%2BBcoKSg0yBbehtos9Zt%2Bzd0gN1J2L6xP%2B4jJg%40mail.gmail.com>
Precedence: bulk

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

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

> On Wed, Oct 30, 2024 at 11:24=E2=80=AFAM Don Seiler <don@seiler.us> wrote=
:
> > One thing worth mentioning is that the table is 4 columns, the index is
> on two of them and includes the other two. I can't think of an explanatio=
n
> for the index being so much larger than its table, especially compared to
> last month's index.
> >
> > Curious if anyone has any thoughts on what might be causing this.
>
> You mentioned that this has 4 columns. Sounds like this could be a
> "locally monotonically increasing index". I wonder if you're
> benefiting from this optimization, though only inconsistently:
>
> https://www.youtube.com/watch?v=3Dp5RaATILoiE&t=3D2079s
> https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dcommit;h=3Df216=
68f3
>
> Of course, this is only a guess. I vaguely recall a complaint that
> sounded vaguely like yours, also involving partitioning.
>

Thanks Peter, I'll look into that shortly.

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.

Don.

--=20
Don Seiler
www.seiler.us

--0000000000008c755d0625b382dd
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:35=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">On Wed, Oct 30, 2024 at 11:24=E2=80=AFAM D=
on Seiler &lt;<a href=3D"mailto:don@seiler.us" target=3D"_blank">don@seiler=
.us</a>&gt; wrote:<br>
&gt; One thing worth mentioning is that the table is 4 columns, the index i=
s on two of them and includes the other two. I can&#39;t think of an explan=
ation for the index being so much larger than its table, especially compare=
d to last month&#39;s index.<br>
&gt;<br>
&gt; Curious if anyone has any thoughts on what might be causing this.<br>
<br>
You mentioned that this has 4 columns. Sounds like this could be a<br>
&quot;locally monotonically increasing index&quot;. I wonder if you&#39;re<=
br>
benefiting from this optimization, though only inconsistently:<br>
<br>
<a href=3D"https://www.youtube.com/watch?v=3Dp5RaATILoiE&amp;t=3D2079s" rel=
=3D"noreferrer" target=3D"_blank">https://www.youtube.com/watch?v=3Dp5RaATI=
LoiE&amp;t=3D2079s</a><br>
<a href=3D"https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dcommit=
;h=3Df21668f3" rel=3D"noreferrer" target=3D"_blank">https://git.postgresql.=
org/gitweb/?p=3Dpostgresql.git;a=3Dcommit;h=3Df21668f3</a><br>
<br>
Of course, this is only a guess. I vaguely recall a complaint that<br>
sounded vaguely like yours, also involving partitioning.<br>
</blockquote></div><br clear=3D"all"><div>Thanks Peter, I&#39;ll look into =
that shortly.</div><div><br></div><div>I should have also included that thi=
s is on PG 15 (currently 15.8 but we created the indexes when it was still =
15.7) on Ubuntu 22.04 LTS.</div><div><br></div><div>Don.<br></div><div><br>=
</div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr"=
 class=3D"gmail_signature"><div dir=3D"ltr"><div>Don Seiler<br><a href=3D"h=
ttp://www.seiler.us" target=3D"_blank">www.seiler.us</a></div></div></div><=
/div>

--0000000000008c755d0625b382dd--