explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J667

Settings
# exclusive inclusive rows x rows loops node
1. 11.051 24,871.376 ↓ 371.0 371 1

Aggregate (cost=3,296.40..3,296.43 rows=1 width=14) (actual time=24,862.155..24,871.376 rows=371 loops=1)

  • Group Key: merge_requests.id
  • Filter: (count(DISTINCT labels.title) = 2)
  • Rows Removed by Filter: 8,288
  • Buffers: shared hit=1,694,598 read=154,191
  • I/O Timings: read=21,252.298
2. 19.340 24,860.325 ↓ 9,030.0 9,030 1

Sort (cost=3,296.40..3,296.41 rows=1 width=15) (actual time=24,859.328..24,860.325 rows=9,030 loops=1)

  • Sort Key: merge_requests.id
  • Sort Method: quicksort Memory: 838kB
  • Buffers: shared hit=1,694,598 read=154,191
  • I/O Timings: read=21,252.298
3. 22.598 24,840.985 ↓ 9,030.0 9,030 1

Nested Loop Left Join (cost=2.44..3,296.39 rows=1 width=15) (actual time=56.087..24,840.985 rows=9,030 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=1,694,598 read=154,191
  • I/O Timings: read=21,252.298
4. 291.877 24,763.075 ↓ 9,030.0 9,030 1

Nested Loop (cost=2.00..3,289.33 rows=1 width=19) (actual time=56.059..24,763.075 rows=9,030 loops=1)

  • Buffers: shared hit=1,657,344 read=154,191
  • I/O Timings: read=21,252.298
5. 243.965 22,975.468 ↓ 49,857.7 299,146 1

Nested Loop (cost=1.56..3,286.41 rows=6 width=14) (actual time=15.604..22,975.468 rows=299,146 loops=1)

  • Buffers: shared hit=460,645 read=154,157
  • I/O Timings: read=21,224.260
6. 37.606 3,714.317 ↓ 2,379.5 85,663 1

Nested Loop (cost=1.00..3,264.16 rows=36 width=10) (actual time=0.526..3,714.317 rows=85,663 loops=1)

  • Buffers: shared hit=35,052 read=56,031
  • I/O Timings: read=3,334.119
7. 1.011 2.427 ↓ 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.034..2.427 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
8.          

SubPlan (for Index Scan)

9. 1.416 1.416 ↑ 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.006..0.006 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
10. 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
11. 3,674.284 3,674.284 ↓ 1.5 363 236

Index Scan using index_merge_requests_target_project_id_created_at on public.merge_requests (cost=0.56..315.23 rows=239 width=10) (actual time=0.024..15.569 rows=363 loops=236)

  • Index Cond: (merge_requests.target_project_id = projects.id)
  • Buffers: shared hit=33,791 read=56,031
  • I/O Timings: read=3,334.119
12. 19,017.186 19,017.186 ↓ 3.0 3 85,663

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.111..0.222 rows=3 loops=85,663)

  • Index Cond: ((label_links.target_id = merge_requests.id) AND ((label_links.target_type)::text = 'MergeRequest'::text))
  • Buffers: shared hit=425,593 read=98,126
  • I/O Timings: read=17,890.141
13. 1,495.730 1,495.730 ↓ 0.0 0 299,146

Index Scan using labels_pkey on public.labels (cost=0.43..0.49 rows=1 width=13) (actual time=0.005..0.005 rows=0 loops=299,146)

  • 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,196,699 read=34
  • I/O Timings: read=28.037
14. 54.180 54.180 ↑ 1.0 1 9,030

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.006..0.006 rows=1 loops=9,030)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=36,120
15.          

SubPlan (for Nested Loop Left Join)

16. 1.132 1.132 ↑ 1.0 1 283

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.004..0.004 rows=1 loops=283)

  • 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,134
17. 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