Date: Tue, 26 Mar 2024 13:48:19 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@lists.postgresql.org, alex work <alexwork033@gmail.com>,
	Robert Haas <robertmhaas@gmail.com>
Subject: Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs
Message-ID: <20240326184819.GA3559028@nathanxps13>
References: <20240322163952.GA2347986@nathanxps13>
 <939945.1711126395@sss.pgh.pa.us>
 <20240322165448.GA2350322@nathanxps13>
 <20240322193506.GA2352051@nathanxps13>
 <1084642.1711140109@sss.pgh.pa.us>
 <20240325144743.GA2618709@nathanxps13>
 <2490242.1711379319@sss.pgh.pa.us>
 <20240325151647.GA2662364@nathanxps13>
 <20240326165918.GA3350222@nathanxps13>
 <2999167.1711476963@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="3V7upXqbjpZ4EhLz"
Content-Disposition: inline
In-Reply-To: <2999167.1711476963@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/20240326184819.GA3559028%40nathanxps13>
Precedence: bulk


--3V7upXqbjpZ4EhLz
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Tue, Mar 26, 2024 at 02:16:03PM -0400, Tom Lane wrote:
> I did a little experimentation using the attached quick-hack C
> function, and came to the conclusion that setting up the bloom filter
> costs more or less as much as inserting 1000 or so OIDs the dumb way.
> So we definitely want a threshold that's not much less than that.

Thanks for doing this.

> So I'm now content with choosing a threshold of 1000 or 1024 or so.

Cool.

> As for the bloom filter size, I see that bloom_create does
> 
> 	bitset_bytes = Min(bloom_work_mem * UINT64CONST(1024), total_elems * 2);
> 	bitset_bytes = Max(1024 * 1024, bitset_bytes);
> 
> which means that any total_elems input less than 512K is disregarded
> altogether.  So I'm not sold on your "ROLES_LIST_BLOOM_THRESHOLD * 10"
> value.  Maybe it doesn't matter though.

Yeah, I wasn't sure how much to worry about this.  I figured that we might
as well set it to a reasonable estimate based on the description of the
parameter.  This description claims that the filter should work well if
this is off by a factor of 5 or more, and 50x the threshold sounded like it
ought to be good enough for anyone, so that's how I landed on 10x.  But as
you point out, this value will be disregarded altogether, and it will
continue to be ignored unless the filter implementation changes, which
seems unlikely.  If you have a different value in mind that you would
rather use, I'm fine with changing it.

> I do not like, even a little bit, your use of a static variable to
> hold the bloom filter pointer.  That code will misbehave horribly
> if we throw an error partway through the role-accumulation loop;
> the next call will try to carry on using the old filter, which would
> be wrong even if it still existed which it likely won't.  It's not
> that much worse notationally to keep it as a local variable, as I
> did in the attached.

Ah, yes, that's no good.  I fixed this in the new version.

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

--3V7upXqbjpZ4EhLz
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="v4-0001-Optimize-roles_is_member_of-with-a-Bloom-filter.patch"