get_user_access_sitewide performs poorly when the context table is large. This affects Postgres for sure and may possibly affect other databases.

The cause is this query:

$sql = "SELECT sctx.path, ra.roleid,
ctx.path AS parentpath,
rco.capability, rco.permission
FROM {$CFG->prefix}role_assignments ra
JOIN {$CFG->prefix}context ctx
ON ra.contextid=ctx.id
JOIN {$CFG->prefix}context sctx
ON (sctx.path LIKE " . sql_concat('ctx.path',"'/%'"). " )
JOIN {$CFG->prefix}role_capabilities rco
ON (rco.roleid=ra.roleid AND rco.contextid=sctx.id)
WHERE ra.userid = $userid
AND sctx.contextlevel <= ".CONTEXT_COURSE."
ORDER BY sctx.depth, sctx.path, ra.roleid";

The join on context path is slow because the database is unable to use an index for LIKE followed by computed value. (Provided that you have added a Postgres pattern index, or the database is set to no locale, then the same LIKE would be perfectly fine if you just did LIKE '/1/23/1241241/%' or whatever, it's the computed bit that throws it.)

This query is trying to find all subcontexts (sctx) of a main context where user has a role (ctx) that are of level course, category, or site. Courses cannot have subcontexts that meet this requirement. Most users roles on most sites are in courses. Therefore it is possible to dramatically improve performance by adding the following to the WHERE clause:

AND ctx.contextlevel < CONTEXT_COURSE

note < not <=: that means we only consider category, site level roles.

This improves performance by a large factor in the typical student case (a few roles on a few courses); on our test server with no load it goes from 600ms to 15ms. It makes no difference in the admin/site level role case.

Anything wrong with this assumption? Could somebody knowledgeable about roles confirm it? This fix should be applied to 1.9 if it is correct as it can save a second from each student login on a large system, which is a lot...