I was able to pull some stats with pgstattuple and nothing looks particularly hinky to me.
version: 4 tree_level: 2 index_size: 499589120 root_block_no: 412 internal_pages: 194 leaf_pages: 54572 empty_pages: 0 deleted_pages: 6218 avg_leaf_density: 90.08 leaf_fragmentation: 0.01 For flavor, If I remember correctly, the table has around 50mil rows, and around 17mil of them should be included in the partial index due to the "where payload is not null" predicate. 0 deleted pages would be nicer than ~6k, but by my count, that's around 10% of the total index size. I also assume if the index was not cleaned up during regular operations this number would be much larger. I think this points away from index bloat as the culprit, but please check me on this. We're checking assumptions about when/how often the table is getting analyzed, but other than possibly using extended stats it sounds like the only other odd thing is "Aurora not being Postgres," which I'm not sure there's much I can do about right now :( On Tue, Nov 28, 2023 at 9:23 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 11/28/23 18:13, Owen Nelson wrote: > > > Aurora is not really Postgres > > > > Oh geez, I didn't realize there was such a divide. This is my first look > > at Aurora and I thought it was just a hosted postgres offering. > > > https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html#aur-shared-resp > > > "Aurora includes a high-performance storage subsystem. Its MySQL- and > PostgreSQL-compatible database engines are customized to take advantage > of that fast distributed storage. " > > When I see things like *-compatible alarms start going off. > > > > > Still, I'll take what I can get. Hopefully, some of this will carry over. > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >