Distribution:+bugtarget-portlet-bugfilters-stats timeouts

bug summary can definitely answer most of the stats we need rapidly, but the fixed-upstream stat may be harder to get without a table scan. An open question is whether a table scan + bugsummary would be cheaper at all than the current tablescan.

The current scan does a lookup per bugtask for related bugtasks. So one way to rephrase that would be to pull back more raw data and get a group-and-count of the.

The privacy clause is 1 second hot; the related bugtask check another 1 second hot.

The bug join is ~ 2.5 seconds (0.014 * 190K) - a table scan of just bugtask is 1 second hot.

If we denormalise duplicate and latest_patch_uploaded onto bugtask we may be able to do a much more efficient query (even though the table will be getting wider).

added dba tag - stub, could you double check my analysis?

temp table:
select bugtask.*, duplicateof, latest_patch_uploaded, private into temporary table bugtask2 from bug,bugtask where bug.id=bugtask.bug;

and a query on it: (bugtask2 as bugtask, assignee check dropped for the experiment: we can look at making a special sort of subscription or something).
SELECT BugTask.status,
       BugTask.importance,
       Bugtask.latest_patch_uploaded IS NOT NULL, (EXISTS
                                                 (SELECT TRUE
                                                  FROM BugTask AS RelatedBugTask
                                                  WHERE RelatedBugTask.bug = BugTask.bug
                                                    AND RelatedBugTask.id != BugTask.id
                                                    AND ((RelatedBugTask.bugwatch IS NOT NULL
                                                          AND RelatedBugTask.status IN (17, 25, 30))
                                                         OR (RelatedBugTask.product IS NOT NULL
                                                             AND RelatedBugTask.bugwatch IS NULL
                                                             AND RelatedBugTask.status IN (25, 30))))), COUNT(DISTINCT BugTask.bug)
FROM BugTask2 as bugtask
WHERE BugTask.distribution = 1
  AND ((BugTask.status = 10)
       OR (BugTask.status = 15)
       OR (BugTask.status = 20)
       OR (BugTask.status = 21)
       OR (BugTask.status = 22)
       OR (BugTask.status = 25))
  AND Bugtask.duplicateof IS NULL
  AND (Bugtask.private = FALSE
       OR EXISTS
         (SELECT BugSubscription.bug
          FROM BugSubscription,
               TeamParticipation
          WHERE TeamParticipation.person = 3199329
            AND TeamParticipation.team = BugSubscription.person
            AND BugSubscription.bug = bugtask.bug)
       )
GROUP BY BugTask.status,
         BugTask.importance,
         Bugtask.latest_patch_uploaded IS NOT NULL, (EXISTS
                                                   (SELECT TRUE
                                                    FROM BugTask AS RelatedBugTask
                                                    WHERE RelatedBugTask.bug = BugTask.bug
                                                      AND RelatedBugTask.id != BugTask.id
                                                      AND ((RelatedBugTask.bugwatch IS NOT NULL
                                                            AND RelatedBugTask.status IN (17,
                                                                                          25,
                                                                                          30))
                                                           OR (RelatedBugTask.product IS NOT NULL
                                                               AND RelatedBugTask.bugwatch IS NULL
                                                               AND RelatedBugTask.status IN (25,
                                                                                             30)))))

This is 2 seconds hot, 20 seconds with only the bugtask2 table hot.

 GroupAggregate (cost=23292297.50..23292303.95 rows=1 widt...

Or we can update bugsummary to permit querying on the truthfullness of latest_patch, importance and has-upstream

create temporary table bugsummary2(
id serial PRIMARY KEY,
count integer NOT NULL default 0,
product integer ,
productseries integer ,
distribution integer ,
distroseries integer ,
sourcepackagename integer ,
viewed_by integer ,
tag TEXT,
status integer NOT NULL,
importance integer NOT NULL,
has_patch boolean NOT NULL,
fixed_upstream boolean NOT NULL,
milestone integer,
CONSTRAINT bugtask_assignment_checks CHECK (CASE WHEN (product IS NOT NULL) THEN ((((productseries IS NULL) AND (distribution IS NULL)) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (productseries IS NOT NULL) THEN (((distribution IS NULL) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (distribution IS NOT NULL) THEN (distroseries IS NULL) WHEN (distroseries IS NOT NULL) THEN true ELSE false END)
);

INSERT INTO bugsummary2 (
    count, product, productseries, distribution, distroseries,
    sourcepackagename, viewed_by, tag, status, importance, has_patch, fixed_upstream, milestone)
WITH
    -- kill dupes
    relevant_bug AS (SELECT * FROM bug where duplicateof is NULL),

    -- (bug.id, tag) for all bug-tag pairs plus (bug.id, NULL) for all bugs
    bug_tags AS (
        SELECT relevant_bug.id, NULL::text AS tag FROM relevant_bug
        UNION
        SELECT relevant_bug.id, tag
        FROM relevant_bug INNER JOIN bugtag ON relevant_bug.id=bugtag.bug),
    -- (bug.id, NULL) for all public bugs + (bug.id, viewer) for all
    -- (subscribers+assignee) on private bugs
    bug_viewers AS (
        SELECT relevant_bug.id, NULL::integer AS person
        FROM relevant_bug WHERE NOT relevant_bug.private
        UNION
        SELECT relevant_bug.id, assignee AS person
        FROM relevant_bug
        INNER JOIN bugtask ON relevant_bug.id=bugtask.bug
        WHERE relevant_bug.private and bugtask.assignee IS NOT NULL
        UNION
        SELECT relevant_bug.id, bugsubscription.person
        FROM relevant_bug INNER JOIN bugsubscription
            ON bugsubscription.bug=relevant_bug.id WHERE relevant_bug.private),

    -- (bugtask.(bug, product, productseries, distribution, distroseries,
    -- sourcepackagename, status, milestone) for all bugs + the same with
    -- sourcepackage squashed to NULL)
    tasks AS (
        SELECT
            bug, product, productseries, distribution, distroseries,
            sourcepackagename, status, importance, (EXISTS
                                                 (SELECT TRUE
                                                  FROM BugTask AS RelatedBugTask
                                                  WHERE RelatedBugTask.bug = BugTask.bug
                                                    AND RelatedBugTask.id != BugTask.id
                                                    AND ((RelatedBugTask.bugwatch IS NOT NULL
                                                          AND RelatedBugTask.status IN (17, 25, 30))
                                                         OR (RelatedBugTask.product IS NOT NULL
                                                             AND RelatedBugTask....

Still runs the tags aggregate fast:

 i386 | 20702
 apport-bug | 22781
(48 rows)

Time: 211.954 ms

And for this aggregate:
with teams as (select team from teamparticipation where person=3199329)
SELECT status, importance, has_patch, fixed_upstream, sum(count)
   FROM Bugsummary2
WHERE status IN (10, 15, 20, 21, 22, 25)
     AND distribution = 1 AND sourcepackagename IS NULL
     AND (viewed_by IS NULL OR viewed_by in (select team from teams))
    AND tag IS NULL
GROUP BY status, importance, has_patch, fixed_upstream;

Time: 109ms.

The results are very similar, though note that we're not counting *tasks* not *bugs* which means one bug at distro scope, with two source package tasks, having different status and importance, will count twice towards the numbers., I think this will be indistinguishable in practice. It will also suffer the private-paths-to-view-may-count-twice imprecision.

The numbers are fairly close to the existing query - some difference was expected - the difference seems to be ~1%.

Inflates rows-considered-for-tags from 9K to 13K - quite tolerable.

Fixed in db-stable r10679 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/10679>.

Fixed in db-stable r10683 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/10683>.

Fixed in db-stable r10684 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/10684>.

Fixed in db-stable r10699 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/10699>.

Page is 2.5 seconds on staging; there are 4 queries that still hit bugtask and take significant time to run. Series and milestone portlets show ok. The db patch took 7 minutes to apply IIRC, so we're good.