Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 2.1.5, 2.2.2
-
Component/s: Calendar, Performance
-
Testing Instructions:
-
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 -
Pull Master Diff URL:
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.
Attachments
Issue Links
- is a regression caused by
-
MDL-31086 invalid sql in calendar/lib.php
-
- Closed
-