explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HcQG

Settings
# exclusive inclusive rows x rows loops node
1. 846.514 1,201.360 ↓ 0.0 0 1

ModifyTable on public.issues (cost=13,367.31..13,402.75 rows=8 width=838) (actual time=1,201.360..1,201.360 rows=0 loops=1)

  • Buffers: shared hit=44,769 read=772 dirtied=470
  • I/O Timings: read=1,084.896
2.          

CTE promotion_notes

3. 0.234 56.730 ↑ 3.5 54 1

Nested Loop (cost=0.71..11,073.79 rows=191 width=116) (actual time=7.167..56.730 rows=54 loops=1)

  • Buffers: shared hit=817 read=29 dirtied=3
  • I/O Timings: read=51.682
4. 3.954 3.954 ↑ 3.5 54 1

Index Scan using t2 on public.notes (cost=0.28..10,221.46 rows=191 width=116) (actual time=0.232..3.954 rows=54 loops=1)

  • Filter: ((notes.id >= 170,219,140) AND (notes.id <= 180,219,140))
  • Rows Removed by Filter: 570
  • Buffers: shared hit=628
5. 52.542 52.542 ↑ 1.0 1 54

Index Scan using projects_pkey on public.projects (cost=0.43..4.45 rows=1 width=8) (actual time=0.928..0.973 rows=1 loops=54)

  • Index Cond: (projects.id = notes.project_id)
  • Buffers: shared hit=189 read=29 dirtied=3
  • I/O Timings: read=51.682
6.          

CTE promoted_epics

7. 0.175 178.250 ↓ 6.2 50 1

Nested Loop (cost=0.29..2,293.07 rows=8 width=8) (actual time=9.129..178.250 rows=50 loops=1)

  • Buffers: shared hit=41,704 read=78 dirtied=3
  • I/O Timings: read=101.784
8. 56.845 56.845 ↑ 3.5 54 1

CTE Scan on promotion_notes (cost=0.00..3.82 rows=191 width=40) (actual time=7.169..56.845 rows=54 loops=1)

  • Buffers: shared hit=817 read=29 dirtied=3
  • I/O Timings: read=51.682
9. 121.230 121.230 ↑ 1.0 1 54

Index Scan using index_epics_on_group_id on public.epics (cost=0.29..11.98 rows=1 width=12) (actual time=1.041..2.245 rows=1 loops=54)

  • 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: 812
  • Buffers: shared hit=40,887 read=49
  • I/O Timings: read=50.102
10. 0.241 354.846 ↓ 6.1 49 1

Nested Loop (cost=0.44..35.88 rows=8 width=838) (actual time=12.948..354.846 rows=49 loops=1)

  • Buffers: shared hit=41,815 read=169 dirtied=5
  • I/O Timings: read=276.650
11. 178.405 178.405 ↓ 6.2 50 1

CTE Scan on promoted_epics (cost=0.00..0.16 rows=8 width=40) (actual time=9.136..178.405 rows=50 loops=1)

  • Buffers: shared hit=41,704 read=78 dirtied=3
  • I/O Timings: read=101.784
12. 176.200 176.200 ↑ 1.0 1 50

Index Scan using issues_pkey on public.issues (cost=0.44..4.46 rows=1 width=778) (actual time=3.522..3.524 rows=1 loops=50)

  • Index Cond: (issues.id = promoted_epics.issue_id)
  • Filter: (issues.promoted_to_epic_id IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=111 read=91 dirtied=2
  • I/O Timings: read=174.866