Re: High CPU Usage of "SET ROLE" - Mailing list pgsql-performance

From Tom Lane
Subject Re: High CPU Usage of "SET ROLE"
Date
Msg-id 82967.1540216668@sss.pgh.pa.us
Whole thread Raw
In response to High CPU Usage of "SET ROLE"  (Ulf Lohbrügge <ulf.lohbruegge@gmail.com>)
Responses Re: High CPU Usage of "SET ROLE"  (Ulf Lohbrügge <ulf.lohbruegge@gmail.com>)
List pgsql-performance
=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= <ulf.lohbruegge@gmail.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


pgsql-performance by date:

Previous
From: Ulf Lohbrügge
Date:
Subject: High CPU Usage of "SET ROLE"
Next
From: Dave E Martin
Date:
Subject: DELETE / UPDATE from partition not optimized (11.0)