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 BugTaskJOIN 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 _______________________________________________ Mailing list: https://launchpad.net/~launchpad-dev Post to : [email protected] Unsubscribe : https://launchpad.net/~launchpad-dev More help : https://help.launchpad.net/ListHelp
