Migrate mentions for MergeRequest
Number of rows affected
- All MRs with with mentions ~932771
- Open MRs with mentions ~28721
- Closed MRs with mentions ~137466
- Merged MRs with mentions ~766542
-- all MRs that have a mention in description or title: ~932771
explain select id from merge_requests where (description like '%@%' OR title like '%@%')
Seq Scan on public.merge_requests (cost=0.00..9282761.66 rows=867036 width=4) (actual time=3.909..343964.373 rows=932771 loops=1)
Filter: ((merge_requests.description ~~ '%@%'::text) OR ((merge_requests.title)::text ~~ '%@%'::text))
Rows Removed by Filter: 36240680
Buffers: shared hit=454890 read=2307218 dirtied=6162 written=2904
I/O Timings: read=306872.101 write=923.727
-- open MRs that have a mention in description or title: ~28721
explain select id from merge_requests where state_id = 1 and (description like '%@%' OR title like '%@%')
Index Scan using idx_merge_requests_on_target_project_id_and_iid_opened on public.merge_requests (cost=0.43..2038387.45 rows=41280 width=4) (actual time=82.046..651075.740 rows=28721 loops=1)
Filter: ((merge_requests.description ~~ '%@%'::text) OR ((merge_requests.title)::text ~~ '%@%'::text))
Rows Removed by Filter: 1744762
Buffers: shared hit=784636 read=948533 dirtied=1719
I/O Timings: read=639900.445
-- closed MRs that have a mention in description or title: ~137466
explain select id from merge_requests where state_id = 2 and (description like '%@%' OR title like '%@%')
Seq Scan on public.merge_requests (cost=0.00..9374745.27 rows=95172 width=4) (actual time=5.367..1342300.357 rows=137466 loops=1)
Filter: ((merge_requests.state_id = 2) AND ((merge_requests.description ~~ '%@%'::text) OR ((merge_requests.title)::text ~~ '%@%'::text)))
Rows Removed by Filter: 37035985
Buffers: shared hit=324423 read=2018843
I/O Timings: read=1306254.452
-- merged MRs that have a mention in description or title: ~766542
explain select id from merge_requests where state_id = 3 and (description like '%@%' OR title like '%@%')
Seq Scan on public.merge_requests (cost=0.00..9374745.27 rows=730504 width=4) (actual time=3.283..1323725.560 rows=766542 loops=1)
Filter: ((merge_requests.state_id = 3) AND ((merge_requests.description ~~ '%@%'::text) OR ((merge_requests.title)::text ~~ '%@%'::text)))
Rows Removed by Filter: 36406909
Buffers: shared hit=448923 read=2096514
I/O Timings: read=1276740.656
Edited by Alexandru Croitor