launchpad_prod_3=# explain analyze SELECT BugTask.*, Bug.* launchpad_prod_3-# FROM BugTask JOIN Bug ON BugTask.bug = Bug.id launchpad_prod_3-# WHERE Bug.id = BugTask.bug launchpad_prod_3-# AND BugTask.product = 10294 launchpad_prod_3-# AND ( launchpad_prod_3(# (BugTask.status = 10) launchpad_prod_3(# OR (BugTask.status = 15) OR (BugTask.status = 20) launchpad_prod_3(# OR (BugTask.status = 21) OR (BugTask.status = 22) launchpad_prod_3(# OR (BugTask.status = 25)) launchpad_prod_3-# AND Bug.duplicateof is NULL AND launchpad_prod_3-# BugTask.id IN ( launchpad_prod_3(# SELECT BugTask.id FROM BugTask, BugAffectsPerson launchpad_prod_3(# WHERE launchpad_prod_3(# BugTask.bug = BugAffectsPerson.bug launchpad_prod_3(# AND BugAffectsPerson.person = 78 launchpad_prod_3(# AND BugAffectsPerson.affected = TRUE) launchpad_prod_3-# ORDER BY Bug.date_last_updated DESC LIMIT 76 OFFSET 0; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1950.09..7546.34 rows=76 width=1400) (actual time=155.107..9665.562 rows=58 loops=1) -> Nested Loop Semi Join (cost=1950.09..660246.29 rows=8940 width=1400) (actual time=155.106..9665.506 rows=58 loops=1) Join Filter: (public.bugtask.id = public.bugtask.id) -> Nested Loop (cost=0.00..556112.00 rows=8940 width=1400) (actual time=65.912..8744.850 rows=5772 loops=1) -> Index Scan Backward using bug__date_last_updated__idx on bug (cost=0.00..263671.56 rows=677083 width=1116) (actual time=0.146..5893.546 rows=677338 loops=1) Filter: (duplicateof IS NULL) -> Index Scan using bugtask__product__bug__key on bugtask (cost=0.00..0.42 rows=1 width=284) (actual time=0.004..0.004 rows=0 loops=677338) Index Cond: ((public.bugtask.product = 10294) AND (public.bugtask.bug = bug.id)) Filter: ((public.bugtask.status = 10) OR (public.bugtask.status = 15) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25)) -> Materialize (cost=1950.09..1955.17 rows=508 width=4) (actual time=0.003..0.065 rows=480 loops=5772) -> Nested Loop (cost=6.70..1949.58 rows=508 width=4) (actual time=14.713..42.650 rows=482 loops=1) -> Bitmap Heap Scan on bugaffectsperson (cost=6.70..571.40 rows=357 width=4) (actual time=14.601..23.301 rows=345 loops=1) Recheck Cond: (person = 78) Filter: affected -> Bitmap Index Scan on bugaffectsperson__person__idx (cost=0.00..6.62 rows=361 width=0) (actual time=14.410..14.410 rows=346 loops=1) Index Cond: (person = 78) -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.85 rows=1 width=8) (actual time=0.051..0.055 rows=1 loops=345) Index Cond: (public.bugtask.bug = bugaffectsperson.bug) Total runtime: 9665.923 ms (19 rows)
launchpad_prod_3=# explain analyze SELECT BugTask.*, Bug.* n.bug n.person = 78 n.affected = TRUE) last_updated DESC LIMIT 76 OFFSET 0;
QUERY PLAN ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- --- 09..7546. 34 rows=76 width=1400) (actual time=155. 107..9665. 562 rows=58 loops=1) 09..660246. 29 rows=8940 width=1400) (actual time=155. 106..9665. 506 rows=58 loops=1) 00..556112. 00 rows=8940 width=1400) (actual time=65. 912..8744. 850 rows=5772 loops=1) last_updated_ _idx on bug (cost=0. 00..263671. 56 rows=677083 width=1116) (actual time=0. 146..5893. 546 rows=677338 loops=1)
Filter: (duplicateof IS NULL) _product_ _bug__key on bugtask (cost=0.00..0.42 rows=1 width=284) (actual time=0.004..0.004 rows=0 loops=677338)
Index Cond: ((public. bugtask. product = 10294) AND (public.bugtask.bug = bug.id))
Filter: ((public. bugtask. status = 10) OR (public. bugtask. status = 15) OR (public. bugtask. status = 20) OR (public. bugtask. status = 21) OR (public. bugtask. status = 22) OR (public. bugtask. status = 25)) 09..1955. 17 rows=508 width=4) (actual time=0.003..0.065 rows=480 loops=5772)
-> Bitmap Heap Scan on bugaffectsperson (cost=6.70..571.40 rows=357 width=4) (actual time=14.601..23.301 rows=345 loops=1)
Recheck Cond: (person = 78)
Filter: affected
- > Bitmap Index Scan on bugaffectsperso n__person_ _idx (cost=0.00..6.62 rows=361 width=0) (actual time=14.410..14.410 rows=346 loops=1)
Index Cond: (person = 78)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.85 rows=1 width=8) (actual time=0.051..0.055 rows=1 loops=345)
Index Cond: (public.bugtask.bug = bugaffectsperso n.bug)
launchpad_prod_3-# FROM BugTask JOIN Bug ON BugTask.bug = Bug.id
launchpad_prod_3-# WHERE Bug.id = BugTask.bug
launchpad_prod_3-# AND BugTask.product = 10294
launchpad_prod_3-# AND (
launchpad_prod_3(# (BugTask.status = 10)
launchpad_prod_3(# OR (BugTask.status = 15) OR (BugTask.status = 20)
launchpad_prod_3(# OR (BugTask.status = 21) OR (BugTask.status = 22)
launchpad_prod_3(# OR (BugTask.status = 25))
launchpad_prod_3-# AND Bug.duplicateof is NULL AND
launchpad_prod_3-# BugTask.id IN (
launchpad_prod_3(# SELECT BugTask.id FROM BugTask, BugAffectsPerson
launchpad_prod_3(# WHERE
launchpad_prod_3(# BugTask.bug = BugAffectsPerso
launchpad_prod_3(# AND BugAffectsPerso
launchpad_prod_3(# AND BugAffectsPerso
launchpad_prod_3-# ORDER BY Bug.date_
-------
Limit (cost=1950.
-> Nested Loop Semi Join (cost=1950.
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.
-> Index Scan Backward using bug__date_
-> Index Scan using bugtask_
-> Materialize (cost=1950.
-> Nested Loop (cost=6.70..1949.58 rows=508 width=4) (actual time=14.713..42.650 rows=482 loops=1)
Total runtime: 9665.923 ms
(19 rows)