public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Nathan Bossart <[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 12:53:15 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <20240322163952.GA2347986@nathanxps13>
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]>
	<20240322163952.GA2347986@nathanxps13>

Nathan Bossart <[email protected]> writes:
> On Fri, Mar 22, 2024 at 11:27:46AM -0400, Tom Lane wrote:
>> * 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.

Yeah.  Although both the catcache and list_append_unique_oid bits
are O(N^2), the catcache seems to have a much bigger constant
factor --- when I did a "perf" check on the unpatched code,
I saw catcache eating over 90% of the runtime and list_member_oid
about 2%.  So let's fix that part in v16 and call it a day.
It should be safe to back-patch the catcache changes as long as
we put the new fields at the end of the struct and leave cc_lists
present but empty.

Would you like to review the catcache patch further, or do you
think it's good to go?

			regards, tom lane

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: <[email protected]>

* 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