Comment 1 for bug 1692120

Colin Watson (cjwatson) wrote :

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.