Creating a review request can create a lot of notification, where the prelim search is very expensive. See sample trace here, screenshotted below for reference:
The search query does an unnecessary join on the site table. The join is not needed since the site id is already known.
TODO: Remove unnecessary join
Note that even on much smaller traces, not at peak time (~9pm), the pattern of having the selects take much longer than the insert/updates hold. Here is another sample trace for reference.
Problem
Creating a review request can create a lot of notification, where the prelim search is very expensive. See sample trace here, screenshotted below for reference:
The search query does an unnecessary join on the site table. The join is not needed since the site id is already known.
TODO: Remove unnecessary join
Note that even on much smaller traces, not at peak time (~9pm), the pattern of having the selects take much longer than the insert/updates hold. Here is another sample trace for reference.
Solution
Remove the unnecessary join!
The change is fully backward compatible.