explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jcoh

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 54.690 ↓ 5.0 5 1

Sort (cost=235.32..235.33 rows=1 width=739) (actual time=54.689..54.690 rows=5 loops=1)

  • Sort Key: ((milestones.due_date IS NULL)), ((milestones.id IS NULL)), milestones.due_date DESC, merge_requests.id DESC
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=56,871
2. 0.607 54.641 ↓ 5.0 5 1

Aggregate (cost=235.29..235.31 rows=1 width=739) (actual time=54.217..54.641 rows=5 loops=1)

  • Group Key: merge_requests.id, milestones.id
  • Filter: (count(DISTINCT labels.title) = 2)
  • Rows Removed by Filter: 316
  • Buffers: shared hit=56,871
3. 0.484 54.034 ↓ 326.0 326 1

Sort (cost=235.29..235.29 rows=1 width=746) (actual time=53.974..54.034 rows=326 loops=1)

  • Sort Key: merge_requests.id DESC, milestones.id
  • Sort Method: quicksort Memory: 437kB
  • Buffers: shared hit=56,871
4. 0.238 53.550 ↓ 326.0 326 1

Nested Loop Left Join (cost=2.72..235.28 rows=1 width=746) (actual time=0.386..53.550 rows=326 loops=1)

  • Buffers: shared hit=56,871
5. 0.423 52.660 ↓ 326.0 326 1

Nested Loop Left Join (cost=2.30..234.81 rows=1 width=738) (actual time=0.382..52.660 rows=326 loops=1)

  • Filter: ((project_features.merge_requests_access_level IS NULL) OR (project_features.merge_requests_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.merge_requests_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=55,859
6. 7.340 51.549 ↓ 326.0 326 1

Nested Loop (cost=1.86..227.75 rows=1 width=742) (actual time=0.371..51.549 rows=326 loops=1)

  • Buffers: shared hit=54,506
7. 6.149 24.357 ↓ 9,926.0 9,926 1

Nested Loop (cost=1.43..227.27 rows=1 width=737) (actual time=0.071..24.357 rows=9,926 loops=1)

  • Buffers: shared hit=14,791
8. 0.946 7.132 ↓ 1,846.0 1,846 1

Nested Loop (cost=0.86..226.49 rows=1 width=733) (actual time=0.058..7.132 rows=1,846 loops=1)

  • Buffers: shared hit=4,096
9. 0.522 1.230 ↓ 23.6 236 1

Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..87.96 rows=10 width=4) (actual time=0.035..1.230 rows=236 loops=1)

  • Index Cond: (projects.namespace_id = 9,970)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1,261
10.          

SubPlan (for Index Scan)

11. 0.708 0.708 ↑ 1.0 1 236

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=236)

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 33
  • Buffers: shared hit=1,022
12. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_1 (cost=0.57..230.03 rows=1,014 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_1.user_id = 4,156,052) AND (project_authorizations_1.access_level >= 20))
  • Heap Fetches: 0
13. 4.956 4.956 ↑ 1.1 8 236

Index Scan using idx_merge_requests_on_target_project_id_and_iid_opened on public.merge_requests (cost=0.43..13.76 rows=9 width=729) (actual time=0.004..0.021 rows=8 loops=236)

  • Index Cond: (merge_requests.target_project_id = projects.id)
  • Buffers: shared hit=2,835
14. 11.076 11.076 ↓ 5.0 5 1,846

Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.56..0.77 rows=1 width=8) (actual time=0.005..0.006 rows=5 loops=1,846)

  • Index Cond: ((label_links.target_id = merge_requests.id) AND (label_links.target_type = 'MergeRequest'::text))
  • Heap Fetches: 237
  • Buffers: shared hit=10,695
15. 19.852 19.852 ↓ 0.0 0 9,926

Index Scan using labels_pkey on public.labels (cost=0.43..0.49 rows=1 width=13) (actual time=0.002..0.002 rows=0 loops=9,926)

  • Index Cond: (labels.id = label_links.label_id)
  • Filter: ((labels.title)::text = ANY ('{database::approved,feature}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=39,715
16. 0.652 0.652 ↑ 1.0 1 326

Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..3.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=326)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=1,304
17.          

SubPlan (for Nested Loop Left Join)

18. 0.036 0.036 ↑ 1.0 1 12

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=12)

  • Index Cond: ((project_authorizations_2.user_id = 4,156,052) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 20))
  • Heap Fetches: 0
  • Buffers: shared hit=49
19. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_3 (cost=0.57..230.03 rows=1,014 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_3.user_id = 4,156,052) AND (project_authorizations_3.access_level >= 20))
  • Heap Fetches: 0
20. 0.652 0.652 ↑ 1.0 1 326

Index Scan using milestones_pkey on public.milestones (cost=0.42..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=326)

  • Index Cond: (merge_requests.milestone_id = milestones.id)
  • Buffers: shared hit=1,012