explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Htt6

Settings
# exclusive inclusive rows x rows loops node
1. 56,459.874 292,566.700 ↓ 0.0 0 1

ModifyTable on public.issues (cost=63,627.17..64,546.15 rows=205 width=838) (actual time=292,566.700..292,566.700 rows=0 loops=1)

  • Buffers: shared hit=493111 read=289200 dirtied=52695 written=41234
  • I/O Timings: read=272634.584 write=6645.603
2.          

CTE promotion_notes

3. 2.693 232,588.288 ↑ 8.1 624 1

Nested Loop (cost=1,230.76..59,042.10 rows=5,074 width=116) (actual time=207,042.763..232,588.288 rows=624 loops=1)

  • Buffers: shared hit=370874 read=212182 dirtied=271 written=41234
  • I/O Timings: read=214897.555 write=6645.603
4. 25,536.279 232,219.307 ↑ 8.1 624 1

Bitmap Heap Scan on public.notes (cost=1,230.33..36,889.37 rows=5,074 width=116) (actual time=207,039.199..232,219.307 rows=624 loops=1)

  • Filter: (notes.system AND ((notes.noteable_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 69
  • Buffers: shared hit=368708 read=211835 dirtied=253 written=41234
  • I/O Timings: read=214543.803 write=6645.603
5. 206,683.028 206,683.028 ↑ 6.3 2,741 1

Bitmap Index Scan using index_notes_on_note_trigram (cost=0.00..1,229.06 rows=17,208 width=0) (actual time=206,683.028..206,683.028 rows=2,741 loops=1)

  • Index Cond: (notes.note ~~ 'promoted to epic%'::text)
  • Buffers: shared hit=366368 read=185652 written=40761
  • I/O Timings: read=189987.190 write=6616.549
6. 366.288 366.288 ↑ 1.0 1 624

Index Scan using projects_pkey on public.projects (cost=0.43..4.36 rows=1 width=8) (actual time=0.574..0.587 rows=1 loops=624)

  • Index Cond: (projects.id = notes.project_id)
  • Buffers: shared hit=2166 read=347 dirtied=18
  • I/O Timings: read=353.752
7.          

CTE promoted_epics

8. 422.602 234,549.691 ↓ 2.9 586 1

Merge Join (cost=414.04..4,584.63 rows=205 width=8) (actual time=232,597.501..234,549.691 rows=586 loops=1)

  • Buffers: shared hit=382275 read=213708 dirtied=304 written=41234
  • I/O Timings: read=216332.847 write=6645.603
9. 1,476.251 1,476.251 ↑ 1.0 15,880 1

Index Scan using index_epics_on_group_id on public.epics (cost=0.29..3,308.01 rows=16,346 width=12) (actual time=6.202..1,476.251 rows=15,880 loops=1)

  • Buffers: shared hit=11396 read=1526 dirtied=33
  • I/O Timings: read=1435.292
10. 60.678 232,650.838 ↓ 116.7 592,024 1

Sort (cost=413.76..426.44 rows=5,074 width=40) (actual time=232,591.261..232,650.838 rows=592,024 loops=1)

  • Sort Key: promotion_notes.epic_group_id
  • Sort Method: quicksort Memory: 74kB
  • Buffers: shared hit=370879 read=212182 dirtied=271 written=41234
  • I/O Timings: read=214897.555 write=6645.603
11. 232,590.160 232,590.160 ↑ 8.1 624 1

CTE Scan on promotion_notes (cost=0.00..101.48 rows=5,074 width=40) (actual time=207,042.770..232,590.160 rows=624 loops=1)

  • Buffers: shared hit=370874 read=212182 dirtied=271 written=41234
  • I/O Timings: read=214897.555 write=6645.603
12. 3.265 236,106.826 ↓ 2.1 435 1

Nested Loop (cost=0.44..919.42 rows=205 width=838) (actual time=232,619.834..236,106.826 rows=435 loops=1)

  • Buffers: shared hit=383487 read=214869 dirtied=326 written=41234
  • I/O Timings: read=217867.934 write=6645.603
13. 234,551.833 234,551.833 ↓ 2.9 586 1

CTE Scan on promoted_epics (cost=0.00..4.10 rows=205 width=40) (actual time=232,597.569..234,551.833 rows=586 loops=1)

  • Buffers: shared hit=382275 read=213708 dirtied=304 written=41234
  • I/O Timings: read=216332.847 write=6645.603
14. 1,551.728 1,551.728 ↑ 1.0 1 586

Index Scan using issues_pkey on public.issues (cost=0.44..4.46 rows=1 width=778) (actual time=2.644..2.648 rows=1 loops=586)

  • Index Cond: (issues.id = promoted_epics.issue_id)
  • Filter: (issues.promoted_to_epic_id IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1212 read=1161 dirtied=22
  • I/O Timings: read=1535.087