From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= <ulf.lohbruegge@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: High CPU Usage of "SET ROLE"
In-reply-to: 
 <CABZYQRJDKytuyyL-th+Kq2_D4fS-rrb3sgDG2Y4qsNT__GGD8Q@mail.gmail.com>
References: 
 <CABZYQRJDKytuyyL-th+Kq2_D4fS-rrb3sgDG2Y4qsNT__GGD8Q@mail.gmail.com>
Comments: In-reply-to =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?=
 <ulf.lohbruegge@gmail.com>
	message dated "Mon, 22 Oct 2018 15:44:16 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <82966.1540216668.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 22 Oct 2018 09:57:48 -0400
Message-ID: <82967.1540216668@sss.pgh.pa.us>
Precedence: bulk

=3D?UTF-8?Q?Ulf_Lohbr=3DC3=3DBCgge?=3D <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%2BiLFhqwY89QrrnKG9wKx=
ckmssDG2rYKESojiohRgQ%40mail.gmail.com
> ...
> The setup is the same as reported in the above mentioned post: I use mor=
e
> 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