public inbox for [email protected]help / color / mirror / Atom feed
Re: Index Partition Size Double of its Table Partition? 6+ messages / 4 participants [nested] [flat]
* Re: Index Partition Size Double of its Table Partition? @ 2024-10-30 16:22 Peter Geoghegan <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Peter Geoghegan @ 2024-10-30 16:22 UTC (permalink / raw) To: Don Seiler <[email protected]>; +Cc: pgsql-general 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index Partition Size Double of its Table Partition? @ 2024-10-30 16:28 Don Seiler <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Don Seiler @ 2024-10-30 16:28 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: pgsql-general On Wed, Oct 30, 2024 at 11:23 AM Peter Geoghegan <[email protected]> wrote: > > 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index Partition Size Double of its Table Partition? @ 2024-10-30 21:59 David Mullineux <[email protected]> parent: Don Seiler <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: David Mullineux @ 2024-10-30 21:59 UTC (permalink / raw) To: Don Seiler <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; pgsql-general 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, <[email protected]> wrote: > On Wed, Oct 30, 2024 at 11:23 AM Peter Geoghegan <[email protected]> wrote: > >> >> 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 > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index Partition Size Double of its Table Partition? @ 2024-10-31 16:01 Don Seiler <[email protected]> parent: David Mullineux <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Don Seiler @ 2024-10-31 16:01 UTC (permalink / raw) To: David Mullineux <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; pgsql-general On Wed, Oct 30, 2024 at 4:59 PM David Mullineux <[email protected]> wrote: > 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 ? > I'm not sure on this. There are other indexes on these table partitions as well. Another bit of useful info that I should have shared immediately is that this is a monthly partitioned table, going back years. We don't drop old partitions (yet) on this one. For now we've only added this index to a few individual partitions. The hope was to add it to all of them and then eventually the template (using an older version of pg_partman). Don. -- Don Seiler www.seiler.us ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index Partition Size Double of its Table Partition? @ 2024-10-31 16:13 Rob Sargent <[email protected]> parent: Don Seiler <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Rob Sargent @ 2024-10-31 16:13 UTC (permalink / raw) To: Don Seiler <[email protected]>; +Cc: David Mullineux <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-general --Apple-Mail-BFA07331-786C-46B7-95ED-890100B3C86D Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D= utf-8"></head><body dir=3D"auto"><div dir=3D"ltr"></div><div dir=3D"ltr"><br= ></div><div dir=3D"ltr"><br><blockquote type=3D"cite">On Oct 31, 2024, at 10= :02=E2=80=AFAM, Don Seiler <[email protected]> wrote:<br><br></blockquote>= </div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<div dir=3D"ltr"><= div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Oct 3= 0, 2024 at 4:59=E2=80=AFPM David Mullineux <<a href=3D"mailto:dmullx@gmai= l.com">[email protected]</a>> wrote:<br></div><blockquote class=3D"gmail_q= uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20= 4);padding-left:1ex"><p dir=3D"ltr">Are you able to cluster the table ? The i= dea is that rows ordered in the same way as the index might reduce it'= s size ? </p></blockquote><div>I'm not sure on this. There are other indexes= on these table partitions as well.<br></div><div><br></div><div>Another bit= of useful info that I should have shared immediately is that this is a mont= hly partitioned table, going back years. We don't drop old partitions (yet) o= n this one. For now we've only added this index to a few individual partitio= ns. The hope was to add it to all of them and then eventually the template (= using an older version of pg_partman).</div><div><br></div><div>Don. <br></d= iv></div><span class=3D"gmail_signature_prefix">-- </span><br></div></div></= blockquote>Whats the fill factor?<div><br></div></body></html>= --Apple-Mail-BFA07331-786C-46B7-95ED-890100B3C86D-- ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index Partition Size Double of its Table Partition? @ 2024-11-07 03:41 Don Seiler <[email protected]> parent: Rob Sargent <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Don Seiler @ 2024-11-07 03:41 UTC (permalink / raw) To: Rob Sargent <[email protected]>; +Cc: David Mullineux <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-general On Thu, Oct 31, 2024 at 11:13 AM Rob Sargent <[email protected]> wrote: > Whats the fill factor? > No fill factor is specified, so I'm assuming it's the default 90% for indexes. FYI we did a REINDEX for the index in question tonight. Since the index was for last month, there are no more writes to it so we didn't use CONCURRENTLY either. The size went from 83GB to 48GB, which also happens to be the size of the table partition. -- Don Seiler www.seiler.us ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-11-07 03:41 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-10-30 16:22 Re: Index Partition Size Double of its Table Partition? Peter Geoghegan <[email protected]> 2024-10-30 16:28 ` Don Seiler <[email protected]> 2024-10-30 21:59 ` David Mullineux <[email protected]> 2024-10-31 16:01 ` Don Seiler <[email protected]> 2024-10-31 16:13 ` Rob Sargent <[email protected]> 2024-11-07 03:41 ` Don Seiler <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox