Launchpad itself

  1. Bug #661988
  2. Comment #5

Comment 5 for bug 661988

Revision history for this message
Robert Collins (lifeless) wrote :

Have done a few experiments. We can halve the time on the count by dropping the joins to product and sourcepackage name - they are not being filtered on, so are totally unnecessary. For eager loading we can do a more efficient separate query anyway.

The query around bugtask.status is bonkers - when both incompletes are present we should just permit status=15 rather than constraining by date_incomplete.

The fti stuff is an issue too - doing a union like below constraints pgsql to do a fti lookup on bug itself - and this shaves another 50% off (getting down to 1s query).

SELECT bugtask.id
FROM BugTask
LEFT JOIN Bug ON BugTask.bug = Bug.id WHERE Bug.id = BugTask.bug
  AND BugTask.distribution = 1
  AND (BugTask.status in (10, 20, 21, 22, 25, 15)) AND Bug.duplicateof IS NULL
  AND Bug.fti @@ ftq('kpassgen')
  AND (Bug.private = FALSE
       OR EXISTS
         (SELECT BugSubscription.bug
          FROM BugSubscription,
               TeamParticipation
          WHERE TeamParticipation.person = 2497367
            AND TeamParticipation.team = BugSubscription.person
            AND BugSubscription.bug = Bug.id
          UNION SELECT BugTask.bug
          FROM BugTask,
               TeamParticipation
          WHERE TeamParticipation.person = 2497367
            AND TeamParticipation.team = BugTask.assignee
            AND BugTask.bug = Bug.id))
union SELECT bugtask.id
FROM BugTask
LEFT JOIN Bug ON BugTask.bug = Bug.id WHERE Bug.id = BugTask.bug
  AND BugTask.distribution = 1
  AND (BugTask.status in (10, 20, 21, 22, 25, 15)) AND Bug.duplicateof IS NULL
  AND (BugTask.fti @@ ftq('kpassgen')
       OR BugTask.targetnamecache ILIKE '%' || 'kpassgen' || '%')
  AND (Bug.private = FALSE
       OR EXISTS
         (SELECT BugSubscription.bug
          FROM BugSubscription,
               TeamParticipation
          WHERE TeamParticipation.person = 2497367
            AND TeamParticipation.team = BugSubscription.person
            AND BugSubscription.bug = Bug.id
          UNION SELECT BugTask.bug
          FROM BugTask,
               TeamParticipation
          WHERE TeamParticipation.person = 2497367
            AND TeamParticipation.team = BugTask.assignee
            AND BugTask.bug = Bug.id));