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
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;
-------
Aggregate (cost=8645.
-> HashAggregate (cost=8627.
-> 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=261.