Apparently, the query for \block_manager::load_blocks() uses full table scans which can take some time, especially if the block_instances table grows too large.

The performance of the query for \block_manager::load_blocks() can be improved using the parentcontextid-showinsubcontexts-pagetypepattern-subpagepattern index. This has been demonstrated by mudrd8mz for the moodle.org site in MDLSITE-6601.

Though David's temporary fix serves as a working proof of concept for moodle.org, we need to find a way to address this issue in a cross-database-compatible way.