From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Cc: alex work <alexwork033@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Subject: Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs
Date: Thu, 21 Mar 2024 16:31:45 -0400
Message-ID: <642442.1711053105@sss.pgh.pa.us> (raw)
In-Reply-To: <619340.1711050134@sss.pgh.pa.us>
References: <CAGvXd3OSMbJQwOSc-Tq-Ro1CAz=vggErdSG7pv2s6vmmTOLJSg@mail.gmail.com>
<341186.1711037256@sss.pgh.pa.us>
<619340.1711050134@sss.pgh.pa.us>
I wrote:
> ... I still see the problematic GRANT taking ~250ms, compared
> to 5ms in v15. roles_is_member_of is clearly on the hook for that.
Ah: looks like that is mainly the fault of the list_append_unique_oid
calls in roles_is_member_of. That's also an O(N^2) cost of course,
though with a much smaller constant factor.
I don't think we have any really cheap way to de-duplicate the role
OIDs, especially seeing that it has to be done on-the-fly within the
collection loop, and the order of roles_list is at least potentially
interesting. Not sure how to make further progress without a lot of
work.
regards, tom lane
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: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org, alexwork033@gmail.com, robertmhaas@gmail.com
Subject: Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs
In-Reply-To: <642442.1711053105@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox