Distribution:+bugtarget-portlet-bugfilters-stats timeouts

SourcepackageName and Product are an unnecessary joins for the COUNT queries. I suspect this is coming from prejoins, and we would benefit by removing them and doing two separate queries for the sourcepackagenames and products if they are needed.

The private flag has always caused bad queries. One option for tackling this problem is to do two queries - one for private, one for public (either client side or aggregating two subqueries queries server side). We can then create partial indexes on the column being used for the join (eg. CREATE UNIQUE INDEX bug__id__private__key ON Bug(id) WHERE private IS TRUE and CREATE INDEX bug__id__public__key ON Bug(id) WHERE private IS FALSE. We would have to test what columns would use the partial index - product, distribution, sourcepackagename and id are all candidates.

One thing that can be confusing the planner is using = for boolean comparisons. Bug.private = FALSE is different to Bug.private IS FALSE due to SQL's three value logic, and in particular, using = would cause the partial indexes I described to not be used.

distribution == 1 is also always an issue. We might consider duplicating existing indexes as partial indexes 'WHERE distribution = 1', because we know Ubuntu is a special case and will remain so. eg. CREATE INDEX bugtask__status__ubuntu__idx ON BugTask(status) WHERE distribution = 1.

Of course, doing a query for 'all open Ubuntu Bugs' is going to be slow because this is a lot of bugs. However, people need counts of bugs in various statuses for their projects. We should consider breaking out these counts into a table for public bugs. eg.

CREATE TABLE BugTaskStatusCount (
    id serial PRIMARY KEY,
    product integer REFERENCES Product,
    distribution integer REFERENCES Distribution,
    sourcepackagename REFERENCES SourcepackageName,
    status integer NOT NULL,
    count integer NOT NULL
    );

The table would need to be maintained of course - triggers, python code in the model objects, a cronscript, whatever.

The table would be queried for public bug counts. Private bug counts would be done live, and since private bugs are a minority we can create partial indexes to make the queries fast.

For now I'm going to tune what we have. The plans show seqscans on bugtask; we can do that in ~ 4 seconds - if we do just one it should be fine for another few years.

current portlet contents (ubuntu - worst case):

aggregates:
status:
53645 New bugs
90587 Open bugs
953 In-progress bugs
912 Incomplete bugs (can expire) - needs 'Bug.date_last_updated < CURRENT_TIMESTAMP AT TIME ZONE $STRING - interval $STRING' as a aggregation column

importance:
40 Critical bugs
1151 High importance bugs

Individual lookup:
0 Bugs assigned to me - best done as a separate individual search

other attribute checks:
2237 Bugs with patches - scans, should include
2824 Bugs fixed elsewhere - scans, should include
127 Open CVE bugs - CVE reports - separate lookup will be tolerable

so it looks like something like the following will work with a little python postprocess (of a few hundred rows at most)
select bugtask.status, bugtask.importance, Bug.date_last_updated < CURRENT_TIMESTAMP AT TIME ZONE $STRING - interval,
bug.latest_patch_uploaded is not null, (pending_bugwatch_clause), count(*) from ...
group by bugtask.status, bugtask.importance, Bug.date_last_updated < CURRENT_TIMESTAMP AT TIME ZONE $STRING - interval,
bug.latest_patch_uploaded is not null, (pending_bugwatch_clause);

expiry needs an exclude on open-bugtask-counts, which for an entire scan will be expensive - leaving that as standalone for now.

bugcve is 47ms hot, leave it as a separate query still.

My patch creates this query -

SELECT BugTask.status, BugTask.importance, Bug.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 BugTask, Bug WHERE Bug.id = BugTask.bug AND 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 Bug.duplicateof is NULL GROUP BY BugTask.status, BugTask.importance, Bug.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)))
                    )
                )

with plan:

 GroupAggregate (cost=1294352.38..1297261.11 rows=67 width=24) (actual time=3294.610..3343.103 rows=123 loops=1)
   -> Sort (cost=1294352.38..1294766.18 rows=165520 width=24) (actual time=3244.369..3263.529 rows=89548 loops=1)
         Sort Key: bugtask.status, bugtask.importance, ((bug.latest_patch_uploaded IS NOT NULL)), ((SubPlan 1))
         Sort Method: quicksort Memory: 10068kB
         -> Hash Join (cost=182139.76..1280004.57 rows=165520 width=24) (actual time=1988.003..3126.985 rows=89548 loops=1)
               Hash Cond: (bugtask.bug = bug.id)
               -> Bitmap Heap Scan on bugtask (cost=5122.68..45475.49 rows=165520 width=16) (actual time=109.526..326.908 rows=190437 loops=1)
                     Recheck Cond: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
                     Filter: (distribution = 1)
                     -> BitmapOr (cost=5122.68..5122.68 rows=308866 width=0) (actual time=90.837..90.837 rows=0 loops=1)
                           -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..2903.70 rows=184317 width=0) (actual time=53.972..53.972 rows=184511 loops=1)
                                 Index Cond: (status = 10)
                           -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..279.21 rows=17585 width=0) (actual time=6.017..6.017 rows=17637 loops=1)
                       ...

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

10seconds hot on prod, 4.5 seconds on qastaging.