public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter Geoghegan <[email protected]>
To: Don Seiler <[email protected]>
Cc: Postgres General <[email protected]>
Subject: Re: Index Partition Size Double of its Table Partition?
Date: Wed, 30 Oct 2024 12:22:37 -0400
Message-ID: <CAH2-WznOOfi5L9TmeyzFT+-Hs23edNmJerDzFaRWGE5X0rJD=g@mail.gmail.com> (raw)
In-Reply-To: <CAHJZqBDhGmMCTp-7bDXDq4k-b625G0r00VXx1wH0=kLEcetDhQ@mail.gmail.com>
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>

On Wed, Oct 30, 2024 at 12:08 PM Don Seiler <[email protected]> wrote:
> Why would last month's index be so much smaller?

Because the split heuristics worked as designed there. That's the
theory, at least.

> 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.

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.

-- 
Peter Geoghegan

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Index Partition Size Double of its Table Partition?
  In-Reply-To: <CAH2-WznOOfi5L9TmeyzFT+-Hs23edNmJerDzFaRWGE5X0rJD=g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox