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
    • Rank:
      39163

      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.

        Issue Links

          Activity

          Tony Levi created issue -
          Tony Levi made changes -
          Field Original Value New Value
          Priority Minor [ 4 ] Critical [ 2 ]
          Tony Levi made changes -
          Labels netspot partner
          Tony Levi made changes -
          Labels netspot partner netspot partner patch
          Difficulty Easy [ 10023 ]
          Michael de Raadt made changes -
          Fix Version/s STABLE backlog [ 10463 ]
          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.
          It seems MDL-31086 introduces a very slow query in calendar, for large sites.

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

          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:
          {code}
          - $sql = "SELECT DISTINCT c.* $select
          + $sql = "SELECT DISTINCT ON (c.id) c.* $select
          {code}

          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.
          Labels netspot partner patch netspot partner patch triaged
          Assignee moodle.com [ moodle.com ] Andrew Davis [ andyjdavis ]
          Michael de Raadt made changes -
          Link This issue is a regression caused by MDL-31086 [ MDL-31086 ]
          Dan Poltawski made changes -
          Assignee Andrew Davis [ andyjdavis ] Dan Poltawski [ poltawski ]
          Dan Poltawski made changes -
          Pull 2.1 Branch https://github.com/tlevi/moodle/tree/mdl32340_21 mdl32340_21
          Pull from Repository git://github.com/tlevi/moodle
          Andrew Nicols made changes -
          Assignee Dan Poltawski [ poltawski ] Andrew Nicols [ dobedobedoh ]
          Andrew Nicols made changes -
          Pull Master Diff URL https://git.luns.net.uk/moodle.git/commitdiff/aa753ac24f16458fb881525d30c2ed6c0e372ba4
          Pull Master Branch MDL-32340-master-1
          Testing Instructions 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:
          {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)
          ;
          {code}

          New code;
          {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 c.id IN (SELECT mdl_event.courseid FROM mdl_event)
          {code}
          Pull 2.1 Branch mdl32340_21
          Pull 2.1 Diff URL https://github.com/tlevi/moodle/compare/moodle%3AMOODLE_21_STABLE...mdl32340_21
          Pull from Repository git://github.com/tlevi/moodle git://git.luns.net.uk/moodle.git
          Difficulty Easy [ 10023 ] Moderate [ 10024 ]
          Component/s Performance [ 10221 ]
          Andrew Nicols made changes -
          Status Open [ 1 ] Waiting for peer review [ 10012 ]
          Andrew Nicols made changes -
          Testing Instructions 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:
          {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)
          ;
          {code}

          New code;
          {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 c.id IN (SELECT mdl_event.courseid FROM mdl_event)
          {code}
          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:
          {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)
          ;
          {code}

          New code:
          {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)
          ;
          {code}
          Dan Poltawski made changes -
          Original Estimate 0 minutes [ 0 ]
          Remaining Estimate 0 minutes [ 0 ]
          Status Waiting for peer review [ 10012 ] Peer review in progress [ 10013 ]
          Peer reviewer poltawski
          Dan Poltawski made changes -
          Status Peer review in progress [ 10013 ] Development in progress [ 3 ]
          Andrew Nicols made changes -
          Status Development in progress [ 3 ] Waiting for integration review [ 10010 ]
          Sam Hemelryk made changes -
          Status Waiting for integration review [ 10010 ] Integration review in progress [ 10004 ]
          Integrator samhemelryk
          Currently in integration Yes [ 10041 ]
          Sam Hemelryk made changes -
          Status Integration review in progress [ 10004 ] Waiting for testing [ 10005 ]
          Fix Version/s 2.1.7 [ 12161 ]
          Fix Version/s 2.2.4 [ 12162 ]
          Fix Version/s STABLE backlog [ 10463 ]
          Tim Barker made changes -
          Tester salvetore
          Michael de Raadt made changes -
          Status Waiting for testing [ 10005 ] Testing in progress [ 10011 ]
          Michael de Raadt made changes -
          Status Testing in progress [ 10011 ] Tested [ 10006 ]
          Eloy Lafuente (stronk7) made changes -
          Status Tested [ 10006 ] Closed [ 6 ]
          Resolution Fixed [ 1 ]
          Currently in integration Yes [ 10041 ]
          Integration date 19/May/12

            People

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

              Dates

              • Created:
                Updated:
                Resolved: