~cjwatson, old query for +commentedbugs with default options:
launchpad_staging=> EXPLAIN (ANALYZE, BUFFERS) SELECT BugTaskFlat.bugtask FROM
BugTaskFlat LEFT JOIN Product ON BugTaskFlat.product = Product.id AND
Product.active WHERE BugTaskFlat.status IN (25, 10, 20, 21, 22, 13, 14) AND
BugTaskFlat.duplicateof IS NULL AND (BugTaskFlat.product IS NULL OR
Product.active = true) AND BugTaskFlat.bug IN (SELECT BugMessage.bug FROM
BugMessage WHERE BugMessage.index > 0 AND BugMessage.owner = 91) AND
(BugTaskFlat.information_type IN (1, 2) OR COALESCE((BugTaskFlat.access_grants)
&& (SELECT ARRAY_AGG(TeamParticipation.team) FROM TeamParticipation WHERE
TeamParticipation.person = 91), false) OR
COALESCE((BugTaskFlat.access_policies) && (SELECT
ARRAY_AGG(AccessPolicyGrant.policy) FROM AccessPolicyGrant JOIN
TeamParticipation ON TeamParticipation.team = AccessPolicyGrant.grantee WHERE
TeamParticipation.person = 91), false)) ORDER BY BugTaskFlat.importance DESC,
BugTaskFlat.bugtask LIMIT 76 OFFSET 0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1768.60..7050.15 rows=76 width=8) (actual time=12.241..594.731
rows=76 loops=1)
Buffers: shared hit=122385
InitPlan 1 (returns $0)
-> Aggregate (cost=62.92..62.93 rows=1 width=32) (actual
time=0.328..0.328 rows=1 loops=1)
Buffers: shared hit=171
-> Index Scan using teamparticipation_person_idx on
teamparticipation (cost=0.43..62.18 rows=293 width=4) (actual
time=0.022..0.255 rows=330 loops=1)
Index Cond: (person = 91)
Buffers: shared hit=171
InitPlan 2 (returns $2)
-> Aggregate (cost=1704.51..1704.52 rows=1 width=32) (actual
time=11.016..11.016 rows=1 loops=1)
Buffers: shared hit=5208
-> Nested Loop (cost=0.85..1704.32 rows=75 width=4) (actual
time=0.035..8.835 rows=13483 loops=1)
Buffers: shared hit=5208
-> Index Scan using teamparticipation_person_idx on
teamparticipation teamparticipation_1 (cost=0.43..62.18 rows=293 width=4)
(actual time=0.014..0.167 rows=330 loops=1)
Index Cond: (person = 91)
Buffers: shared hit=171
-> Index Only Scan using
accesspolicygrant__grantee__policy__key on accesspolicygrant (cost=0.42..5.56
rows=4 width=8) (actual time=0.003..0.021 rows=41 loops=330)
Index Cond: (grantee = teamparticipation_1.team)
Heap Fetches: 13483
Buffers: shared hit=5037
-> Nested Loop Left Join (cost=1.16..1079174.95 rows=15529 width=8)
(actual time=12.240..594.708 rows=76 loops=1)
Filter: ((bugtaskflat.product IS NULL) OR product.active)
Buffers: shared hit=122385
-> Nested Loop Semi Join (cost=0.86..1073265.94 rows=17761 width=12)
(actual time=12.237..594.513 rows=76 loops=1)
Buffers: shared hit=122313
-> Index Scan Backward using
bugtaskflat__importance__bugtask__idx on bugtaskflat (cost=0.43..490403.01
rows=647482 width=16) (actual time=11.641..533.065 rows=4968 loops=1)
Filter: ((duplicateof IS NULL) AND ((information_type =
ANY ('{1,2}'::integer[])) OR COALESCE((access_grants && $0), false) OR
COALESCE((access_policies && $2), false)) AND (status = ANY
('{25,10,20,21,22,13,14}'::integer[])))
Rows Removed by Filter: 56834
Buffers: shared hit=66461
-> Index Scan using bugmessage__bug__index__key on bugmessage
(cost=0.43..0.90 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=4968)
Index Cond: ((bug = bugtaskflat.bug) AND (index > 0))
Filter: (owner = 91)
Rows Removed by Filter: 9
Buffers: shared hit=55852
-> Index Scan using product_pkey on product (cost=0.29..0.32 rows=1
width=5) (actual time=0.002..0.002 rows=0 loops=76)
Index Cond: (bugtaskflat.product = id)
Filter: active
Buffers: shared hit=72
Planning time: 14.577 ms
Execution time: 594.892 ms
(40 rows)
~cjwatson, new query for +commentedbugs with default options:
launchpad_staging=> EXPLAIN (ANALYZE, BUFFERS) SELECT BugTaskFlat.bugtask FROM
BugTaskFlat LEFT JOIN Product ON BugTaskFlat.product = Product.id AND
Product.active WHERE BugTaskFlat.status IN (25, 10, 20, 21, 22, 13, 14) AND
BugTaskFlat.duplicateof IS NULL AND (BugTaskFlat.product IS NULL OR
Product.active = true) AND BugTaskFlat.bug IN (SELECT BugMessage.bug FROM
BugMessage WHERE BugMessage.index > 0 AND BugMessage.owner = 91 UNION SELECT
BugActivity.bug FROM BugActivity WHERE BugActivity.person = 91 AND
(BugActivity.whatchanged != E'bug' OR BugActivity.message != E'added bug')) AND
(BugTaskFlat.information_type IN (1, 2) OR COALESCE((BugTaskFlat.access_grants)
&& (SELECT ARRAY_AGG(TeamParticipation.team) FROM TeamParticipation WHERE
TeamParticipation.person = 91), false) OR
COALESCE((BugTaskFlat.access_policies) && (SELECT
ARRAY_AGG(AccessPolicyGrant.policy) FROM AccessPolicyGrant JOIN
TeamParticipation ON TeamParticipation.team = AccessPolicyGrant.grantee WHERE
TeamParticipation.person = 91), false)) ORDER BY BugTaskFlat.importance DESC,
BugTaskFlat.bugtask LIMIT 76 OFFSET 0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=327777.92..327778.11 rows=76 width=8) (actual
time=420.475..420.493 rows=76 loops=1)
Buffers: shared hit=157609
InitPlan 1 (returns $0)
-> Aggregate (cost=62.92..62.93 rows=1 width=32) (actual
time=0.290..0.290 rows=1 loops=1)
Buffers: shared hit=171
-> Index Scan using teamparticipation_person_idx on
teamparticipation (cost=0.43..62.18 rows=293 width=4) (actual
time=0.013..0.222 rows=330 loops=1)
Index Cond: (person = 91)
Buffers: shared hit=171
InitPlan 2 (returns $2)
-> Aggregate (cost=1704.51..1704.52 rows=1 width=32) (actual
time=10.802..10.802 rows=1 loops=1)
Buffers: shared hit=5208
-> Nested Loop (cost=0.85..1704.32 rows=75 width=4) (actual
time=0.026..8.721 rows=13483 loops=1)
Buffers: shared hit=5208
-> Index Scan using teamparticipation_person_idx on
teamparticipation teamparticipation_1 (cost=0.43..62.18 rows=293 width=4)
(actual time=0.010..0.158 rows=330 loops=1)
Index Cond: (person = 91)
Buffers: shared hit=171
-> Index Only Scan using
accesspolicygrant__grantee__policy__key on accesspolicygrant (cost=0.42..5.56
rows=4 width=8) (actual time=0.003..0.020 rows=41 loops=330)
Index Cond: (grantee = teamparticipation_1.team)
Heap Fetches: 13483
Buffers: shared hit=5037
-> Sort (cost=326010.48..326115.68 rows=42082 width=8) (actual
time=420.474..420.479 rows=76 loops=1)
Sort Key: bugtaskflat.importance DESC, bugtaskflat.bugtask
Sort Method: top-N heapsort Memory: 31kB
Buffers: shared hit=157609
-> Hash Left Join (cost=104139.99..324485.44 rows=42082 width=8)
(actual time=215.728..419.078 rows=2882 loops=1)
Hash Cond: (bugtaskflat.product = product.id)
Filter: ((bugtaskflat.product IS NULL) OR product.active)
Rows Removed by Filter: 5
Buffers: shared hit=157609
-> Nested Loop (cost=97696.16..317915.27 rows=48129 width=12)
(actual time=175.376..377.052 rows=2887 loops=1)
Buffers: shared hit=152313
-> HashAggregate (cost=97695.73..98357.64 rows=66191
width=4) (actual time=175.300..183.677 rows=23123 loops=1)
Group Key: bugmessage.bug
Buffers: shared hit=43535
-> Append (cost=370.29..97530.25 rows=66191
width=4) (actual time=8.227..152.611 rows=77181 loops=1)
Buffers: shared hit=43535
-> Bitmap Heap Scan on bugmessage
(cost=370.29..27803.69 rows=24318 width=4) (actual time=8.226..34.503
rows=26797 loops=1)
Recheck Cond: ((owner = 91) AND (index >
0))
Heap Blocks: exact=16026
Buffers: shared hit=16102
-> Bitmap Index Scan on
bugmessage__owner__index__idx (cost=0.00..364.22 rows=24318 width=0) (actual
time=5.221..5.221 rows=26797 loops=1)
Index Cond: ((owner = 91) AND
(index > 0))
Buffers: shared hit=76
-> Bitmap Heap Scan on bugactivity
(cost=744.79..69064.65 rows=41873 width=4) (actual time=60.435..108.887
rows=50384 loops=1)
Recheck Cond: (person = 91)
Filter: ((whatchanged <> 'bug'::text) OR
(message <> 'added bug'::text))
Rows Removed by Filter: 1060
Heap Blocks: exact=27231
Buffers: shared hit=27433
-> Bitmap Index Scan on
bugactivity_person_datechanged_idx (cost=0.00..734.32 rows=50795 width=0)
(actual time=54.802..54.802 rows=51444 loops=1)
Index Cond: (person = 91)
Buffers: shared hit=202
-> Index Scan using bugtaskflat__bug__idx on bugtaskflat
(cost=0.43..3.30 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=23123)
Index Cond: (bug = bugmessage.bug)
Filter: ((duplicateof IS NULL) AND
((information_type = ANY ('{1,2}'::integer[])) OR COALESCE((access_grants &&
$0), false) OR COALESCE((access_policies && $2), false)) AND (status = ANY
('{25,10,20,21,22,13,14}'::integer[])))
Rows Removed by Filter: 1
Buffers: shared hit=108778
-> Hash (cost=5907.98..5907.98 rows=42868 width=5) (actual
time=40.277..40.277 rows=42868 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2061kB
Buffers: shared hit=5296
-> Seq Scan on product (cost=0.00..5907.98 rows=42868
width=5) (actual time=0.011..30.002 rows=42868 loops=1)
Filter: active
Rows Removed by Filter: 18330
Buffers: shared hit=5296
Planning time: 8.906 ms
Execution time: 421.888 ms
(66 rows)
~willamsusa (spammer), old query for +commentedbugs but with no restrictions on
task or duplicate status:
launchpad_staging=> EXPLAIN (ANALYZE, BUFFERS) SELECT BugTaskFlat.bugtask FROM
BugTaskFlat LEFT JOIN Product ON BugTaskFlat.product = Product.id AND
Product.active WHERE (BugTaskFlat.product IS NULL OR Product.active = true) AND
BugTaskFlat.bug IN (SELECT BugMessage.bug FROM BugMessage WHERE
BugMessage.index > 0 AND BugMessage.owner = 7346751) AND
(BugTaskFlat.information_type IN (1, 2) OR COALESCE((BugTaskFlat.access_grants)
&& (SELECT ARRAY_AGG(TeamParticipation.team) FROM TeamParticipation WHERE
TeamParticipation.person = 7346751), false) OR
COALESCE((BugTaskFlat.access_policies) && (SELECT
ARRAY_AGG(AccessPolicyGrant.policy) FROM AccessPolicyGrant JOIN
TeamParticipation ON TeamParticipation.team = AccessPolicyGrant.grantee WHERE
TeamParticipation.person = 7346751), false)) ORDER BY BugTaskFlat.importance
DESC, BugTaskFlat.bugtask LIMIT 76 OFFSET 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=656.30..656.49 rows=76 width=8) (actual time=0.066..0.066 rows=1
loops=1)
Buffers: shared hit=11
InitPlan 1 (returns $0)
-> Aggregate (cost=4.25..4.26 rows=1 width=32) (never executed)
-> Index Scan using teamparticipation_person_idx on
teamparticipation (cost=0.43..4.25 rows=2 width=4) (never executed)
Index Cond: (person = 7346751)
InitPlan 2 (returns $2)
-> Aggregate (cost=21.77..21.78 rows=1 width=32) (never executed)
-> Nested Loop (cost=0.85..21.76 rows=1 width=4) (never executed)
-> Index Scan using teamparticipation_person_idx on
teamparticipation teamparticipation_1 (cost=0.43..4.25 rows=2 width=4) (never
executed)
Index Cond: (person = 7346751)
-> Index Only Scan using
accesspolicygrant__grantee__policy__key on accesspolicygrant (cost=0.42..8.72
rows=4 width=8) (never executed)
Index Cond: (grantee = teamparticipation_1.team)
Heap Fetches: 0
-> Sort (cost=630.26..630.69 rows=172 width=8) (actual time=0.065..0.065
rows=1 loops=1)
Sort Key: bugtaskflat.importance DESC, bugtaskflat.bugtask
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=11
-> Nested Loop Left Join (cost=121.96..624.02 rows=172 width=8)
(actual time=0.050..0.051 rows=1 loops=1)
Filter: ((bugtaskflat.product IS NULL) OR product.active)
Buffers: shared hit=11
-> Nested Loop (cost=121.67..560.64 rows=197 width=12) (actual
time=0.039..0.040 rows=1 loops=1)
Buffers: shared hit=8
-> HashAggregate (cost=121.24..122.00 rows=76 width=4)
(actual time=0.023..0.024 rows=1 loops=1)
Group Key: bugmessage.bug
Buffers: shared hit=4
-> Index Scan using bugmessage__owner__index__idx
on bugmessage (cost=0.43..121.05 rows=76 width=4) (actual time=0.017..0.018
rows=1 loops=1)
Index Cond: ((owner = 7346751) AND (index > 0))
Buffers: shared hit=4
-> Index Scan using bugtaskflat__bug__idx on bugtaskflat
(cost=0.43..5.74 rows=3 width=16) (actual time=0.013..0.014 rows=1 loops=1)
Index Cond: (bug = bugmessage.bug)
Filter: ((information_type = ANY
('{1,2}'::integer[])) OR COALESCE((access_grants && $0), false) OR
COALESCE((access_policies && $2), false))
Buffers: shared hit=4
-> Index Scan using product_pkey on product (cost=0.29..0.31
rows=1 width=5) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (bugtaskflat.product = id)
Filter: active
Buffers: shared hit=3
Planning time: 11.878 ms
Execution time: 0.215 ms
(39 rows)
~willamsusa (spammer), new query for +commentedbugs but with no restrictions on
task or duplicate status:
launchpad_staging=> EXPLAIN (ANALYZE, BUFFERS) SELECT BugTaskFlat.bugtask FROM
BugTaskFlat LEFT JOIN Product ON BugTaskFlat.product = Product.id AND
Product.active WHERE (BugTaskFlat.product IS NULL OR Product.active = true) AND
BugTaskFlat.bug IN (SELECT BugMessage.bug FROM BugMessage WHERE
BugMessage.index > 0 AND BugMessage.owner = 7346751 UNION SELECT
BugActivity.bug FROM BugActivity WHERE BugActivity.person = 7346751 AND
(BugActivity.whatchanged != E'bug' OR BugActivity.message != E'added bug')) AND
(BugTaskFlat.information_type IN (1, 2) OR COALESCE((BugTaskFlat.access_grants)
&& (SELECT ARRAY_AGG(TeamParticipation.team) FROM TeamParticipation WHERE
TeamParticipation.person = 7346751), false) OR
COALESCE((BugTaskFlat.access_policies) && (SELECT
ARRAY_AGG(AccessPolicyGrant.policy) FROM AccessPolicyGrant JOIN
TeamParticipation ON TeamParticipation.team = AccessPolicyGrant.grantee WHERE
TeamParticipation.person = 7346751), false)) ORDER BY BugTaskFlat.importance
DESC, BugTaskFlat.bugtask LIMIT 76 OFFSET 0;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2077.37..2077.56 rows=76 width=8) (actual time=0.086..0.086
rows=2 loops=1)
Buffers: shared hit=27
InitPlan 1 (returns $0)
-> Aggregate (cost=4.25..4.26 rows=1 width=32) (never executed)
-> Index Scan using teamparticipation_person_idx on
teamparticipation (cost=0.43..4.25 rows=2 width=4) (never executed)
Index Cond: (person = 7346751)
InitPlan 2 (returns $2)
-> Aggregate (cost=21.77..21.78 rows=1 width=32) (never executed)
-> Nested Loop (cost=0.85..21.76 rows=1 width=4) (never executed)
-> Index Scan using teamparticipation_person_idx on
teamparticipation teamparticipation_1 (cost=0.43..4.25 rows=2 width=4) (never
executed)
Index Cond: (person = 7346751)
-> Index Only Scan using
accesspolicygrant__grantee__policy__key on accesspolicygrant (cost=0.42..8.72
rows=4 width=8) (never executed)
Index Cond: (grantee = teamparticipation_1.team)
Heap Fetches: 0
-> Sort (cost=2051.33..2052.70 rows=547 width=8) (actual time=0.085..0.085
rows=2 loops=1)
Sort Key: bugtaskflat.importance DESC, bugtaskflat.bugtask
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=27
-> Nested Loop Left Join (cost=443.37..2031.51 rows=547 width=8)
(actual time=0.062..0.077 rows=2 loops=1)
Filter: ((bugtaskflat.product IS NULL) OR product.active)
Buffers: shared hit=27
-> Nested Loop (cost=443.08..1830.41 rows=625 width=12)
(actual time=0.054..0.063 rows=2 loops=1)
Buffers: shared hit=21
-> HashAggregate (cost=442.65..445.06 rows=241 width=4)
(actual time=0.042..0.044 rows=2 loops=1)
Group Key: bugmessage.bug
Buffers: shared hit=13
-> Append (cost=0.43..442.05 rows=241 width=4)
(actual time=0.016..0.035 rows=5 loops=1)
Buffers: shared hit=13
-> Index Scan using
bugmessage__owner__index__idx on bugmessage (cost=0.43..121.05 rows=76
width=4) (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: ((owner = 7346751) AND
(index > 0))
Buffers: shared hit=4
-> Index Scan using
bugactivity_person_datechanged_idx on bugactivity (cost=0.56..318.60 rows=165
width=4) (actual time=0.011..0.018 rows=4 loops=1)
Index Cond: (person = 7346751)
Filter: ((whatchanged <> 'bug'::text) OR
(message <> 'added bug'::text))
Rows Removed by Filter: 1
Buffers: shared hit=9
-> Index Scan using bugtaskflat__bug__idx on bugtaskflat
(cost=0.43..5.71 rows=3 width=16) (actual time=0.008..0.008 rows=1 loops=2)
Index Cond: (bug = bugmessage.bug)
Filter: ((information_type = ANY
('{1,2}'::integer[])) OR COALESCE((access_grants && $0), false) OR
COALESCE((access_policies && $2), false))
Buffers: shared hit=8
-> Index Scan using product_pkey on product (cost=0.29..0.31
rows=1 width=5) (actual time=0.006..0.006 rows=1 loops=2)
Index Cond: (bugtaskflat.product = id)
Filter: active
Buffers: shared hit=6
Planning time: 8.463 ms
Execution time: 0.211 ms
(46 rows)
So this does pick somewhat different indexes to use in various cases, but the
end result seems to still be reasonably performant for both users with few bugs
and users with many bugs.
--
https://code.launchpad.net/~cjwatson/launchpad/improve-bug-commenter-search/+merge/369526
Your team Launchpad code reviewers is subscribed to branch lp:launchpad.
_______________________________________________
Mailing list: https://launchpad.net/~launchpad-reviewers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~launchpad-reviewers
More help : https://help.launchpad.net/ListHelp
