explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n2SG

Settings
# exclusive inclusive rows x rows loops node
1. 0.137 438.522 ↑ 1.0 1 1

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

  • Buffers: shared hit=18361
2.          

CTE promotion_notes

3. 0.473 3.984 ↑ 8.1 624 1

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

  • Buffers: shared hit=3190
4. 1.639 1.639 ↑ 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.639 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. 359.810 433.552 ↓ 2.9 586 1

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

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

  • Buffers: shared hit=12919
9. 52.014 56.259 ↓ 116.8 592,024 1

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

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

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

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

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

  • Buffers: shared hit=18361
12. 0.567 434.459 ↓ 2.8 561 1

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

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

CTE Scan on promoted_epics (cost=0.00..4.10 rows=205 width=4) (actual time=4.495..433.892 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