From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Nathan Bossart <nathandbossart(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org, alex work <alexwork033(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com> |
Subject: | Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs |
Date: | 2024-03-22 02:07:19 |
Message-ID: | 813833.1711073239@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Nathan Bossart <nathandbossart(at)gmail(dot)com> writes:
> The Bloom filter appears to help a bit, although it regresses the
> create-roles.sql portion of the test. I'm assuming that's thanks to all
> the extra pallocs and pfrees, which are probably avoidable if we store the
> filter in a long-lived context and just clear it at the beginning of each
> call to roles_is_member_of().
The zero-fill to reinitialize the filter probably costs a good deal
all by itself, considering we're talking about at least a megabyte.
Maybe a better idea is to not enable the filter till we're dealing
with at least 1000 or so entries in roles_list, though obviously that
will complicate the logic.
+ if (bloom_lacks_element(bf, (unsigned char *) &otherid, sizeof(Oid)))
+ roles_list = lappend_oid(roles_list, otherid);
+ else
+ roles_list = list_append_unique_oid(roles_list, otherid);
+ bloom_add_element(bf, (unsigned char *) &otherid, sizeof(Oid));
Hmm, I was imagining something more like
if (bloom_lacks_element(bf, (unsigned char *) &otherid, sizeof(Oid)) ||
!list_member_oid(roles_list, otherid))
{
roles_list = lappend_oid(roles_list, otherid);
bloom_add_element(bf, (unsigned char *) &otherid, sizeof(Oid));
}
to avoid duplicate bloom_add_element calls. Probably wouldn't move
the needle much in this specific test case, but this formulation
would simplify letting the filter kick in later. Very roughly,
if ((bf && bloom_lacks_element(bf, (unsigned char *) &otherid, sizeof(Oid))) ||
!list_member_oid(roles_list, otherid))
{
if (bf == NULL && list_length(roles_list) > 1000)
{
... create bf and populate with existing list entries
}
roles_list = lappend_oid(roles_list, otherid);
if (bf)
bloom_add_element(bf, (unsigned char *) &otherid, sizeof(Oid));
}
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | veem v | 2024-03-22 04:47:44 | Re: Not able to purge partition |
Previous Message | alex work | 2024-03-22 02:02:57 | Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2024-03-22 02:44:08 | Re: remaining sql/json patches |
Previous Message | alex work | 2024-03-22 02:02:57 | Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs |