Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Fri, Mar 22, 2024 at 11:27:46AM -0400, Tom Lane wrote:
> Yeah, that's about what I'd expect: hash+bloom ought to remove
> most (not quite all) of the opportunity for simd to shine, because
> the bloom filter should eliminate most of the list_member_oid calls.

Right.  IMHO the SIMD work is still worth considering because there are
probably even more extreme cases where it'll make a decent amount of
difference.  Plus, that stuff is pretty low overhead for what you get in
return.  That being said, the hash table and Bloom filter should definitely
be the higher priority.

> * Same question for the bloom logic, but here I think it's mostly
> a matter of tuning those constants.

I suspect there might be some regressions just after the point where we
construct the filter, but I'd still expect that to be a reasonable
trade-off.  We could probably pretty easily construct some benchmarks to
understand the impact with a given number of roles.  (I'm not sure I'll be
able to get to that today.)

> * Do we want to risk back-patching any of this, to fix the performance
> regression in v16?  I think that the OP's situation is a pretty
> narrow one, but maybe he's not the only person who managed to dodge
> roles_is_member_of's performance issues in most other cases.

I've heard complaints about performance with many roles before, so I
certainly think this area is worth optimizing.  As far as back-patching
goes, my current feeling is that the hash table is probably pretty safe and
provides the majority of the benefit, but anything fancier should probably
be reserved for v17 or v18.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com