launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #23796
Re: [Merge] lp:~cjwatson/launchpad/improve-bug-commenter-search into lp:launchpad
~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.
References
