Comment 3 for bug 716774

Revision history for this message
Robert Collins (lifeless) wrote :

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.63 rows=654 width=4) (actual time=407.972..685.672 rows=723 loops=1)
                                 Recheck Cond: ((fti)::tsvector @@ '''race'''::tsquery)
                                 Filter: ((duplicateof IS NULL) AND (NOT private))
                                 -> Bitmap Index Scan on bug_fti (cost=0.00..260.84 rows=874 width=0) (actual time=399.603..399.603 rows=25712 loops=1)
                                       Index Cond: ((fti)::tsvector @@ '''race'''::tsquery)
                           -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..5.94 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=723)
                                 Index Cond: (public.bugtask.bug = public.bug.id)
                                 Filter: (public.bugtask.status <> 16)
                     -> Index Scan using product_pkey on product (cost=0.00..0.28 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=1059)
                           Index Cond: (public.bugtask.product = public.product.id)
                           Filter: public.product.active