Re: High CPU Usage of "SET ROLE"

=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= <ulf(dot)lohbruegge(at)gmail(dot)com> writes:
> I'm running PostgreSQL 9.6.10 on Debian and reported some performance
> issues with "SET ROLE" a while ago:
> https://www.postgresql.org/message-id/CABZYQR%2BKu%2BiLFhqwY89QrrnKG9wKxckmssDG2rYKESojiohRgQ%40mail.gmail.com
> ...
> The setup is the same as reported in the above mentioned post: I use more
> than a thousand roles per PostgreSQL instance and set the role for every
> connection before executing actual statements. My pg_class consists
> of 1,557,824 rows as every role has its own schema with more than 300
> tables.

It seems plausible to guess that you've hit some behavior that's O(N^2)
in the number of objects (for some object type or other). Perhaps "perf"
or a similar tool would give some insight into where the bottleneck is.

https://wiki.postgresql.org/wiki/Profiling_with_perf

regards, tom lane