obsolete-junk/+series times out when the user is not anonymous

[Edwin Grubbs]

The old oops only showed that some projects have a ton of series, so
batching (Bug 663861) was able to solve the timeouts for anonymous users.

OOPS-1753K2382

The bugtask status query usually takes between 11ms and 40ms when an
anonymous user accesses the page, since the privacy calculation is
simpler.

    SELECT status, count(*)
    FROM (
        SELECT BugTask.status
        FROM BugTask
            JOIN Bug ON BugTask.bug = Bug.id
        WHERE BugTask.productseries = 10455
            AND Bug.private = FALSE

        UNION ALL

        SELECT BugTask.status
        FROM BugTask
            JOIN Bug ON BugTask.bug = Bug.id
            JOIN Milestone ON BugTask.milestone = Milestone.id
        WHERE BugTask.productseries IS NULL
            AND Milestone.productseries = 10455
            AND Bug.private = FALSE
        ) AS subquery
    GROUP BY status;

The new oops shows that even if we reduce the batch size from 50 to 10,
the page will still take on average 6 seconds to generate. I will look
into improving the speed of the query, but it may be necessary to either
remove the bugtask counts from this page or not require them to
accurately reflect privacy. To avoid confusion, it could tell the user
that it is only displaying the public bug count.

OOPS-1794A346

The bugtask status query takes between 276ms and 983ms when a normal
user (not Launchpad admin or anonymous) accesses the page, since the
privacy calculation is very complicated.

    SELECT status, count(*)
    FROM (
        SELECT BugTask.status
        FROM BugTask
            JOIN Bug ON BugTask.bug = Bug.id
        WHERE BugTask.productseries = 35570
            AND (Bug.private = FALSE
                 OR EXISTS (
                    SELECT BugSubscription.bug
                    FROM BugSubscription, TeamParticipation
                    WHERE TeamParticipation.person = 1363376
                        AND BugSubscription.person = TeamParticipation.team
                        AND BugSubscription.bug = Bug.id
                ))

        UNION ALL

        SELECT BugTask.status
        FROM BugTask
            JOIN Bug ON BugTask.bug = Bug.id
            JOIN Milestone ON BugTask.milestone = Milestone.id
        WHERE BugTask.productseries IS NULL
            AND Milestone.productseries = 35570
            AND (Bug.private = FALSE
                 OR EXISTS (
                    SELECT BugSubscription.bug
                    FROM BugSubscription, TeamParticipation
                    WHERE TeamParticipation.person = 1363376
                        AND BugSubscription.person = TeamParticipation.team
                        AND BugSubscription.bug = Bug.id
                ))
        ) AS subquery
    GROUP BY status;

[Curtis Hovey (sinzui)]

+series is just showing bug counts. We do not care about being precise, particularly in the case of obsolete series. Is it possible to only query for public bugs for +series?

[Robert Collins (lifeless)]

Also note that many bugs queries *are* optimisable. Feel free to ask
for dba assistance making the queries subsecond.

In principle you should be able to generate aggregate stats for 100 or
even a 1000 series in under a second given our data model.

[Edwin Grubbs (edwin-grubbs)]

Curtis informed me that it is ok for the counts of bugs and blueprints to include private bugs and blueprints since that eliminates a costly permission check on the TeamMembership table when the user is logged in.

[Launchpad QA Bot (lpqabot)]

Fixed in stable r12158 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/12158>.