Merge lp:~adeuring/launchpad/bug-739075-2 into lp:launchpad/db-devel

[Abel Deuring]

[r=allenap][bug=739075] do not join Message in QuestionMessage related queries in order to find the owner of the message; use the new column QuestionMessage.owner instead.

This branch should fix bug 739075: Person:+questions timeouts. An
EXPLAIN ANALYZE for the long queries from the OOPS reports mentioned
in this bug show that much time is spent joining QuestionMessage and
the huge table Message.

The prerequisite branch lp:~adeuring/launchpad/bug-739075 adds the
column owner to QuestionMessage; similar to BugMessage. The content
of this column is a copy of questionmessage.message.owner.

The branch removes joins of Message in queries in
lp.answers.model.question and lp.answers.model.questionsperson, and
it replaces terms like "Message.owner=some_person" with
"QuestionMessage.owner=some_person".

Tests:

./bin/test answers -vv

(sorry, I did not bother to check which tests are actually necessary
-- answers related tests needed less than 5 minutes on my machine.)

no lint

[Gavin Panella (allenap)]

review: Approve

=== modified file 'lib/lp/answers/model/question.py'
--- lib/lp/answers/model/question.py	2011-05-09 18:57:18 +0000
+++ lib/lp/answers/model/question.py	2011-05-12 12:01:27 +0000
@@ -904,10 +904,8 @@
         particular person."""
         joins = [
             ("""LEFT OUTER JOIN QuestionMessage
-                ON QuestionMessage.question = Question.id"""),
-            ("""LEFT OUTER JOIN Message
-                ON QuestionMessage.message = Message.id
-                AND Message.owner = %s""" % sqlvalues(person))]
+                ON QuestionMessage.question = Question.id
+                AND QuestionMessage.owner = %s""" % sqlvalues(person))]
         if self.project:
             joins.extend(self.getProductJoins())
 
@@ -938,7 +936,7 @@
                     AND Question.status IN %(owner_status)s)
                 OR (Question.owner != %(person)s AND
                     Question.status = %(open_status)s AND
-                    Message.owner = %(person)s)
+                    QuestionMessage.owner = %(person)s)
                 )''' % sqlvalues(
                     person=self.needs_attention_from,
                     owner_status=[
@@ -1149,7 +1147,7 @@
         QuestionParticipation.ANSWERER: "Question.answerer = %s",
         QuestionParticipation.SUBSCRIBER: "QuestionSubscription.person = %s",
         QuestionParticipation.OWNER: "Question.owner = %s",
-        QuestionParticipation.COMMENTER: "Message.owner = %s",
+        QuestionParticipation.COMMENTER: "QuestionMessage.owner = %s",
         QuestionParticipation.ASSIGNEE: "Question.assignee = %s"}
 
     def getConstraints(self):

=== modified file 'lib/lp/answers/model/questionsperson.py'
--- lib/lp/answers/model/questionsperson.py	2011-05-12 12:01:25 +0000
+++ lib/lp/answers/model/questionsperson.py	2011-05-12 12:01:27 +0000
@@ -50,8 +50,8 @@
             UNION SELECT question FROM QuestionSubscription
                   WHERE person = %(personID)s
             UNION SELECT question
-                  FROM QuestionMessage JOIN Message ON (message = Message.id)
-                  WHERE Message.owner = %(personID)s
+                  FROM QuestionMessage
+                  WHERE owner = %(personID)s
             )""" % sqlvalues(personID=self.person.id),
             clauseTables=['Question'], distinct=True))