launchpad-dev team mailing list archive

[Jeroen Vermeulen <jtv@xxxxxxxxxxxxx>]

Curtis Hovey wrote:

> Barring that, I am trying to make this subselect faster by many orders of
> magnitude. There is an index on bugtask.sourcepackagename, but the query
> does not use it. 3/5 of all bugtasks has a sourcepackgename, so an index
> may be irrelevant.
>
> explain
>         SELECT BugTask.sourcepackagename,
>             sum(Bug.heat) AS total_heat,
>             count(Bug.id) AS total_bugs 
>         FROM 
>             BugTask
>             JOIN Bug ON bugtask.bug = Bug.id
>             WHERE
>                 BugTask.sourcepackagename is not NULL
>                 AND BugTask.distribution = 1
>                 AND BugTask.status in (10, 15, 20, 21, 22, 25)
>         GROUP BY BugTask.sourcepackagename;

We got this faster for now by filtering out bugs with low heat numbers 
(and counting distinct BugTask.bug instead of Bug.id).

There's an index on bug heat; I suppose we could see some irregularity 
if the fraction of bugs that meet the heat threshold hovers near the 
threshold where the database switches from index scans to table scans.


Jeroen