Thread: Re: [PATCH] pg_permissions
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2021-Mar-26, Joel Jacobson wrote:
>> On Thu, Mar 25, 2021, at 17:51, Tom Lane wrote:
>> I wonder what performance will be like with lots o' objects.
> I guess he is concerned about the number of catalog accesses.
My concern is basically that you're forcing the join between
pg_shdepend and $everything_else to be done as a nested loop.
It will work well, up to where you have so many objects that
it doesn't ... but the planner will have no way to improve it.
Having said that, I don't really see a better way either.
Materializing $everything_else via a UNION ALL seems like
no fun from a maintenance perspective, plus we're not that
great on optimizing such constructs either.
regards, tom lane
On Fri, Mar 26, 2021, at 14:16, Tom Lane wrote:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:> On 2021-Mar-26, Joel Jacobson wrote:>> On Thu, Mar 25, 2021, at 17:51, Tom Lane wrote:>> I wonder what performance will be like with lots o' objects.> I guess he is concerned about the number of catalog accesses.My concern is basically that you're forcing the join betweenpg_shdepend and $everything_else to be done as a nested loop.It will work well, up to where you have so many objects thatit doesn't ... but the planner will have no way to improve it.
Thanks Alvaro and Tom for explaining.
Having said that, I don't really see a better way either.Materializing $everything_else via a UNION ALL seems likeno fun from a maintenance perspective, plus we're not thatgreat on optimizing such constructs either.
I see why pg_shdepend+pg_get_acl() is to prefer.
That said, I think maintenance of UNION ALL would actually not be too bad,
since the system views could initially be generated by a query using information_schema,
and the same query could update them when new catalogs are added.
/Joel