Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs - Mailing list pgsql-hackers

From Nathan Bossart
Subject Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs
Date
Msg-id 20240322163952.GA2347986@nathanxps13
Whole thread Raw
In response to Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: documentation structure
Next
From: Robert Haas
Date:
Subject: Re: pg_upgrade --copy-file-range