public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Cc: alex work <[email protected]>
Cc: Robert Haas <[email protected]>
Subject: Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs
Date: Fri, 22 Mar 2024 11:39:52 -0500
Message-ID: <20240322163952.GA2347986@nathanxps13> (raw)
In-Reply-To: <[email protected]>
References: <CAGvXd3OSMbJQwOSc-Tq-Ro1CAz=vggErdSG7pv2s6vmmTOLJSg@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<20240321204012.GA1992418@nathanxps13>
<20240322005139.GA2060816@nathanxps13>
<[email protected]>
<20240322144739.GA2321246@nathanxps13>
<20240322145532.GA2346110@nathanxps13>
<[email protected]>
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
view thread (19+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs
In-Reply-To: <20240322163952.GA2347986@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox