launchpad-reviewers team mailing list archive

[Abel Deuring <abel.deuring@xxxxxxxxxxxxx>]

Abel Deuring has proposed merging lp:~adeuring/launchpad/bug-739075-2 into lp:launchpad/db-devel with lp:~adeuring/launchpad/bug-739075 as a prerequisite.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~adeuring/launchpad/bug-739075-2/+merge/60768

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

-- 
https://code.launchpad.net/~adeuring/launchpad/bug-739075-2/+merge/60768
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~adeuring/launchpad/bug-739075-2 into lp:launchpad/db-devel.

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