Re: Understanding partial index selection

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
>