Metabug: Inefficient sub-select SQL in LP causes performance issues
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Launchpad itself |
Invalid
|
Undecided
|
Unassigned |
Bug Description
I'm just noting this here so I don't forget it. Each issue will need to be expanded into a bug as appropriate, so the QA for code fixes can be tracked properly.
Further to the fix in Bug #501945, where an inefficient sub-query was causing a timeout, I decided to have a search for similar SQL queries elsewhere in the Launchpad code-base. A simple grep reveals a handful of easily identifiable candidates:
grep -n "IN[^TSG].*SELECT" * -R --include "*.py"
lp/code/
lp/registry/
lp/registry/
lp/registry/
lp/registry/
lp/services/
lp/soyuz/
lp/translations
lp/bugs/
lp/bugs/
lp/bugs/
The more efficient pattern for many of these would be to change
X.Y IN (SELECT Y FROM Z WHERE ...) to:
EXISTS (SELECT TRUE FROM Z WHERE Z.Y=X.Y AND ....)
I'm going to mark this invalid as its not really actionable - each bug should focus on a single thing. I'm keen to see your separate broken out bugs though!