Metabug: Inefficient sub-select SQL in LP causes performance issues

[Peter Clifton]

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/model/codeimportjob.py:135: """id IN (SELECT id FROM CodeImportJob
lp/registry/model/pillar.py:93: WHERE (id IN (SELECT alias_for FROM PillarName WHERE name=?)
lp/registry/model/pillar.py:126: WHERE (id IN (SELECT alias_for FROM PillarName WHERE name=?)
lp/registry/model/distroseries.py:1028: SourcePackageName.id NOT IN (SELECT DISTINCT
lp/registry/model/distribution.py:1454: %s IN (SELECT TeamParticipation.person
lp/services/openid/model/openidrpsummary.py:77: END NOT IN (SELECT trust_root FROM OpenIdRPConfig)
lp/soyuz/model/binarypackagebuild.py:900: OR %s IN (SELECT TeamParticipation.person
lp/translations/model/pofile.py:720: """POTMsgSet.id IN (SELECT DISTINCT TranslationMessage.potmsgset
lp/bugs/model/bugtask.py:1749: IN (SELECT Milestone.id
lp/bugs/model/bugtask.py:1801: "Bug.id IN (SELECT bug from BugAttachment WHERE %s)")
lp/bugs/model/bugtask.py:2271: clause = 'Bug.id IN (SELECT DISTINCT Bug.id from %s WHERE %s)' % (

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 ....)

[Robert Collins (lifeless)]

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!