Timeout on MaloneApplication:+bugs with fti and nearly all bug status

 17 SELECT COUNT(*) FROM BugTask LEFT JOIN Product ON BugTask.product = Product.id AND Product.active ... i @@ ftq($STRING)) AND (Bugtask.product IS NULL OR Product.active = TRUE) AND Bug.private = FALSE:
   GET: 17 Robots: 5 Local: 11
     16 https://bugs.launchpad.net/bugs/+bugs (MaloneApplication:+bugs)
       OOPS-1893B1153, OOPS-1893C1177, OOPS-1893C1472, OOPS-1893C946, OOPS-1893D417
      1 https://launchpad.net/bugs/+bugs (MaloneApplication:+bugs)
       OOPS-1893G1374

cold

 Aggregate (cost=341410.33..341410.34 rows=1 width=0) (actual time=135843.359..135843.359 rows=1 loops=1)
   -> Nested Loop Left Join (cost=199512.68..341408.66 rows=667 width=0) (actual time=7456.201..135841.320 rows=1154 loops=1)
         Filter: ((bugtask.product IS NULL) OR product.active)
         -> Hash Join (cost=199512.68..341191.97 rows=732 width=4) (actual time=7456.140..135825.973 rows=1155 loops=1)
               Hash Cond: (bugtask.bug = bug.id)
               Join Filter: ((((bug.fti)::tsvector @@ '''race'''::tsquery) OR ((bugtask.fti)::tsvector @@ '''race'''::tsquery)) AND ((bugtask.status = 10) OR ((bugtask.status = 15) AND (bug.date_last_message IS NOT NULL) AND (bugtask.date_incomplete <= bug.date_last_message)) OR ((bugtask.status = 15) AND ((bug.date_last_message IS NULL) OR (bugtask.date_incomplete > bug.date_last_message))) OR (bugtask.status = 17) OR (bugtask.status = 18) OR (bugtask.status = 19) OR (bugtask.status = 20) OR (bugtask.status = 21) OR (bugtask.status = 22) OR (bugtask.status = 25) OR (bugtask.status = 30)))
               -> Seq Scan on bugtask (cost=0.00..63037.94 rows=563609 width=135) (actual time=0.046..4976.433 rows=826587 loops=1)
                     Filter: ((status = 10) OR ((date_incomplete IS NOT NULL) AND (status = 15)) OR (status = 15) OR (status = 17) OR (status = 18) OR (status = 19) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25) OR (status = 30))
               -> Hash (cost=165774.39..165774.39 rows=533143 width=386) (actual time=3860.390..3860.390 rows=530602 loops=1)
                     -> Seq Scan on bug (cost=0.00..165774.39 rows=533143 width=386) (actual time=0.018..2407.350 rows=530602 loops=1)
                           Filter: ((duplicateof IS NULL) AND (NOT private))
         -> Index Scan using product_pkey on product (cost=0.00..0.28 rows=1 width=5) (actual time=0.007..0.007 rows=0 loops=1155)
               Index Cond: (bugtask.product = product.id)
               Filter: product.active
 Total runtime: 135843.565 ms

 Aggregate (cost=341410.33..341410.34 rows=1 width=0) (actual time=12224.685..12224.685 rows=1 loops=1)
   -> Nested Loop Left Join (cost=199512.68..341408.66 rows=667 width=0) (actual time=2724.508..12223.684 rows=1154 loops=1)
         Filter: ((bugtask.product IS NULL) OR product.active)
         -> Hash Join (cost=199512.68..341191.97 rows=732 width=4) (actual time=2724.479..12215.253 rows=1155 loops=1)
               Hash Cond: (bugtask.bug = bug.id)
               Join Filter: ((((bug.fti)::tsvector @@ '''race'''::tsquery) OR ((bugtask.fti)::tsvector @@ '''race'''::tsquery)) AND ((bugtask.status = 10) OR ((bugtask.status = 15) AND (bug.date_last_message IS NOT NULL) AND (bugtask.date_incomplete <= bug.date_last_message)) OR ((bugtask.status = 15) AND ((bug.date_last_message IS NULL) OR (bugtask.date_incomplete > bug.date_last_message))) OR (bugtask.status = 17) OR (bugtask.status = 18) OR (bugtask.status = 19) OR (bugtask.status = 20) OR (bugtask.status = 21) OR (bugtask.status = 22) OR (bugtask.status = 25) OR (bugtask.status = 30)))
               -> Seq Scan on bugtask (cost=0.00..63037.94 rows=563609 wid...

Changing the opinion filter to be just != 16 takes 1 second off of the peak time.

there are 1160 hits for the fti, but the planner is materialising all of the bugs first.

a split query though:
SELECT COUNT(*) from (select bug
FROM BugTask
LEFT JOIN Product ON BugTask.product = Product.id
AND Product.active
JOIN Bug ON BugTask.bug = Bug.id
WHERE Bug.id = BugTask.bug
  AND ((BugTask.status != 16)
 )
  AND Bug.duplicateof IS NULL
  AND (BugTask.fti @@ ftq('race'))
  AND (Bugtask.product IS NULL
       OR Product.active = TRUE)
  AND Bug.private = FALSE
union
select bug
FROM BugTask
LEFT JOIN Product ON BugTask.product = Product.id
AND Product.active
JOIN Bug ON BugTask.bug = Bug.id
WHERE Bug.id = BugTask.bug
  AND ((BugTask.status != 16)
 )
  AND Bug.duplicateof IS NULL
  AND (Bug.fti @@ ftq('race'))
  AND (Bugtask.product IS NULL
       OR Product.active = TRUE)
  AND Bug.private = FALSE) as _tmp;

                                                                        QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=8645.53..8645.54 rows=1 width=0) (actual time=809.507..809.507 rows=1 loops=1)
   -> HashAggregate (cost=8627.10..8635.29 rows=819 width=4) (actual time=809.131..809.377 rows=825 loops=1)
         -> Append (cost=0.00..8625.05 rows=819 width=4) (actual time=0.683..807.923 rows=1172 loops=1)
               -> Nested Loop (cost=0.00..1740.54 rows=115 width=4) (actual time=0.682..110.691 rows=114 loops=1)
                     -> Nested Loop Left Join (cost=0.00..1007.57 rows=115 width=4) (actual time=0.659..108.414 rows=133 loops=1)
                           Filter: ((public.bugtask.product IS NULL) OR public.product.active)
                           -> Index Scan using bugtask_fti on bugtask (cost=0.00..386.98 rows=126 width=8) (actual time=0.655..107.620 rows=133 loops=1)
                                 Index Cond: ((fti)::tsvector @@ '''race'''::tsquery)
                                 Filter: (status <> 16)
                           -> Index Scan using product_pkey on product (cost=0.00..4.91 rows=1 width=5) (actual time=0.004..0.004 rows=0 loops=133)
                                 Index Cond: (public.bugtask.product = public.product.id)
                                 Filter: public.product.active
                     -> Index Scan using bug_pkey on bug (cost=0.00..6.36 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=133)
                           Index Cond: (public.bug.id = public.bugtask.bug)
                           Filter: ((public.bug.duplicateof IS NULL) AND (NOT public.bug.private))
               -> Nested Loop Left Join (cost=261.00..6876.32 rows=704 width=4) (actual time=408.004..696.884 rows=1058 loops=1)
                     Filter: ((public.bugtask.product IS NULL) OR public.product.active)
                     -> Nested Loop (cost=261.00..6651.16 rows=772 width=8) (actual time=407.997..693.581 rows=1059 loops=1)
                           -> Bitmap Heap Scan on bug (cost=261.00..2758.6...

bugtask.fti indexes statusexplanation and targetnamecache, neither of which are needed for fti searches. So I'm going to drop the search on bugtask.fti.

We have been threatening to drop statusexplanation and targetnamecache since 2007. Maybe now is the time to do that.

https://bugs.launchpad.net/launchpad/+bug/88545 covers the statusexplanation deprecation. I agree we should do the deprecation, but its considerably simpler to fix the immediate issue - once we're out of critical territory we can tackle the datacleanup, bugzilla integration etc issues.

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