Slow performance of referee search

Bug Report

Description

POST request to https://scipost.org/submissions/_hx_select_referee_table/<submission_id> take around 5 seconds. This degrades usability of selecting referees.

Page URL(s)

• https://scipost.org/submissions/select_referee/<submission_id>
• Referee search form

mentioned in commit 526cee2b

Althought the latest commit (526cee2b) has improved things a little bit, the problem remains. Unfortunately, this behavior is not witnessed in my local installation, making me wonder whether it's the server machine itself that is the cause of the bottleneck.

added impacthigh priorityhigh labels

changed iteration to Two-week Sprint Feb 9, 2025 - Feb 22, 2025

In trying to solve this issue I uncovered something peculiar. Usually the process of solving these performance issues comes down to identifying inefficient database operations and optimizing them. In fa92eff9 I have tackled the last two N+1 problems of the referee table, thereby creating a fully optimized single-shot query, ...only to discover that made the query 40% slower than the numerous smaller ones.

Whatever the case with this particular query or its failed fix, I am afraid that I still see very large discrepancies between my local installation and production, even when they have the exact same data. On my local machine, the requests take ~350ms to complete, whereas in production they take ~3.25s.

assigned to @gkatsikas

mentioned in commit 5debbea7

closed with commit 5debbea7

I tracked the issue down to, essentially, the distinct() call, and implemented alternatives for it. This + fa92eff9 now constitute the final patch 5debbea7.

Speed has improved from ~3.25s to ~350ms, giving a ~10x performance improvement.

added 1d 2h of time spent