Re: Possible performance regression with pg_dump of a large number of relations

On Fri, Jan 12, 2018 at 8:01 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> That commit covered a few different things, and I don't what improvement
> it mentions is the one that motivated this, but the key change was to add
> this query:
>
> EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON(c.oid
> = pip.objoid AND pip.classoid = (SELECT oid FROM pg_class WHERE relname =
> 'pg_class') AND pip.objsubid = at.attnum)WHERE at.attrelid = c.oid AND
> at.attnum>0 and ((SELECT count(acl) FROM (SELECT unnest(coalesce(at.attacl,acldefault('c',c.relowner)))
> AS acl EXCEPT SELECT unnest(coalesce(pip.initprivs,acldefault('c',c.relowner))))
> as foo) >1 OR (SELECT count(acl) FROM (SELECT unnest(coalesce(pip.initprivs,acldefault('c',c.relowner)))
> AS acl EXCEPT SELECT unnest(coalesce(at.attacl,acldefault('c',c.relowner))))
> as foo) >0))AS changed_acl
>

Sorry, that query reflects some munging I did to it. The real part added
to the query is:

EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON(c.oid
= pip.objoid AND pip.classoid = (SELECT oid FROM pg_class WHERE relname =
'pg_class') AND pip.objsubid = at.attnum)WHERE at.attrelid = c.oid AND
((SELECT array_agg(acl) FROM (SELECT
unnest(coalesce(at.attacl,acldefault('c',c.relowner))) AS acl EXCEPT SELECT
unnest(coalesce(pip.initprivs,acldefault('c',c.relowner)))) as foo) IS NOT
NULL OR (SELECT array_agg(acl) FROM (SELECT
unnest(coalesce(pip.initprivs,acldefault('c',c.relowner))) AS acl EXCEPT
SELECT unnest(coalesce(at.attacl,acldefault('c',c.relowner)))) as foo) IS
NOT NULL OR NULL IS NOT NULL OR NULL IS NOT NULL))AS changed_ac

Cheers,

Jeff