explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iiZT

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 873.525 ↑ 3.0 6 1

Limit (cost=130,721.45..130,721.50 rows=18 width=1,262) (actual time=873.523..873.525 rows=6 loops=1)

  • Buffers: shared hit=979,533
2. 0.030 873.522 ↑ 3.0 6 1

Sort (cost=130,721.45..130,721.50 rows=18 width=1,262) (actual time=873.521..873.522 rows=6 loops=1)

  • Sort Key: ((milestones.due_date IS NULL)), ((milestones.id IS NULL)), milestones.due_date DESC, issues.id DESC
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=979,533
3. 9.416 873.492 ↑ 3.0 6 1

Aggregate (cost=130,640.03..130,721.08 rows=18 width=1,262) (actual time=865.007..873.492 rows=6 loops=1)

  • Group Key: issues.id, milestones.id
  • Filter: (count(DISTINCT labels.title) = 3)
  • Rows Removed by Filter: 5,971
  • Buffers: shared hit=979,533
4. 17.328 864.076 ↓ 1.7 6,165 1

Sort (cost=130,640.03..130,649.04 rows=3,602 width=1,269) (actual time=862.659..864.076 rows=6,165 loops=1)

  • Sort Key: issues.id DESC, milestones.id
  • Sort Method: quicksort Memory: 10,499kB
  • Buffers: shared hit=979,533
5. 2.993 846.748 ↓ 1.7 6,165 1

Nested Loop Left Join (cost=1.99..130,427.25 rows=3,602 width=1,269) (actual time=0.190..846.748 rows=6,165 loops=1)

  • Buffers: shared hit=979,533
6. 129.485 831.425 ↓ 1.7 6,165 1

Nested Loop (cost=1.56..124,933.32 rows=3,602 width=1,261) (actual time=0.179..831.425 rows=6,165 loops=1)

  • Buffers: shared hit=964,993
7. 100.846 321.708 ↓ 5.7 190,116 1

Nested Loop (cost=1.13..109,462.77 rows=33,439 width=1,256) (actual time=0.053..321.708 rows=190,116 loops=1)

  • Buffers: shared hit=204,440
8. 66.997 66.997 ↑ 1.1 30,773 1

Index Scan using idx_issues_on_project_id_and_rel_position_and_state_id_and_id on public.issues (cost=0.56..40,576.70 rows=33,501 width=1,252) (actual time=0.036..66.997 rows=30,773 loops=1)

  • Index Cond: ((issues.project_id = 278,964) AND (issues.state_id = 1))
  • Buffers: shared hit=31,123
9. 153.865 153.865 ↓ 3.0 6 30,773

Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.56..2.04 rows=2 width=8) (actual time=0.004..0.005 rows=6 loops=30,773)

  • Index Cond: ((label_links.target_id = issues.id) AND (label_links.target_type = 'Issue'::text))
  • Heap Fetches: 1,453
  • Buffers: shared hit=173,317
10. 380.232 380.232 ↓ 0.0 0 190,116

Index Scan using labels_pkey on public.labels (cost=0.43..0.46 rows=1 width=13) (actual time=0.002..0.002 rows=0 loops=190,116)

  • Index Cond: (labels.id = label_links.label_id)
  • Filter: ((labels.title)::text = ANY ('{QA,Quality,bug}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=760,553
11. 12.330 12.330 ↑ 1.0 1 6,165

Index Scan using milestones_pkey on public.milestones (cost=0.42..1.53 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=6,165)

  • Index Cond: (issues.milestone_id = milestones.id)
  • Buffers: shared hit=14,540