Bulk project access checks are slow

[William Grant]

ProductSet.getProductPrivacyFilter does a four table join to identify accessible private projects, causing the subquery to regularly take 300ms for participants of teams like ~registry or ~pmteam. This can cause project searches or the top contributions list on Person:+index to time out like OOPS-45decddcd5032cacc973f3616ac66506.

If we follow the denormalisation pattern from Branch and Bug by creating Product.access_policies, and index TeamParticipation(person, team) and AccessPolicyGrantFlat(grantee, policy) it can be made a little faster:

-- Old: 280000 pages, 362ms.
EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT DISTINCT Product.id
FROM AccessPolicy,
    AccessPolicyGrantFlat,
    Product,
    TeamParticipation
WHERE
    AccessPolicyGrantFlat.grantee = TeamParticipation.team
    AND TeamParticipation.person = 21997
    AND AccessPolicyGrantFlat.policy = AccessPolicy.id
    AND AccessPolicy.product = Product.id
    AND AccessPolicy.TYPE = Product.information_type;

-- New: 874 pages, 34ms.
EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT DISTINCT AccessPolicyGrantFlat.policy
FROM AccessPolicyGrantFlat, TeamParticipation
WHERE
    AccessPolicyGrantFlat.grantee = TeamParticipation.team
    AND TeamParticipation.person = 1425512;

[Launchpad QA Bot (lpqabot)]

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