https://lp-oops.canonical.com/oops.py/?oopsid=1681ED2225
Branch: launchpad-rev-11315
Revno: 11315
SQL time: 14211 ms
Non-sql time: 103 ms
Total time: 14314 ms
Statement Count: 30
https://bugs.edge.launchpad.net/ubuntu/+bug/614983/+distrotask
26. 295 14007ms launchpad-main-master SELECT Bug.heat FROM Bug, Bugtask, DistroSeries WHERE Bugtask.bug = Bug.id AND Bugtask.distroseries = DistroSeries.id AND DistroSeries.distribution = 3 ORDER BY Bug.heat DESC LIMIT 1
is the cuplrit here.
lpmain_staging=> explain analyze SELECT Bug.heat FROM Bug, Bugtask, DistroSeries WHERE Bugtask.bug = Bug.id AND Bugtask.distroseries = DistroSeries.id AND DistroSeries.distribution = 3 ORDER BY Bug.heat DESC LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2.38..1413.68 rows=1 width=4) (actual time=12091.186..12091.186 rows=0 loops=1)
-> Nested Loop (cost=2.38..661903.33 rows=469 width=4) (actual time=12091.182..12091.182 rows=0 loops=1)
Join Filter: (bugtask.distroseries = distroseries.id)
-> Nested Loop (cost=0.00..645941.88 rows=709292 width=8) (actual time=0.060..7570.463 rows=709308 loops=1)
-> Index Scan Backward using bug__heat__idx on bug (cost=0.00..348482.43 rows=607705 width=8) (actual time=0.030..1233.113 rows=607701 loops=1)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..0.48 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=607701)
Index Cond: (bugtask.bug = bug.id)
-> Materialize (cost=2.38..2.39 rows=1 width=4) (actual time=0.001..0.003 rows=4 loops=709308)
-> Seq Scan on distroseries (cost=0.00..2.38 rows=1 width=4) (actual time=0.017..0.033 rows=4 loops=1)
Filter: (distribution = 3)
Total runtime: 12091.312 ms
This rewritten query runs in 60ms on production:
SELECT max(Bug.heat) FROM Bug, BugTask, DistroSeries
WHERE BugTask.bug = Bug.id
AND BugTask.
AND DistroSeries.
I can't really explain why PG is choosing such an appalling query plan for the current query. I suspect it to do with ORDER BY Bug.heat DESC LIMIT 1 not quite being identical to MAX(Bug.heat) - the first will return NULL if there are any NULL Bug.heat due to the default ordering of NULL values. We are smart enough to know there is a NOT NULL constraint on that column, but PG 8.3 might not be.