Re: BUG #4596: information_schema.table_privileges is way too slow

Kirill Simonov <xi(at)gamma(dot)dn(dot)ua> writes:
> Pavel Stehule wrote:
>> really it's should be slow, it's cross join pg_class, pg_authid, pg_authid

> Yes, I realize why it's slow. I'm introspecting the database schema,
> that's why I need the whole contents of "table_privileges". I suppose I
> could obtain the same data from "pg_class.relacl", but I hoped to do it
> in a portable way.

There's not much to be done about that in the short term. A bit of
profiling says that essentially all the runtime is going into repeated
evaluations of the clause

aclcontains(c.relacl,
makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))

which cannot be applied until we form the join of all four relations
mentioned. This means the runtime is roughly proportional to the square
of the number of userids (since grantee and u_grantor both have a row
per userid). Even though the test itself is reasonably cheap, you can't
avoid getting screwed by the O(N^2) behavior. It doesn't help any that
we have to run the whole thing over again for each possible privilege
type name...

ISTM that if we wanted to really fix this, what'd be appropriate is
to invent a new function on the order of

aclexplode(aclitem[]) returns table(grantee oid, grantor oid, privilege_type text)

and then implement this view as a join between

(select aclexplode(relacl) from pg_class)

and a couple of instances of pg_authid.

More generally, there are a *whole lot* of ridiculous inefficiencies
in our information_schema views; I'm surprised there haven't been
more complaints about them. Sometime someone ought to go through
the whole set and see what other refactorings might be appropriate
to make them work better.

regards, tom lane