Performance of get_fast_modinfo is poor when courses have many activities, especially on Postgres.
This is because the main query get_fast_modinfo does is to retrieve the list of contexts and it does this with an 'instanceid IN (...)' syntax. This syntax is slow when retrieving large number of results. We observed this on a genuine course with 900 activities. (Yes this is special purpose but still.)
Performance can be improved by using a standard join rather than trying to 'optimise' the database query by passing in the already-known instance IDs. I have done a patch to make this happen.
Here are some tests on dev system (note, Postgres/MySQL times are different servers so not comparable, but tests on the same db are on the same machine; all queries were run repeatedly 5 times in a row after at least one/two warmups):
Postgres (before, after)
2000 activities: ~1500ms, ~140ms
~30 activities: ~30ms, ~30ms
MySQL (before, after)
2000 activities: ~500ms, ~170ms
3 activities: ~20ms, ~20ms
So in other words there is no difference in performance for small courses and for very large courses this is an improvement of roughly 10x on the performance of get_fast_modinfo in Postgres, or roughly 3x in MySQL.