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 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.klaver@aklaver.com