Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-54582

Unacceptably bad performance in load_course_context in accesslib.php

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Hopefully a complete unit test and Behat run will be quite a good test of this.

      It is hard to test any other way, since it should have absolutely no effect.

      I think the best way to ensure correctness it to carefully inspect the old and new query, and convince yourself that they are logically equivalent.

      Show
      Hopefully a complete unit test and Behat run will be quite a good test of this. It is hard to test any other way, since it should have absolutely no effect. I think the best way to ensure correctness it to carefully inspect the old and new query, and convince yourself that they are logically equivalent.
    • Affected Branches:
      MOODLE_29_STABLE, MOODLE_30_STABLE
    • Fixed Branches:
      MOODLE_30_STABLE, MOODLE_31_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      load_course_context currently does DB queries like

      SELECT ctx.path, rc.roleid, rc.capability, rc.permission
      FROM mdl_role_capabilities rc
      JOIN mdl_context ctx ON (ctx.id = rc.contextid)
      JOIN mdl_context cctx ON (cctx.id = 1490797 AND (ctx.id IN (1490797,1479196,160502,160501,1) OR ctx.path LIKE '' || cctx.path || '/%' ))
      WHERE rc.roleid IN (14,5,7)
      ORDER BY rc.capability
      

      which the Postgres query handler, at least, handles badly (0.7 seconds on our site). It does a table scan of mdl_context ctx (1.5 million rows).

      In the case where the query is WHERE rc.roleid =7, it does much better (15ms) with a completely different query plan.

      However, the code where this query is executed already has the full course context object (cctx), so we know the path and therefore you can construc the equivalen, but simpler query

      SELECT ctx.path, rc.roleid, rc.capability, rc.permission
      FROM mdl_context ctx
      JOIN mdl_role_capabilities rc ON rc.contextid = ctx.id
      WHERE rc.roleid IN (14,5,7)
      AND (ctx.id IN (1490797,1479196,160502,160501,1)
              OR ctx.path LIKE '/1/160501/160502/1479196/1490797/%')
      ORDER BY rc.capability
      

      That then executes in 15ms, returning the same data as before.

      Patch coming up.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                3 Vote for this issue
                Watchers:
                10 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  11/Jul/16