timeout querying a list of bugs for packages a team is subscribed to

Bug #904339 reported by Steve Langasek on 2011-12-14
16
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Launchpad itself
Critical
Curtis Hovey

Bug Description

The following URL times out in LP:

  https://bugs.launchpad.net/ubuntu/+bugs?field.structural_subscriber=foundations-bugs

(Error ID: OOPS-bf0e28e04acf069b1274d63622a56b1f)

It's important to Ubuntu Foundations to be able to query the bugs, ordered by bug heat, on the packages we're subscribed to, to identify bugs that should be escalated. This query worked previously and regressed within the past month (although I believe the field was previously called something other than 'structural_subscriber').

Related branches

Gary Poster (gary) on 2011-12-14
Changed in launchpad:
status: New → Triaged
importance: Undecided → Critical
tags: added: timeout
Gary Poster (gary) on 2011-12-14
tags: added: regression
Curtis Hovey (sinzui) wrote :

I switched the call from bug_supervisor to structural_subscriber because that is what the field actually searched...packages do not have bug supervisors. The structural_subscriber field is correct what what we want to do, but I think the implementation does more work than anyone really asks it. I think the teams; structural subscriptions for products and milestones are also being examined, we know the context is distribution. We may want the logic to only select the subscriptions for the bugtarget and subordinate objects. The bug_supervisor field was also doing the same stupid approach of checking if you are the bug supervisor for a product when the bugtarget is ubuntu.

There are three other bugs reported about the time of bug and I recently saw issues with bug heat qastaging. The issue may be about bugheat.

Curtis Hovey (sinzui) wrote :
Download full text (3.7 KiB)

This is the odd chunk of structural subscriber SQL where we know the bugtarget is Ubuntu so the only relevant structures are distro which also has a package clause:

WITH ss AS
  (SELECT *
   FROM StructuralSubscription
   WHERE StructuralSubscription.subscriber = 3562998)
SELECT BugTask.status,
       BugTask.assignee,
       BugTask.bug,
       BugTask.bugwatch,
       BugTask.date_assigned,
       BugTask.date_closed,
       BugTask.date_confirmed,
       BugTask.date_fix_committed,
       BugTask.date_fix_released,
       BugTask.date_incomplete,
       BugTask.date_inprogress,
       BugTask.date_left_closed,
       BugTask.date_left_new,
       BugTask.date_triaged,
       BugTask.datecreated,
       BugTask.distribution,
       BugTask.distroseries,
       BugTask.heat,
       BugTask.id,
       BugTask.importance,
       BugTask.milestone,
       BugTask.OWNER, BugTask.product,
                      BugTask.productseries,
                      BugTask.sourcepackagename,
                      BugTask.targetnamecache,
                      Bug.access_policy,
                      Bug.date_last_message,
                      Bug.date_last_updated,
                      Bug.date_made_private,
                      Bug.datecreated,
                      Bug.description,
                      Bug.duplicateof,
                      Bug.heat,
                      Bug.heat_last_updated,
                      Bug.id,
                      Bug.latest_patch_uploaded,
                      Bug.message_count,
                      Bug.name,
                      Bug.number_of_duplicates,
                      Bug.OWNER, Bug.private,
                                 Bug.security_related,
                                 Bug.title,
                                 Bug.users_affected_count,
                                 Bug.users_unaffected_count,
                                 Bug.who_made_private
FROM BugTask
JOIN Bug ON BugTask.bug = Bug.id
WHERE BugTask.id IN
    (SELECT BugTask.id
     FROM BugTask
     LEFT JOIN Product ON BugTask.product = Product.id
     AND Product.active
     LEFT JOIN ss ss1 ON BugTask.product = (ss1.product)
     LEFT JOIN ss ss2 ON BugTask.productseries = (ss2.productseries)
     LEFT JOIN ss ss3 ON Product.project = (ss3.project)
     LEFT JOIN ss ss4 ON BugTask.distribution = (ss4.distribution)
     AND (BugTask.sourcepackagename = (ss4.sourcepackagename)
          OR (ss4.sourcepackagename IS NULL))
     LEFT JOIN ss ss5 ON BugTask.distroseries = (ss5.distroseries)
     OR (ss5.distribution) = 0
     AND BugTask.sourcepackagename = (ss5.sourcepackagename)
     LEFT JOIN ss ss6 ON BugTask.milestone = (ss6.milestone), Bug
     WHERE Bug.id = BugTask.bug
       AND BugTask.distribution = 1
       AND (BugTask.status IN (10))
       AND Bug.duplicateof IS NULL
       AND NULL_COUNT(ARRAY[ss1.id, ss2.id, ss3.id, ss4.id, ss5.id, ss6.id]) < 6
       AND (Bug.private = FALSE
            OR EXISTS ( WITH teams AS
                         ( SELECT team
                          FROM TeamParticipation
                          WHERE person = 972620 )
                       SELECT BugSubscription.bug
                       F...

Read more...

tags: added: bugs search subscribers
Curtis Hovey (sinzui) wrote :

This is the long query from the most recent oops at 8920.0ms:

WITH ss AS (
SELECT *
FROM StructuralSubscription
WHERE StructuralSubscription.subscriber = $INT)
  SELECT BugTaskFlat.bugtask
  FROM BugTaskFlat WHERE BugTaskFlat.bugtask IN (
  SELECT BugTaskFlat.bugtask
  FROM BugTaskFlat
  LEFT JOIN Product ON BugTaskFlat.product = Product.id
  AND Product.active
  LEFT JOIN ss AS "_23f0" ON BugTaskFlat.product = "_23f0".product
  LEFT JOIN ss AS "_23f1" ON BugTaskFlat.productseries = "_23f1".productseries
  LEFT JOIN ss AS "_23f2" ON Product.project = "_23f2".project
  LEFT JOIN ss AS "_23f3" ON BugTaskFlat.distribution = "_23f3".distribution
  AND ("_23f3".sourcepackagename = BugTaskFlat.sourcepackagename
       OR "_23f3".sourcepackagename IS NULL)
  LEFT JOIN ss AS "_23f4" ON BugTaskFlat.distroseries = "_23f4".distroseries
  OR "_23f4".distribution = $INT
  AND BugTaskFlat.sourcepackagename = "_23f4".sourcepackagename
  LEFT JOIN ss AS "_23f5" ON BugTaskFlat.milestone = "_23f5".milestone WHERE BugTaskFlat.distribution = $INT
  AND BugTaskFlat.status IN ($INT ... $INT)
  AND BugTaskFlat.duplicateof IS NULL
  AND NULL_COUNT(ARRAY["_23f0".id, "_23f1".id, "_23f2".id, "_23f3".id, "_23f4".id, "_23f5".id]) < $INT
  AND (BugTaskFlat.information_type IN ($INT, $INT)
  OR COALESCE((BugTaskFlat.access_grants)&&
                (SELECT ARRAY_AGG(TeamParticipation.team)
                 FROM TeamParticipation
                 WHERE TeamParticipation.person = $INT), FALSE)
              OR COALESCE((BugTaskFlat.access_policies)&&
                            (SELECT ARRAY_AGG(AccessPolicyGrant.policy)
                             FROM AccessPolicyGrant
                             JOIN TeamParticipation ON TeamParticipation.team = AccessPolicyGrant.grantee
                             WHERE TeamParticipation.person = $INT), FALSE)))
ORDER BY BugTaskFlat.heat DESC, BugTaskFlat.bugtask LIMIT $INT
OFFSET $INT

description: updated
Curtis Hovey (sinzui) on 2012-09-25
summary: - timeout querying a list of bugs ordered by heat on packages a team is
- subscribed to
+ timeout querying a list of bugs for packages a team is subscribed to
Curtis Hovey (sinzui) on 2012-09-25
description: updated
Curtis Hovey (sinzui) on 2012-09-25
Changed in launchpad:
assignee: nobody → Curtis Hovey (sinzui)
status: Triaged → In Progress
Curtis Hovey (sinzui) wrote :

This variant is much faster. it replaces joins with subselects in the where clause. Only the relevant rules are added to the clause; the product, productseries, and project subselects are ignored because they are not in Ubuntu. I am not sure this example is fast enough and I think the containership rules might be easier to query if the tables new that milestones were is series which were in Ubuntu.

WITH ss AS
  (SELECT *
   FROM StructuralSubscription
   WHERE StructuralSubscription.subscriber = 3562998)
SELECT BugTaskFlat.bugtask
FROM BugTaskFlat
WHERE BugTaskFlat.bugtask IN
    (SELECT BugTaskFlat.bugtask
     FROM BugTaskFlat
     WHERE BugTaskFlat.distribution = 1
       AND BugTaskFlat.status IN (25,
                                  10,
                                  20,
                                  21,
                                  22,
                                  13,
                                  14)
       AND BugTaskFlat.duplicateof IS NULL
       AND (
        BugTaskFlat.sourcepackagename in (
            SELECT sourcepackagename from ss
            WHERE distribution = 1)
        OR
        BugTaskFlat.distroseries in (
            SELECT distroseries from ss
            JOIN DistroSeries on ss.distroseries = DistroSeries.id
            WHERE
                DistroSeries.distribution = 1
                AND DistroSeries.releasestatus in (1, 2, 3, 3, 4, 5)
        )
        OR
        BugTaskFlat.milestone in (
            SELECT milestone from ss
            JOIN Milestone on Milestone.id = ss.milestone
            JOIN DistroSeries on ss.distroseries = DistroSeries.id
            WHERE
                DistroSeries.distribution = 1
                AND Milestone.active IS TRUE)
        )
       AND (BugTaskFlat.information_type IN (1,
                                             2)
            OR COALESCE((BugTaskFlat.access_grants)&&
                          (SELECT ARRAY_AGG(TeamParticipation.team)
                           FROM TeamParticipation
                           WHERE TeamParticipation.person = 350514), FALSE)
            OR COALESCE((BugTaskFlat.access_policies)&&
                          (SELECT ARRAY_AGG(AccessPolicyGrant.policy)
                           FROM AccessPolicyGrant
                           JOIN TeamParticipation ON TeamParticipation.team = AccessPolicyGrant.grantee
                           WHERE TeamParticipation.person = 350514), FALSE)))
ORDER BY BugTaskFlat.importance DESC, BugTaskFlat.bugtask LIMIT 76

Launchpad QA Bot (lpqabot) wrote :
tags: added: qa-needstesting
Changed in launchpad:
status: In Progress → Fix Committed
Curtis Hovey (sinzui) on 2012-09-29
tags: added: qa-ok
removed: qa-needstesting
Curtis Hovey (sinzui) wrote :

This is okay to release, but I am not convinced the query is fast enough. This is the shape the the new query which is indeed fast when I am just querying the db:

WITH ss AS
  (SELECT *
   FROM StructuralSubscription
   WHERE StructuralSubscription.subscriber = 3562998)
SELECT BugTaskFlat.bugtask
FROM BugTaskFlat
WHERE BugTaskFlat.distribution = 1
  AND BugTaskFlat.status IN (25,
                             10,
                             20,
                             21,
                             22,
                             13,
                             14)
  AND BugTaskFlat.duplicateof IS NULL
  AND (BugTaskFlat.milestone IN
         (SELECT "_17".milestone
          FROM ss AS "_17")
       OR BugTaskFlat.distribution IN
         (SELECT "_17".distribution
          FROM ss AS "_17"
          WHERE "_17".sourcepackagename IS NULL)
       OR ROW(BugTaskFlat.distribution, BugTaskFlat.sourcepackagename) IN
         (SELECT "_17".distribution,
                 "_17".sourcepackagename
          FROM ss AS "_17")
       OR BugTaskFlat.distroseries IN
         (SELECT "_17".distroseries
          FROM ss AS "_17"
          WHERE "_17".sourcepackagename IS NULL)
       OR ROW(BugTaskFlat.distroseries, BugTaskFlat.sourcepackagename) IN
         (SELECT 0,
                 "_17".sourcepackagename
          FROM ss AS "_17"
          WHERE "_17".distribution = 0
            AND "_17".sourcepackagename IS NOT NULL))
  AND (BugTaskFlat.information_type IN (1,
                                        2)
       OR COALESCE((BugTaskFlat.access_grants)&&
                     (SELECT ARRAY_AGG(TeamParticipation.team)
                      FROM TeamParticipation
                      WHERE TeamParticipation.person = 350514), FALSE)
       OR COALESCE((BugTaskFlat.access_policies)&&
                     (SELECT ARRAY_AGG(AccessPolicyGrant.policy)
                      FROM AccessPolicyGrant
                      JOIN TeamParticipation ON TeamParticipation.team = AccessPolicyGrant.grantee
                      WHERE TeamParticipation.person = 350514), FALSE))
ORDER BY BugTaskFlat.importance DESC, BugTaskFlat.bugtask LIMIT 51
OFFSET 0

Curtis Hovey (sinzui) wrote :

Oh, there are two queries being called. The slow version does not have a limit, offset or sort:

WITH ss AS
  (SELECT *
   FROM StructuralSubscription
   WHERE StructuralSubscription.subscriber = 3562998)
SELECT COUNT(*)
FROM BugTaskFlat
WHERE BugTaskFlat.distribution = 1
  AND BugTaskFlat.status IN (25,
                             10,
                             20,
                             21,
                             22,
                             13,
                             14)
  AND BugTaskFlat.duplicateof IS NULL
  AND (BugTaskFlat.milestone IN
         (SELECT "_17".milestone
          FROM ss AS "_17")
       OR BugTaskFlat.distribution IN
         (SELECT "_17".distribution
          FROM ss AS "_17"
          WHERE "_17".sourcepackagename IS NULL)
       OR ROW(BugTaskFlat.distribution, BugTaskFlat.sourcepackagename) IN
         (SELECT "_17".distribution,
                 "_17".sourcepackagename
          FROM ss AS "_17")
       OR BugTaskFlat.distroseries IN
         (SELECT "_17".distroseries
          FROM ss AS "_17"
          WHERE "_17".sourcepackagename IS NULL)
       OR ROW(BugTaskFlat.distroseries, BugTaskFlat.sourcepackagename) IN
         (SELECT 0,
                 "_17".sourcepackagename
          FROM ss AS "_17"
          WHERE "_17".distribution = 0
            AND "_17".sourcepackagename IS NOT NULL))
  AND (BugTaskFlat.information_type IN (1,
                                        2)
       OR COALESCE((BugTaskFlat.access_grants)&&
                     (SELECT ARRAY_AGG(TeamParticipation.team)
                      FROM TeamParticipation
                      WHERE TeamParticipation.person = 350514), FALSE)
       OR COALESCE((BugTaskFlat.access_policies)&&
                     (SELECT ARRAY_AGG(AccessPolicyGrant.policy)
                      FROM AccessPolicyGrant
                      JOIN TeamParticipation ON TeamParticipation.team = AccessPolicyGrant.grantee
                      WHERE TeamParticipation.person = 350514), FALSE))

William Grant (wgrant) wrote :

With a hot cache the filtered query is only tens of milliseconds slower than the equivalent bug listing without the subscription filter. There's not much improvement to be made here without eliminating the COUNT(*) entirely (eg. moving to StormRangeFactory).

Curtis Hovey (sinzui) on 2012-10-01
Changed in launchpad:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers