Comment 5 for bug 904339

This variant is much faster. it replaces joins with subselects in the where clause. Only the relevant rules are added to the clause; the product, productseries, and project subselects are ignored because they are not in Ubuntu. I am not sure this example is fast enough and I think the containership rules might be easier to query if the tables new that milestones were is series which were in Ubuntu.

WITH ss AS
  (SELECT *
   FROM StructuralSubscription
   WHERE StructuralSubscription.subscriber = 3562998)
SELECT BugTaskFlat.bugtask
FROM BugTaskFlat
WHERE BugTaskFlat.bugtask IN
    (SELECT BugTaskFlat.bugtask
     FROM BugTaskFlat
     WHERE BugTaskFlat.distribution = 1
       AND BugTaskFlat.status IN (25,
                                  10,
                                  20,
                                  21,
                                  22,
                                  13,
                                  14)
       AND BugTaskFlat.duplicateof IS NULL
       AND (
        BugTaskFlat.sourcepackagename in (
            SELECT sourcepackagename from ss
            WHERE distribution = 1)
        OR
        BugTaskFlat.distroseries in (
            SELECT distroseries from ss
            JOIN DistroSeries on ss.distroseries = DistroSeries.id
            WHERE
                DistroSeries.distribution = 1
                AND DistroSeries.releasestatus in (1, 2, 3, 3, 4, 5)
        )
        OR
        BugTaskFlat.milestone in (
            SELECT milestone from ss
            JOIN Milestone on Milestone.id = ss.milestone
            JOIN DistroSeries on ss.distroseries = DistroSeries.id
            WHERE
                DistroSeries.distribution = 1
                AND Milestone.active IS TRUE)
        )
       AND (BugTaskFlat.information_type IN (1,
                                             2)
            OR COALESCE((BugTaskFlat.access_grants)&&
                          (SELECT ARRAY_AGG(TeamParticipation.team)
                           FROM TeamParticipation
                           WHERE TeamParticipation.person = 350514), FALSE)
            OR COALESCE((BugTaskFlat.access_policies)&&
                          (SELECT ARRAY_AGG(AccessPolicyGrant.policy)
                           FROM AccessPolicyGrant
                           JOIN TeamParticipation ON TeamParticipation.team = AccessPolicyGrant.grantee
                           WHERE TeamParticipation.person = 350514), FALSE)))
ORDER BY BugTaskFlat.importance DESC, BugTaskFlat.bugtask LIMIT 76