When I use the psql cli on the same database I can see via "\timing" that the first statement after "RESET ROLE;" is significantly slower. I was even able to strip it down to two statements ("SET ROLE ...;" and "RESET ROLE;"):
...
Maybe my observations here are already sufficient to find out what happens here? I guess that my setup with 1k rows in pg_roles and 1.5m rows in pg_class is probably the cause.
It would probably be enough if it were reproducible, but I can't reproduce it.
-- set up
perl -le 'print "create user foo$_;" foreach 1..1000'|psql
perl -le 'foreach $r (reverse 1..1000) {print "set role foo$r;"; print "create table foo$r.foo$_ (x serial primary key);" foreach 1..1000;}'|psql > out
-- test
perl -le 'print "set role foo$_;\nreset role;" foreach 1..1000'|psql
Does it help when I create a test setup with a docker image that contains a database with that many entries in pg_roles and pg_class and share it here?
If you have a script to create the database, I'd be more likely to play around with that than with a docker image. (Which I have to guess would be quite large anyway, with 1.5 rows in pg_class)