Upcoming work pages appear to time out a lot

[Mathieu Trudel-Lapierre]

My personal upcomingwork page seems to time out a whole lot (but I also have lots of stuff on it).

https://launchpad.net/~cyphermox/+upcomingwork

For instance: (Error ID: OOPS-3462b9b41acbf8d5afb00ba372201498)

[Colin Watson (cjwatson)]

The offending query is:

SELECT DISTINCT SpecificationWorkItem.assignee, SpecificationWorkItem.date_created, SpecificationWorkItem.deleted, SpecificationWorkItem.id, SpecificationWorkItem.milestone, SpecificationWorkItem.sequence, SpecificationWorkItem.specification, SpecificationWorkItem.status, SpecificationWorkItem.title
FROM Specification
LEFT JOIN Product ON Specification.product = Product.id
JOIN SpecificationWorkItem ON SpecificationWorkItem.specification = Specification.id
JOIN Milestone ON COALESCE(SpecificationWorkItem.milestone, Specification.milestone) = Milestone.id
WHERE (Specification.product IS NULL
       OR Product.active = TRUE)
  AND (Specification.information_type IN (1,
                                          2)
       OR COALESCE((Specification.access_grants)&&
                     (SELECT ARRAY_AGG(TeamParticipation.team)
                      FROM TeamParticipation
                      WHERE TeamParticipation.person = 1557381), FALSE)
       OR COALESCE(ARRAY[(Specification.access_policy)]&&
                     (SELECT ARRAY_AGG(AccessPolicyGrant.policy)
                      FROM AccessPolicyGrant
                      JOIN TeamParticipation ON TeamParticipation.team = AccessPolicyGrant.grantee
                      WHERE TeamParticipation.person = 1557381), FALSE))
  AND Milestone.dateexpected <= E'2017-11-15'
  AND Milestone.dateexpected >= E'2017-05-19'
  AND SpecificationWorkItem.deleted = FALSE
  AND (SpecificationWorkItem.assignee IN (1557381)
       OR Specification.assignee IN (1557381))
ORDER BY SpecificationWorkItem.id;

I can't get good EXPLAIN ANALYZE output from dogfood on this since the database dump is a bit too old (and when I tweak the dates dogfood is an order of magnitude faster, so I don't want to put much faith in the results), so I'll have to wait until a weekday when we can get a sysadmin to try it on production.

[Brian Murray (brian-murray)]

This also happens to me.

[William Grant (wgrant)]

The main problem is that the planner can't accurately estimate the number of workitems due to one of the assignee filters being on the wrong side of a join, causing it to end up retrieving all workitems and all specifications and filtering those down. This runs into a growing performance issue with ACL checks for Canonical employees that are in lots of teams.

https://pastebin.canonical.com/189802/ has three variants of the query which attempt to mitigate the issues. I suspect the last one will be <50ms.

[William Grant (wgrant)]

EXPLAIN (ANALYZE, BUFFERS) results: https://pastebin.canonical.com/190056/

As anticipated, the planner works much better when the CTE optimisation barrier is added, forcing it to use indexes to calculate the set of workitems.

[Launchpad QA Bot (lpqabot)]

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