explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OTOc

Settings
# exclusive inclusive rows x rows loops node
1. 0.143 420.243 ↑ 1.0 1 1

Aggregate (cost=90,982.85..90,982.86 rows=1 width=8) (actual time=420.243..420.243 rows=1 loops=1)

  • Buffers: shared hit=18361
2.          

CTE promotion_notes

3. 0.417 3.822 ↑ 8.1 624 1

Nested Loop (cost=0.71..56,707.70 rows=5,070 width=116) (actual time=0.029..3.822 rows=624 loops=1)

  • Buffers: shared hit=3190
4. 1.533 1.533 ↑ 8.1 624 1

Index Scan using t1 on public.notes (cost=0.28..34,572.82 rows=5,070 width=116) (actual time=0.013..1.533 rows=624 loops=1)

  • Filter: (notes.system AND ((notes.noteable_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 69
  • Buffers: shared hit=693
5. 1.872 1.872 ↑ 1.0 1 624

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

  • Index Cond: (projects.id = notes.project_id)
  • Buffers: shared hit=2497
6.          

CTE promoted_epics

7. 348.781 415.459 ↓ 2.9 586 1

Merge Join (cost=413.69..4,583.64 rows=205 width=8) (actual time=4.305..415.459 rows=586 loops=1)

  • Buffers: shared hit=16114
8. 15.366 15.366 ↑ 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=0.009..15.366 rows=15,880 loops=1)

  • Buffers: shared hit=12919
9. 47.232 51.312 ↓ 116.8 592,024 1

Sort (cost=413.40..426.08 rows=5,070 width=40) (actual time=4.280..51.312 rows=592,024 loops=1)

  • Sort Key: promotion_notes.epic_group_id
  • Sort Method: quicksort Memory: 74kB
  • Buffers: shared hit=3195
10. 4.080 4.080 ↑ 8.1 624 1

CTE Scan on promotion_notes (cost=0.00..101.40 rows=5,070 width=40) (actual time=0.032..4.080 rows=624 loops=1)

  • Buffers: shared hit=3190
11. 0.000 420.100 ↑ 28,021.3 411 1

Nested Loop (cost=5.05..899.61 rows=11,516,760 width=4) (actual time=416.126..420.100 rows=411 loops=1)

  • Buffers: shared hit=18361
12. 0.475 416.219 ↓ 2.8 561 1

HashAggregate (cost=4.61..6.61 rows=200 width=4) (actual time=416.098..416.219 rows=561 loops=1)

  • Group Key: promoted_epics.issue_id
  • Buffers: shared hit=16114
13. 415.744 415.744 ↓ 2.9 586 1

CTE Scan on promoted_epics (cost=0.00..4.10 rows=205 width=4) (actual time=4.307..415.744 rows=586 loops=1)

  • Buffers: shared hit=16114
14. 3.927 3.927 ↑ 1.0 1 561

Index Scan using issues_pkey on public.issues (cost=0.44..4.46 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=561)

  • Index Cond: (issues.id = promoted_epics.issue_id)
  • Filter: (issues.promoted_to_epic_id IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2247