launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

William Grant has proposed merging lp:~wgrant/launchpad/bug-1007140 into lp:launchpad.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #1007140 in Launchpad itself: "Person:EntryResource:getRequestedReviews timeout because of BranchMergeProposal"
  https://bugs.launchpad.net/launchpad/+bug/1007140
  Bug #1031764 in Launchpad itself: "timeout on code.launchpad.net/~ubuntu-branches"
  https://bugs.launchpad.net/launchpad/+bug/1031764

For more details, see:
https://code.launchpad.net/~wgrant/launchpad/bug-1007140/+merge/123688

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.
-- 
https://code.launchpad.net/~wgrant/launchpad/bug-1007140/+merge/123688
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/bug-1007140 into lp:launchpad.

=== 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)]