public inbox for [email protected]help / color / mirror / Atom feed
Index Partition Size Double of its Table Partition? 5+ messages / 2 participants [nested] [flat]
* Index Partition Size Double of its Table Partition? @ 2024-10-30 15:24 Don Seiler <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Don Seiler @ 2024-10-30 15:24 UTC (permalink / raw) To: pgsql-general We're trying out a new non-unique covering (including) index on a couple of table partitions. We put the index on partitions for last month and this month. Both table partitions have similar sizes (45-46 GB) and row counts (330-333 million). The covering index on last month's partition is 50GB, but this month's index is 79GB already. The table is basically write-only as well. So there shouldn't be any real bloat here. 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 explanation 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. -- Don Seiler www.seiler.us ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index Partition Size Double of its Table Partition? @ 2024-10-30 15:34 Peter Geoghegan <[email protected]> parent: Don Seiler <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Peter Geoghegan @ 2024-10-30 15:34 UTC (permalink / raw) To: Don Seiler <[email protected]>; +Cc: pgsql-general On Wed, Oct 30, 2024 at 11:24 AM Don Seiler <[email protected]> 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 explanation 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=p5RaATILoiE&t=2079s https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f21668f3 Of course, this is only a guess. I vaguely recall a complaint that sounded vaguely like yours, also involving partitioning. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index Partition Size Double of its Table Partition? @ 2024-10-30 15:39 Don Seiler <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Don Seiler @ 2024-10-30 15:39 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: pgsql-general On Wed, Oct 30, 2024 at 10:35 AM Peter Geoghegan <[email protected]> wrote: > On Wed, Oct 30, 2024 at 11:24 AM Don Seiler <[email protected]> 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 explanation > 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=p5RaATILoiE&t=2079s > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f21668f3 > > 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. -- Don Seiler www.seiler.us ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index Partition Size Double of its Table Partition? @ 2024-10-30 15:44 Peter Geoghegan <[email protected]> parent: Don Seiler <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Peter Geoghegan @ 2024-10-30 15:44 UTC (permalink / raw) To: Don Seiler <[email protected]>; +Cc: pgsql-general On Wed, Oct 30, 2024 at 11:39 AM Don Seiler <[email protected]> wrote: > Thanks Peter, I'll look into that shortly. 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). 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 we 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. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index Partition Size Double of its Table Partition? @ 2024-10-30 16:08 Don Seiler <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Don Seiler @ 2024-10-30 16:08 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: pgsql-general On Wed, Oct 30, 2024 at 10:45 AM Peter Geoghegan <[email protected]> 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 we > 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. -- Don Seiler www.seiler.us ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-10-30 16:08 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-10-30 15:24 Index Partition Size Double of its Table Partition? Don Seiler <[email protected]> 2024-10-30 15:34 ` Peter Geoghegan <[email protected]> 2024-10-30 15:39 ` Don Seiler <[email protected]> 2024-10-30 15:44 ` Peter Geoghegan <[email protected]> 2024-10-30 16:08 ` 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