Migrate mentions in Issue notes
Number of rows affected
- Notes for all issues with with mentions ~14720459
- Notes for open issues with mentions ~2593794
-- issue notes that have at least one mention ~14720459
explain select * from notes where noteable_type = 'Issue' and note like '%\@%'
Seq Scan on public.notes (cost=0.00..81296854.08 rows=8761665 width=4) (actual time=3.154..1747987.120 rows=14720459 loops=1)
Filter: ((notes.note ~~ '%\@%'::text) AND ((notes.noteable_type)::text = 'Issue'::text))
Rows Removed by Filter: 214948219
Buffers: shared hit=1109341 read=19928387 dirtied=175572 written=133906
I/O Timings: read=1572330.794 write=5860.768
-- issue notes on open issues that have at least one mention ~2593794
explain select * from notes, issues where issues.id = notes.noteable_id AND issues.state_id = 1 AND noteable_type = 'Issue' and note like '%\@%'
Hash Join (cost=1100509.75..82904773.06 rows=2607649 width=4) (actual time=132545.396..1893138.418 rows=2593794 loops=1)
Hash Cond: (notes.noteable_id = issues.id)
Buffers: shared hit=5617956 read=20090453 dirtied=8220
I/O Timings: read=1695538.239
-> Seq Scan on public.notes (cost=0.00..81296854.08 rows=8761665 width=8) (actual time=2.256..1740585.661 rows=14720459 loops=1)
Filter: ((notes.note ~~ '%\@%'::text) AND ((notes.noteable_type)::text = 'Issue'::text))
Rows Removed by Filter: 214948219
Buffers: shared hit=1126027 read=19911701
I/O Timings: read=1571403.562
-> Hash (cost=904389.78..904389.78 rows=6973118 width=4) (actual time=132501.250..132501.250 rows=6943477 loops=1)
Buckets: 4194304 Batches: 8 Memory Usage: 63242kB
Buffers: shared hit=4491926 read=178752 dirtied=8220
I/O Timings: read=124134.677
-> Index Only Scan using idx_issues_on_project_id_and_rel_position_and_state_id_and_id on public.issues (cost=0.56..904389.78 rows=6973118 width=4) (actual time=7.380..129733.262 rows=6943477 loops=1)
Index Cond: (issues.state_id = 1)
Heap Fetches: 304825
Buffers: shared hit=4491926 read=178752 dirtied=8220
I/O Timings: read=124134.677
Edited by Alexandru Croitor