Re: [PATCH] pg_permissions

On Fri, Mar 26, 2021, at 07:53, Joel Jacobson wrote:
> On Thu, Mar 25, 2021, at 17:51, Tom Lane wrote:
>> "Joel Jacobson" <joel(at)compiler(dot)org <mailto:joel%40compiler.org>> writes:
>> > On Thu, Mar 25, 2021, at 16:16, Alvaro Herrera wrote:
>> >> Ah, of course -- the only way to obtain the acl columns is by going
>> >> through the catalogs individually, so it won't be possible. I think
>> >> this could be fixed with some very simple, quick function pg_get_acl()
>> >> that takes a catalog OID and object OID and returns the ACL; then
>> >> use aclexplode() to obtain all those details.
>>
>> > +1 for adding pg_get_acl().
>>
>> I wonder what performance will be like with lots o' objects.
>
> I guess pg_get_acl() would need to be implemented using a switch(classid) with 36 cases (one for each class)?
>
> Is your performance concern on how such switch statement will be optimized by the C-compiler?
> ...
> the classid case values are nicely ordered from OCLASS_CLASS..OCLASS_TRANSFORM (0..37), so they should produce O(2) fast jump tables.
>
> Maybe there is some other performance concern to reason about that I'm missing here?

Hmm, I think I understand your performance concern now:

Am I right guessing the problem even with a jump table is going to be branch prediction,
which will be poor due to many classids being common?

Interesting, the long UNION ALL variant does not seem to suffer from this problem,
thanks to explicitly specifying where to find the aclitem/owner-column.
We pay the lookup-cost "compile time" when writing the pg_ownerships/pg_permissions system views,
instead of having to lookup the classids at run-time to go fetch aclitem/owner-info.

The query planner is also smart enough to understand not all the individuals queries
needs to be executed, for the use-case when filtering on a specific classid.

/Joel