explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tJxk

Settings
# exclusive inclusive rows x rows loops node
1. 15.324 5,414.803 ↓ 371.0 371 1

Aggregate (cost=33,154.53..33,154.58 rows=1 width=14) (actual time=5,400.850..5,414.803 rows=371 loops=1)

  • Group Key: merge_requests.id
  • Filter: (count(DISTINCT labels.title) = 2)
  • Rows Removed by Filter: 11,135
  • Buffers: shared hit=2,108,331 read=83,310
  • I/O Timings: read=2,570.788
2. 15.638 5,399.479 ↓ 5,938.5 11,877 1

Sort (cost=33,154.53..33,154.54 rows=2 width=15) (actual time=5,397.922..5,399.479 rows=11,877 loops=1)

  • Sort Key: merge_requests.id
  • Sort Method: quicksort Memory: 972kB
  • Buffers: shared hit=2,108,331 read=83,310
  • I/O Timings: read=2,570.788
3. 18.770 5,383.841 ↓ 5,938.5 11,877 1

Nested Loop Left Join (cost=1,420.63..33,154.52 rows=2 width=15) (actual time=2.981..5,383.841 rows=11,877 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=2,108,331 read=83,310
  • I/O Timings: read=2,570.788
4. 247.895 5,328.552 ↓ 5,938.5 11,877 1

Nested Loop (cost=1,420.20..33,146.33 rows=2 width=19) (actual time=2.951..5,328.552 rows=11,877 loops=1)

  • Buffers: shared hit=2,059,644 read=83,300
  • I/O Timings: read=2,569.849
5. 185.860 4,063.375 ↓ 345.7 339,094 1

Nested Loop (cost=1,419.76..32,668.40 rows=981 width=14) (actual time=1.971..4,063.375 rows=339,094 loops=1)

  • Buffers: shared hit=703,194 read=83,219
  • I/O Timings: read=2,562.647
6. 35.175 1,580.015 ↓ 19.8 114,875 1

Nested Loop (cost=1,419.20..29,083.40 rows=5,800 width=10) (actual time=1.822..1,580.015 rows=114,875 loops=1)

  • Buffers: shared hit=99,697 read=26,256
  • I/O Timings: read=1,218.301
7. 0.504 11.726 ↑ 1.7 979 1

Nested Loop (cost=1,418.63..16,399.75 rows=1,633 width=4) (actual time=1.775..11.726 rows=979 loops=1)

  • Buffers: shared hit=6,367
8. 0.190 1.847 ↑ 1.3 125 1

HashAggregate (cost=1,418.20..1,419.81 rows=161 width=4) (actual time=1.711..1.847 rows=125 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=627
9. 1.657 1.657 ↑ 1.3 125 1

CTE Scan on base_and_descendants namespaces (cost=1,412.97..1,416.19 rows=161 width=4) (actual time=0.038..1.657 rows=125 loops=1)

  • Buffers: shared hit=627
10.          

CTE base_and_descendants

11. 0.366 1.443 ↑ 1.3 125 1

Recursive Union (cost=0.43..1,412.97 rows=161 width=329) (actual time=0.032..1.443 rows=125 loops=1)

  • Buffers: shared hit=627
12. 0.027 0.027 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=329) (actual time=0.026..0.027 rows=1 loops=1)

  • Index Cond: (namespaces_1.id = 9,970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
13. 0.155 1.050 ↓ 1.6 25 5

Nested Loop (cost=0.56..140.63 rows=16 width=329) (actual time=0.020..0.210 rows=25 loops=5)

  • Buffers: shared hit=623
14. 0.020 0.020 ↓ 2.5 25 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.004 rows=25 loops=5)

15. 0.875 0.875 ↑ 2.0 1 125

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..14.02 rows=2 width=329) (actual time=0.005..0.007 rows=1 loops=125)

  • Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=623
16. 4.480 9.375 ↑ 1.2 8 125

Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..92.94 rows=10 width=8) (actual time=0.014..0.075 rows=8 loops=125)

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

SubPlan (for Index Scan)

18. 4.895 4.895 ↑ 1.0 1 979

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.005..0.005 rows=1 loops=979)

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 113
  • Buffers: shared hit=4,383
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_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
20. 1,533.114 1,533.114 ↑ 2.0 117 979

Index Scan using index_merge_requests_on_target_project_id_and_iid on public.merge_requests (cost=0.56..5.38 rows=239 width=10) (actual time=0.043..1.566 rows=117 loops=979)

  • Index Cond: (merge_requests.target_project_id = projects.id)
  • Buffers: shared hit=93,330 read=26,256
  • I/O Timings: read=1,218.301
21. 2,297.500 2,297.500 ↓ 3.0 3 114,875

Index Scan using index_label_links_on_target_id_and_target_type on public.label_links (cost=0.56..0.61 rows=1 width=8) (actual time=0.012..0.020 rows=3 loops=114,875)

  • Index Cond: ((label_links.target_id = merge_requests.id) AND ((label_links.target_type)::text = 'MergeRequest'::text))
  • Buffers: shared hit=603,497 read=56,963
  • I/O Timings: read=1,344.346
22. 1,017.282 1,017.282 ↓ 0.0 0 339,094

Index Scan using labels_pkey on public.labels (cost=0.43..0.49 rows=1 width=13) (actual time=0.003..0.003 rows=0 loops=339,094)

  • 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=1,356,450 read=81
  • I/O Timings: read=7.203
23. 35.631 35.631 ↑ 1.0 1 11,877

Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..0.49 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=11,877)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=47,498 read=10
  • I/O Timings: read=0.938
24.          

SubPlan (for Nested Loop Left Join)

25. 0.888 0.888 ↑ 1.0 1 296

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=296)

  • 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=1,189
26. 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