To reproduce, find a database with a few thousand courses in. Turn on performance tracking. Visit the profile page for:

i) An arbitrary user who is a student on one or two courses
ii) The admin user

In case I saw around 16,000 queries. In case (ii) I saw around 48,000 queries. This results in unacceptably poor performance.

The problem is caused by the function get_my_courses() which checks every single course on the system to see whether or not the user has access. My recommended solution, corresponding to the two cases above, would be:

i) Provide optimisation that reduces the set of courses which get_my_courses must check. (This improves performance for most users by reducing the number of courses that need checking to a handful instead of 2,000.)

ii) Provide an optional parameter to get_my_courses, which can be used from the profile page, that limits the maximum number of courses returned e.g. to 20 so that it doesn't use thousands of queries showing every course on the system.

These changes reduce the number of queries for the profile page to 98 and 403 respectively on our system.

I am working on a patch (which may use a Postgres-specific query so need modification) that fixes the issue but am currently unable to test this as my core Moodle HEAD install doesn't work at all. Will attach patch at such time as I get it working.