explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jU8s

Settings
# exclusive inclusive rows x rows loops node
1. 15.483 227.971 ↑ 1.0 4 1

Aggregate (cost=3,794,124.01..3,795,206.97 rows=4 width=10) (actual time=205.026..227.971 rows=4 loops=1)

  • Group Key: merge_requests.state_id
  • Buffers: shared hit=93,687
2.          

Initplan (for Aggregate)

3. 0.001 0.013 ↑ 1.0 1 1

Limit (cost=0.43..3.45 rows=1 width=25) (actual time=0.013..0.013 rows=1 loops=1)

  • Buffers: shared hit=4
4. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: (namespaces_1.id = 9,970)
  • Buffers: shared hit=4
5. 30.483 212.475 ↑ 1.6 88,347 1

Sort (cost=3,794,120.56..3,794,481.53 rows=144,389 width=2) (actual time=204.298..212.475 rows=88,347 loops=1)

  • Sort Key: merge_requests.state_id
  • Sort Method: quicksort Memory: 7,214kB
  • Buffers: shared hit=93,687
6. 16.057 181.992 ↑ 1.6 88,347 1

Nested Loop (cost=333.51..3,781,746.71 rows=144,389 width=2) (actual time=0.127..181.992 rows=88,347 loops=1)

  • Buffers: shared hit=93,687
7. 1.171 5.591 ↑ 191.7 204 1

Nested Loop Left Join (cost=332.95..3,464,742.23 rows=39,112 width=4) (actual time=0.114..5.591 rows=204 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: 7
  • Buffers: shared hit=3,926
8. 0.050 1.859 ↑ 193.3 211 1

Nested Loop (cost=332.51..3,297,544.60 rows=40,785 width=4) (actual time=0.105..1.859 rows=211 loops=1)

  • Buffers: shared hit=1,365
9. 0.322 0.376 ↑ 38,978.0 1 1

Bitmap Heap Scan on public.namespaces (cost=332.08..88,022.24 rows=38,978 width=4) (actual time=0.072..0.376 rows=1 loops=1)

  • Buffers: shared hit=147
10. 0.054 0.054 ↑ 254.8 153 1

Bitmap Index Scan using index_namespaces_on_traversal_ids (cost=0.00..322.33 rows=38,978 width=0) (actual time=0.054..0.054 rows=153 loops=1)

  • Index Cond: (namespaces.traversal_ids <@ $4)
  • Buffers: shared hit=12
11. 0.589 1.433 ↓ 21.1 211 1

Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..82.24 rows=10 width=8) (actual time=0.031..1.433 rows=211 loops=1)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Filter: ((NOT projects.archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[]))))
  • Rows Removed by Filter: 25
  • Buffers: shared hit=1,218
12.          

SubPlan (for Index Scan)

13. 0.844 0.844 ↑ 1.0 1 211

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

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 89
  • Buffers: shared hit=978
14. 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..111.99 rows=1,248 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
15. 1.055 1.055 ↑ 1.0 1 211

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

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=844
16.          

SubPlan (for Nested Loop Left Join)

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_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • 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
18. 1.506 1.506 ↓ 2.6 3,307 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_3 (cost=0.57..111.99 rows=1,248 width=4) (actual time=0.016..1.506 rows=3,307 loops=1)

  • Index Cond: ((project_authorizations_3.user_id = 4,156,052) AND (project_authorizations_3.access_level >= 20))
  • Heap Fetches: 861
  • Buffers: shared hit=1,717
19. 160.344 160.344 ↓ 1.7 433 204

Index Scan using index_merge_requests_on_target_project_id_and_iid on public.merge_requests (cost=0.56..5.61 rows=250 width=6) (actual time=0.007..0.786 rows=433 loops=204)

  • Index Cond: (merge_requests.target_project_id = projects.id)
  • Buffers: shared hit=89,761