explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uS1Y

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 17.988 ↑ 1.0 20 1

Limit (cost=3,375,202.57..3,375,202.62 rows=20 width=725) (actual time=17.982..17.988 rows=20 loops=1)

  • Buffers: shared hit=12,948
2.          

Initplan (for Limit)

3. 0.001 0.014 ↑ 1.0 1 1

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

  • Buffers: shared hit=4
4. 0.013 0.013 ↑ 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.013..0.013 rows=1 loops=1)

  • Index Cond: (namespaces_1.id = 9,970)
  • Buffers: shared hit=4
5. 1.881 17.983 ↑ 276.2 20 1

Sort (cost=3,375,199.12..3,375,212.93 rows=5,524 width=725) (actual time=17.981..17.983 rows=20 loops=1)

  • Sort Key: merge_requests.updated_at DESC, merge_requests.id DESC
  • Sort Method: top-N heapsort Memory: 63kB
  • Buffers: shared hit=12,948
6. 2.760 16.102 ↑ 3.1 1,792 1

Nested Loop Left Join (cost=333.38..3,375,052.13 rows=5,524 width=725) (actual time=0.145..16.102 rows=1,792 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: 1
  • Buffers: shared hit=12,948
7. 1.084 8.071 ↑ 3.2 1,793 1

Nested Loop (cost=332.94..3,351,434.98 rows=5,761 width=729) (actual time=0.133..8.071 rows=1,793 loops=1)

  • Buffers: shared hit=4,059
8. 0.050 1.712 ↑ 193.3 211 1

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

  • Buffers: shared hit=1,365
9. 0.309 0.365 ↑ 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.074..0.365 rows=1 loops=1)

  • Buffers: shared hit=147
10. 0.056 0.056 ↑ 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.055..0.056 rows=153 loops=1)

  • Index Cond: (namespaces.traversal_ids <@ $4)
  • Buffers: shared hit=12
11. 0.664 1.297 ↓ 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.297 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.633 0.633 ↑ 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.003..0.003 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. 5.275 5.275 ↑ 1.2 8 211

Index Scan using idx_merge_requests_on_target_project_id_and_iid_opened on public.merge_requests (cost=0.43..1.22 rows=10 width=725) (actual time=0.005..0.025 rows=8 loops=211)

  • Index Cond: (merge_requests.target_project_id = projects.id)
  • Buffers: shared hit=2,694
16. 3.586 3.586 ↑ 1.0 1 1,793

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.002..0.002 rows=1 loops=1,793)

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

SubPlan (for Nested Loop Left Join)

18. 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
19. 1.685 1.685 ↓ 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.021..1.685 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