explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eVp6

Settings
# exclusive inclusive rows x rows loops node
1. 3.429 523.013 ↓ 9,745.0 9,745 1

Unique (cost=4,878.23..4,878.24 rows=1 width=4) (actual time=517.966..523.013 rows=9,745 loops=1)

  • Buffers: shared hit=263,550
2. 9.580 519.584 ↓ 9,745.0 9,745 1

Sort (cost=4,878.23..4,878.23 rows=1 width=4) (actual time=517.963..519.584 rows=9,745 loops=1)

  • Sort Key: issues.id
  • Sort Method: quicksort Memory: 841kB
  • Buffers: shared hit=263,550
3. 12.257 510.004 ↓ 9,745.0 9,745 1

Nested Loop Left Join (cost=3,348.53..4,878.22 rows=1 width=4) (actual time=72.066..510.004 rows=9,745 loops=1)

  • Filter: ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=263,550
4. 7.444 458.697 ↓ 9,745.0 9,745 1

Hash Semi Join (cost=3,348.10..4,874.12 rows=1 width=8) (actual time=72.018..458.697 rows=9,745 loops=1)

  • Hash Cond: (projects.namespace_id = namespaces_3.id)
  • Buffers: shared hit=224,513
5. 8.686 448.831 ↓ 226.8 9,754 1

Nested Loop (cost=1,914.49..3,440.40 rows=43 width=12) (actual time=69.571..448.831 rows=9,754 loops=1)

  • Buffers: shared hit=223,741
6. 11.196 342.585 ↓ 126.7 9,756 1

Nested Loop (cost=1,914.06..3,126.92 rows=77 width=8) (actual time=69.523..342.585 rows=9,756 loops=1)

  • Buffers: shared hit=142,774
7. 22.559 81.357 ↓ 176.6 20,836 1

HashAggregate (cost=1,913.49..1,914.67 rows=118 width=4) (actual time=69.492..81.357 rows=20,836 loops=1)

  • Group Key: epic_issues.issue_id
  • Buffers: shared hit=35,538
8. 5.169 58.798 ↓ 176.6 20,836 1

Nested Loop (cost=1,814.45..1,913.20 rows=118 width=4) (actual time=17.538..58.798 rows=20,836 loops=1)

  • Buffers: shared hit=35,538
9. 3.860 19.261 ↓ 226.1 4,296 1

HashAggregate (cost=1,814.03..1,814.22 rows=19 width=4) (actual time=17.515..19.261 rows=4,296 loops=1)

  • Group Key: epics.id
  • Buffers: shared hit=5,122
10. 2.351 15.401 ↓ 226.1 4,296 1

Sort (cost=1,813.74..1,813.79 rows=19 width=4) (actual time=14.843..15.401 rows=4,296 loops=1)

  • Sort Key: epics.id DESC
  • Sort Method: quicksort Memory: 394kB
  • Buffers: shared hit=5,122
11. 0.976 13.050 ↓ 226.1 4,296 1

Nested Loop (cost=1,782.61..1,813.34 rows=19 width=4) (actual time=2.596..13.050 rows=4,296 loops=1)

  • Buffers: shared hit=5,122
12. 0.070 2.680 ↓ 77.0 154 1

Unique (cost=1,782.32..1,782.33 rows=2 width=4) (actual time=2.581..2.680 rows=154 loops=1)

  • Buffers: shared hit=776
13. 0.076 2.610 ↓ 77.0 154 1

Sort (cost=1,782.32..1,782.33 rows=2 width=4) (actual time=2.581..2.610 rows=154 loops=1)

  • Sort Key: namespaces.id
  • Sort Method: quicksort Memory: 32kB
  • Buffers: shared hit=776
14. 0.032 2.534 ↓ 77.0 154 1

Subquery Scan on namespaces (cost=1,782.02..1,782.29 rows=2 width=4) (actual time=2.267..2.534 rows=154 loops=1)

  • Buffers: shared hit=776
15.          

CTE base_and_ancestors

16. 0.007 0.025 ↑ 21.0 1 1

Recursive Union (cost=0.43..351.12 rows=21 width=345) (actual time=0.019..0.025 rows=1 loops=1)

  • Buffers: shared hit=4
17. 0.013 0.013 ↑ 1.0 1 1

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

  • Index Cond: (namespaces_6.id = 9,970)
  • Filter: ((namespaces_6.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
18. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.43..34.73 rows=2 width=345) (actual time=0.005..0.005 rows=0 loops=1)

19. 0.002 0.002 ↑ 10.0 1 1

WorkTable Scan on base_and_ancestors (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.002 rows=1 loops=1)

20. 0.002 0.002 ↓ 0.0 0 1

Index Scan using namespaces_pkey on public.namespaces namespaces_7 (cost=0.43..3.45 rows=1 width=345) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (namespaces_7.id = base_and_ancestors.parent_id)
  • Filter: ((namespaces_7.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
21.          

CTE base_and_descendants

22. 0.582 1.651 ↑ 1.0 154 1

Recursive Union (cost=0.43..1,426.77 rows=161 width=345) (actual time=0.013..1.651 rows=154 loops=1)

  • Buffers: shared hit=772
23. 0.009 0.009 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_8 (cost=0.43..3.45 rows=1 width=345) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (namespaces_8.id = 9,970)
  • Filter: ((namespaces_8.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
24. 0.106 1.060 ↓ 1.9 31 5

Nested Loop (cost=0.56..142.01 rows=16 width=345) (actual time=0.021..0.212 rows=31 loops=5)

  • Buffers: shared hit=768
25. 0.030 0.030 ↓ 3.1 31 5

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

26. 0.924 0.924 ↑ 2.0 1 154

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_9 (cost=0.56..14.16 rows=2 width=345) (actual time=0.004..0.006 rows=1 loops=154)

  • Index Cond: (namespaces_9.parent_id = base_and_descendants_1.id)
  • Filter: ((namespaces_9.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=768
27. 0.220 2.502 ↓ 77.0 154 1

Unique (cost=4.13..4.38 rows=2 width=2,946) (actual time=2.267..2.502 rows=154 loops=1)

  • Buffers: shared hit=776
28. 0.246 2.282 ↓ 77.5 155 1

Sort (cost=4.13..4.14 rows=2 width=2,946) (actual time=2.266..2.282 rows=155 loops=1)

  • Sort Key: namespaces_1.id, namespaces_1.name, namespaces_1.path, namespaces_1.owner_id, namespaces_1.created_at, namespaces_1.updated_at, namespaces_1.type, namespaces_1.description, namespaces_1.avatar, namespaces_1.membership_lock, namespaces_1.share_with_group_lock, namespaces_1.visibility_level, namespaces_1.request_access_enabled, namespaces_1.ldap_sync_status, namespaces_1.ldap_sync_error, namespaces_1.ldap_sync_last_update_at, namespaces_1.ldap_sync_last_successful_update_at, namespaces_1.ldap_sync_last_sync_at, namespaces_1.lfs_enabled, namespaces_1.description_html, namespaces_1.parent_id, namespaces_1.shared_runners_minutes_limit, namespaces_1.repository_size_limit, namespaces_1.require_two_factor_authentication, namespaces_1.two_factor_grace_period, namespaces_1.cached_markdown_version, namespaces_1.project_creation_level, namespaces_1.runners_token, namespaces_1.file_template_project_id, namespaces_1.saml_discovery_token, namespaces_1.runners_token_encrypted, namespaces_1.custom_project_templates_group_id, namespaces_1.auto_devops_enabled, namespaces_1.extra_shared_runners_minutes_limit, namespaces_1.last_ci_minutes_notification_at, namespaces_1.last_ci_minutes_usage_notification_level, namespaces_1.subgroup_creation_level, namespaces_1.emails_disabled, namespaces_1.max_pages_size, namespaces_1.max_artifacts_size, namespaces_1.mentions_disabled, namespaces_1.default_branch_protection, namespaces_1.unlock_membership_to_ldap, namespaces_1.max_personal_access_token_lifetime, namespaces_1.push_rule_id, namespaces_1.shared_runners_enabled, namespaces_1.allow_descendants_override_disabled_shared_runners, namespaces_1.traversal_ids, namespaces_1.delayed_project_removal
  • Sort Method: quicksort Memory: 97kB
  • Buffers: shared hit=776
29. 0.041 2.036 ↓ 77.5 155 1

Append (cost=0.00..4.12 rows=2 width=2,946) (actual time=0.024..2.036 rows=155 loops=1)

  • Buffers: shared hit=776
30. 0.029 0.029 ↑ 1.0 1 1

CTE Scan on base_and_ancestors namespaces_1 (cost=0.00..0.47 rows=1 width=2,946) (actual time=0.023..0.029 rows=1 loops=1)

  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
31. 1.966 1.966 ↓ 154.0 154 1

CTE Scan on base_and_descendants namespaces_2 (cost=0.00..3.62 rows=1 width=2,946) (actual time=0.016..1.966 rows=154 loops=1)

  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=772
32. 9.394 9.394 ↓ 3.1 28 154

Index Scan using index_epics_on_group_id_and_iid_varchar_pattern on public.epics (cost=0.29..15.41 rows=9 width=8) (actual time=0.005..0.061 rows=28 loops=154)

  • Index Cond: (epics.group_id = namespaces.id)
  • Buffers: shared hit=4,346
33. 34.368 34.368 ↑ 1.2 5 4,296

Index Scan using index_epic_issues_on_epic_id on public.epic_issues (cost=0.42..5.15 rows=6 width=8) (actual time=0.004..0.008 rows=5 loops=4,296)

  • Index Cond: (epic_issues.epic_id = epics.id)
  • Buffers: shared hit=30,416
34. 244.600 250.032 ↓ 0.0 0 20,836

Index Scan using issues_pkey on public.issues (cost=0.56..10.27 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=20,836)

  • Index Cond: (issues.id = epic_issues.issue_id)
  • Filter: ((issues.state_id = 1) AND ((issues.confidential IS NOT TRUE) OR (issues.confidential AND ((issues.author_id = 1,642,716) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=107,236
35.          

SubPlan (for Index Scan)

36. 3.492 3.492 ↓ 0.0 0 388

Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on public.issue_assignees (cost=0.43..3.45 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=388)

  • Index Cond: ((issue_assignees.issue_id = issues.id) AND (issue_assignees.user_id = 1,642,716))
  • Heap Fetches: 0
  • Buffers: shared hit=1,164
37. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_issue_assignees_on_user_id on public.issue_assignees issue_assignees_1 (cost=0.43..84.90 rows=85 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (issue_assignees_1.user_id = 1,642,716)
38. 1.940 1.940 ↑ 1.0 1 388

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

  • Index Cond: ((project_authorizations.user_id = 1,642,716) AND (project_authorizations.project_id = issues.project_id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 29
  • Buffers: shared hit=1,814
39. 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..310.93 rows=1,312 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_1.user_id = 1,642,716) AND (project_authorizations_1.access_level >= 20))
  • Heap Fetches: 0
40. 58.544 97.560 ↑ 1.0 1 9,756

Index Scan using projects_pkey on public.projects (cost=0.43..4.07 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=9,756)

  • Index Cond: (projects.id = issues.project_id)
  • Filter: ((NOT projects.archived) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (projects.visibility_level = ANY ('{10,20}'::integer[]))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=80,967
41.          

SubPlan (for Index Scan)

42. 39.016 39.016 ↑ 1.0 1 9,754

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=9,754)

  • Index Cond: ((project_authorizations_2.user_id = 1,642,716) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
  • Heap Fetches: 178
  • Buffers: shared hit=41,943
43. 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..367.13 rows=1,579 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_3.user_id = 1,642,716) AND (project_authorizations_3.access_level >= 10))
  • Heap Fetches: 0
44. 0.064 2.422 ↑ 1.0 154 1

Hash (cost=1,431.60..1,431.60 rows=161 width=4) (actual time=2.421..2.422 rows=154 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=772
45. 2.358 2.358 ↑ 1.0 154 1

CTE Scan on base_and_descendants namespaces_3 (cost=1,426.77..1,429.99 rows=161 width=4) (actual time=0.030..2.358 rows=154 loops=1)

  • Buffers: shared hit=772
46.          

CTE base_and_descendants

47. 0.605 2.070 ↑ 1.0 154 1

Recursive Union (cost=0.43..1,426.77 rows=161 width=345) (actual time=0.027..2.070 rows=154 loops=1)

  • Buffers: shared hit=772
48. 0.020 0.020 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_4 (cost=0.43..3.45 rows=1 width=345) (actual time=0.019..0.020 rows=1 loops=1)

  • Index Cond: (namespaces_4.id = 9,970)
  • Filter: ((namespaces_4.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
49. 0.183 1.445 ↓ 1.9 31 5

Nested Loop (cost=0.56..142.01 rows=16 width=345) (actual time=0.029..0.289 rows=31 loops=5)

  • Buffers: shared hit=768
50. 0.030 0.030 ↓ 3.1 31 5

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

51. 1.232 1.232 ↑ 2.0 1 154

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_5 (cost=0.56..14.16 rows=2 width=345) (actual time=0.006..0.008 rows=1 loops=154)

  • Index Cond: (namespaces_5.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_5.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=768
52. 38.980 38.980 ↑ 1.0 1 9,745

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

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=38,980
53.          

SubPlan (for Nested Loop Left Join)

54. 0.070 0.070 ↑ 1.0 1 14

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

  • Index Cond: ((project_authorizations_4.user_id = 1,642,716) AND (project_authorizations_4.project_id = projects.id) AND (project_authorizations_4.access_level >= 10))
  • Heap Fetches: 0
  • Buffers: shared hit=57
55. 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_5 (cost=0.57..367.13 rows=1,579 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_5.user_id = 1,642,716) AND (project_authorizations_5.access_level >= 10))
  • Heap Fetches: 0