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

Extremely slow query in calendar for large sites

    Details

    • Testing Instructions:
      Hide

      Set the calendar_adminseesall config setting to true
      Visit a calendar page
      Ensure that all entries are correctly shown

      Run the old query and the new query to ensure that the same results are always returned. For convenience, I've pasted them below:

      Old code:

      SELECT DISTINCT
          c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
      FROM mdl_course c
      JOIN mdl_event e ON e.courseid = c.id
      LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
      ;
      

      New code:

      SELECT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
      FROM mdl_course c
      LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
      WHERE EXISTS (SELECT 1 FROM mdl_event e WHERE e.courseid = c.id)
      ;
      
      Show
      Set the calendar_adminseesall config setting to true Visit a calendar page Ensure that all entries are correctly shown Run the old query and the new query to ensure that the same results are always returned. For convenience, I've pasted them below: Old code: SELECT DISTINCT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance FROM mdl_course c JOIN mdl_event e ON e.courseid = c.id LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50) ; New code: SELECT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance FROM mdl_course c LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50) WHERE EXISTS (SELECT 1 FROM mdl_event e WHERE e.courseid = c.id) ;
    • Difficulty:
      Moderate
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-32340-master-2

      Description

      It seems MDL-31086 introduces a very slow query in calendar, for large sites.

      $sql = "SELECT DISTINCT c.* $select
      FROM {course} c
      JOIN {event} e ON e.courseid = c.id
      $join";
      

      In my case, after upgrading to 2.1.5 via CLI, I could not login to the site as an admin due to hitting 5 minute connection timeout. Checking postgres showed this query running for several minutes - it appears to try and sort by all columns in the query, then return the top 20 for the get_records_sql limit. (So, not a very good plan, but...)

      Since we know course id is unique enough, it is much nicer on the database to advise it only to make the results unique by c.id i.e:

      -        $sql = "SELECT DISTINCT c.* $select
      +        $sql = "SELECT DISTINCT ON (c.id) c.* $select
      

      This makes the query return in only 10ms for this site. I'm not sure if this is compatible enough syntax for core, however.

      Github incoming.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  0 Vote for this issue
                  Watchers:
                  5 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    9/Jul/12