Michal Charemza <mic...@charemza.name> writes: > The issue is that we're hitting a strange performance problem on > connection. Sometimes it can take ~25 to 40 seconds just to connect, > although it's often way quicker. There seems to be no middle ground - never > have I seen a connection take between 0.5 and 25 seconds for example. We > suspect it's related to the number of roles the connecting user has > (including via other roles), because if we remove all roles but one from > the connecting user (the one that grants connection permissions), > connecting is always virtually instantaneous.
It's not very clear what you mean by "sometimes". Is the slowness reproducible for a particular user and role configuration, or does it seem to come and go by itself? As Tomas said, a self-contained reproduction script would be very helpful for looking into this. > The closest issue that I can find that's similar is > https://www.postgresql.org/message-id/flat/CAGvXd3OSMbJQwOSc-Tq-Ro1CAz%3DvggErdSG7pv2s6vmmTOLJSg%40mail.gmail.com, > which reports that GRANT role is slow with a high number of roles - but in > our case, it's connecting that's the problem, before (as far as we can > tell) even one query is run. That specific problem is (we think) new in v16, but the root cause is an inefficient lookup mechanism that has been there a long time. Maybe you have found a usage pattern that exposes its weakness in older branches. If so, we could consider back-patching 14e991db8 further than v16 ... but I don't plan to take any risk there without concrete evidence that it'd improve things. regards, tom lane
