explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kFmP

Settings
# exclusive inclusive rows x rows loops node
1. 196.724 15,865.018 ↓ 0.0 0 1

ModifyTable on public.issues (cost=20,089.08..20,093.13 rows=1 width=838) (actual time=15,865.018..15,865.018 rows=0 loops=1)

  • Buffers: shared hit=519181 read=43941 dirtied=105
  • I/O Timings: read=7897.920
2.          

CTE promotion_notes

3. 0.054 15,551.899 ↑ 2.4 10 1

Nested Loop (cost=19,398.42..19,671.05 rows=24 width=116) (actual time=15,492.470..15,551.899 rows=10 loops=1)

  • Buffers: shared hit=513848 read=43781
  • I/O Timings: read=7617.624
4. 48.868 15,534.325 ↑ 2.4 10 1

Bitmap Heap Scan on public.notes (cost=19,397.98..19,563.95 rows=24 width=116) (actual time=15,485.503..15,534.325 rows=10 loops=1)

  • Filter: (notes.system AND ((notes.noteable_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=513814 read=43775
  • I/O Timings: read=7600.335
5. 3.830 15,485.457 ↓ 0.0 0 1

BitmapAnd (cost=19,397.98..19,397.98 rows=82 width=0) (actual time=15,485.457..15,485.457 rows=0 loops=1)

  • Buffers: shared hit=511985 read=43775
  • I/O Timings: read=7600.335
6. 12,215.610 12,215.610 ↑ 6.3 2,741 1

Bitmap Index Scan using index_notes_on_note_trigram (cost=0.00..1,228.96 rows=17,195 width=0) (actual time=12,215.610..12,215.610 rows=2,741 loops=1)

  • Index Cond: (notes.note ~~ 'promoted to epic%'::text)
  • Buffers: shared hit=511984 read=40036
  • I/O Timings: read=4930.111
7. 3,266.017 3,266.017 ↓ 1.0 1,167,297 1

Bitmap Index Scan using notes_pkey (cost=0.00..18,168.76 rows=1,140,819 width=0) (actual time=3,266.017..3,266.017 rows=1,167,297 loops=1)

  • Index Cond: ((notes.id >= 168890616) AND (notes.id <= 170219140))
  • Buffers: shared hit=1 read=3739
  • I/O Timings: read=2670.224
8. 17.520 17.520 ↑ 1.0 1 10

Index Scan using projects_pkey on public.projects (cost=0.43..4.45 rows=1 width=8) (actual time=1.751..1.752 rows=1 loops=10)

  • Index Cond: (projects.id = notes.project_id)
  • Buffers: shared hit=34 read=6
  • I/O Timings: read=17.289
9.          

CTE promoted_epics

10. 0.053 15,612.229 ↓ 10.0 10 1

Nested Loop (cost=0.29..417.60 rows=1 width=8) (actual time=15,523.371..15,612.229 rows=10 loops=1)

  • Buffers: shared hit=518516 read=43815
  • I/O Timings: read=7661.947
11. 15,551.936 15,551.936 ↑ 2.4 10 1

CTE Scan on promotion_notes (cost=0.00..0.48 rows=24 width=40) (actual time=15,492.474..15,551.936 rows=10 loops=1)

  • Buffers: shared hit=513848 read=43781
  • I/O Timings: read=7617.624
12. 60.240 60.240 ↑ 1.0 1 10

Index Scan using index_epics_on_group_id on public.epics (cost=0.29..17.37 rows=1 width=12) (actual time=4.373..6.024 rows=1 loops=10)

  • Index Cond: (epics.group_id = promotion_notes.epic_group_id)
  • Filter: (promotion_notes.promotion_note = concat('promoted to epic &', epics.iid))
  • Rows Removed by Filter: 572
  • Buffers: shared hit=4668 read=34
  • I/O Timings: read=44.323
13. 0.089 15,668.294 ↓ 9.0 9 1

Nested Loop (cost=0.44..4.48 rows=1 width=838) (actual time=15,529.651..15,668.294 rows=9 loops=1)

  • Buffers: shared hit=518535 read=43837 dirtied=1
  • I/O Timings: read=7717.431
14. 15,612.285 15,612.285 ↓ 10.0 10 1

CTE Scan on promoted_epics (cost=0.00..0.02 rows=1 width=40) (actual time=15,523.388..15,612.285 rows=10 loops=1)

  • Buffers: shared hit=518516 read=43815
  • I/O Timings: read=7661.947
15. 55.920 55.920 ↑ 1.0 1 10

Index Scan using issues_pkey on public.issues (cost=0.44..4.46 rows=1 width=778) (actual time=5.590..5.592 rows=1 loops=10)

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