Merge lp:~wgrant/launchpad/bug-1007140 into lp:launchpad

[William Grant]

Tweak get_branch_privacy_filter to use the array intersection trick to match Branch.access_policy, convincing postgres to pull it up two levels and fix performance for users with lots of teams.

This branch tweaks get_branch_privacy_filter to outsmart the query planner. See https://bugs.launchpad.net/launchpad/+bug/1007140/comments/1 for details/explains, but basically PostgreSQL was severely misplanning some complex branch queries for people with lots of teams, and slightly misplanning for people with not many teams. By replacing the traditional IN with an array intersection operation, the planner somehow realises the inner subquery is uncorrelated and is able to pull it right out to the top. This makes a number of BMP queries 10-200x faster, and leaves the already good queries just as quick.

[Curtis Hovey (sinzui)]

Thank you.

review: Approve (code)

=== modified file 'lib/lp/code/model/branch.py'
--- lib/lp/code/model/branch.py	2012-09-05 21:51:06 +0000
+++ lib/lp/code/model/branch.py	2012-09-11 08:03:23 +0000
@@ -170,6 +170,7 @@
     sqlvalues,
     )
 from lp.services.database.stormexpr import (
+    Array,
     ArrayAgg,
     ArrayIntersects,
     )
@@ -1622,15 +1623,17 @@
                 where=(TeamParticipation.person == user)
             )), False)
 
-    policy_grant_query = branch_class.access_policy.is_in(
+    policy_grant_query = Coalesce(
+        ArrayIntersects(
+            Array(branch_class.access_policy),
             Select(
-                AccessPolicyGrant.policy_id,
+                ArrayAgg(AccessPolicyGrant.policy_id),
                 tables=(AccessPolicyGrant,
                         Join(TeamParticipation,
                             TeamParticipation.teamID ==
                             AccessPolicyGrant.grantee_id)),
                 where=(TeamParticipation.person == user)
-            ))
+            )), False)
 
     return [
         Or(public_branch_filter, artifact_grant_query, policy_grant_query)]