explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EGe

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 40.894 ↑ 1.0 20 1

Limit (cost=90,285.77..90,285.82 rows=20 width=74) (actual time=40.877..40.894 rows=20 loops=1)

  • Buffers: shared hit=13994
2. 1.501 40.879 ↑ 1,965.3 20 1

Sort (cost=90,285.77..90,384.03 rows=39,307 width=74) (actual time=40.876..40.879 rows=20 loops=1)

  • Sort Key: events.id DESC
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=13994
3. 6.150 39.378 ↑ 4.9 7,974 1

HashAggregate (cost=88,453.68..88,846.75 rows=39,307 width=74) (actual time=37.748..39.378 rows=7,974 loops=1)

  • Group Key: events.id, events.project_id, events.author_id, events.target_id, events.created_at, events.updated_at, events.action, events.target_type, events.group_id
  • Buffers: shared hit=13991
4. 0.701 33.228 ↑ 4.9 7,974 1

Append (cost=16,382.18..87,569.27 rows=39,307 width=74) (actual time=8.379..33.228 rows=7,974 loops=1)

  • Buffers: shared hit=13991
5. 2.056 30.906 ↑ 5.0 7,920 1

Nested Loop (cost=16,382.18..78,103.13 rows=39,240 width=51) (actual time=8.379..30.906 rows=7,920 loops=1)

  • Buffers: shared hit=13235
6. 2.451 8.914 ↑ 3.1 623 1

HashAggregate (cost=16,381.60..16,401.22 rows=1,962 width=3,665) (actual time=8.362..8.914 rows=623 loops=1)

  • Group Key: projects.id, projects.name, projects.path, projects.description, projects.created_at, projects.updated_at, projects.creator_id, projects.namespace_id, projects.last_activity_at, projects.import_url, projects.visibility_level, projects.archived, projects.merge_requests_template, projects.star_count, projects.merge_requests_rebase_enabled, projects.import_type, projects.import_source, projects.avatar, projects.approvals_before_merge, projects.reset_approvals_on_push, projects.merge_requests_ff_only_enabled, projects.issues_template, projects.mirror, projects.mirror_last_update_at, projects.mirror_last_successful_update_at, projects.mirror_user_id, projects.shared_runners_enabled, projects.runners_token, projects.build_coverage_regex, projects.build_allow_git_fetch, projects.build_timeout, projects.mirror_trigger_builds, projects.public_builds, projects.pending_delete, projects.last_repository_check_failed, projects.last_repository_check_at, projects.container_registry_enabled, projects.only_allow_merge_if_pipeline_succeeds, projects.has_external_issue_tracker, projects.repository_storage, projects.request_access_enabled, projects.has_external_wiki, projects.repository_read_only, projects.lfs_enabled, projects.description_html, projects.only_allow_merge_if_all_discussions_are_resolved, projects.repository_size_limit, projects.service_desk_enabled, projects.printing_merge_request_link_enabled, projects.auto_cancel_pending_pipelines, projects.cached_markdown_version, projects.last_repository_updated_at, projects.ci_config_path, projects.disable_overriding_approvers_per_merge_request, projects.delete_error, projects.storage_version, projects.resolve_outdated_diff_discussions, projects.remote_mirror_available_overridden, projects.only_mirror_protected_branches, projects.pull_mirror_available_overridden, projects.jobs_cache_index, projects.external_authorization_classification_label, projects.mirror_overwrites_diverged_branches, projects.external_webhook_token, projects.pages_https_only, projects.packages_enabled, projects.merge_requests_author_approval, projects.pool_repository_id, projects.runners_token_encrypted, projects.bfg_object_map, projects.merge_requests_require_code_owner_approval, projects.detected_repository_languages, projects.merge_requests_disable_committers_approval, projects.require_password_to_approve, projects.emails_disabled
  • Buffers: shared hit=2583
7. 0.075 6.463 ↑ 3.1 624 1

Append (cost=1,664.54..16,013.73 rows=1,962 width=3,665) (actual time=3.303..6.463 rows=624 loops=1)

  • Buffers: shared hit=2557
8. 0.171 5.997 ↑ 3.2 597 1

Nested Loop (cost=1,664.54..15,725.48 rows=1,937 width=612) (actual time=3.302..5.997 rows=597 loops=1)

  • Buffers: shared hit=2296
9. 0.030 1.026 ↑ 2.0 64 1

HashAggregate (cost=1,664.10..1,665.41 rows=131 width=4) (actual time=1.015..1.026 rows=64 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=261
10. 0.996 0.996 ↑ 2.0 64 1

CTE Scan on base_and_descendants namespaces (cost=1,659.84..1,662.46 rows=131 width=4) (actual time=0.053..0.996 rows=64 loops=1)

  • Buffers: shared hit=261
11.          

CTE base_and_descendants

12. 0.150 0.884 ↑ 2.0 64 1

Recursive Union (cost=0.43..1,659.84 rows=131 width=314) (actual time=0.048..0.884 rows=64 loops=1)

  • Buffers: shared hit=261
13. 0.039 0.039 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_10 (cost=0.43..4.45 rows=1 width=314) (actual time=0.039..0.039 rows=1 loops=1)

  • Index Cond: (namespaces_10.id = 9970)
  • Filter: ((namespaces_10.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
14. 0.045 0.695 ↑ 1.0 13 5

Nested Loop (cost=0.43..165.28 rows=13 width=314) (actual time=0.027..0.139 rows=13 loops=5)

  • Buffers: shared hit=257
15. 0.010 0.010 ↓ 1.3 13 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.002 rows=13 loops=5)

16. 0.640 0.640 ↑ 1.0 1 64

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_11 (cost=0.43..16.50 rows=1 width=314) (actual time=0.007..0.010 rows=1 loops=64)

  • Index Cond: (namespaces_11.parent_id = base_and_descendants_1.id)
  • Filter: ((namespaces_11.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=257
17. 3.260 4.800 ↑ 1.7 9 64

Index Scan using index_projects_on_namespace_id on public.projects (cost=0.43..107.18 rows=15 width=612) (actual time=0.045..0.075 rows=9 loops=64)

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

SubPlan (forIndex Scan)

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_4 (cost=0.56..4.58 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_4.user_id = 1) AND (project_authorizations_4.project_id = projects.id))
  • Heap Fetches: 0
20. 1.540 1.540 ↓ 24.0 2,500 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_5 (cost=0.56..6.38 rows=104 width=4) (actual time=0.052..1.540 rows=2,500 loops=1)

  • Index Cond: (project_authorizations_5.user_id = 1)
  • Heap Fetches: 576
  • Buffers: shared hit=1246
21. 0.000 0.391 ↓ 1.1 27 1

Nested Loop (cost=0.85..268.63 rows=25 width=612) (actual time=0.046..0.391 rows=27 loops=1)

  • Buffers: shared hit=261
22. 0.068 0.068 ↑ 1.0 27 1

Index Scan using index_project_group_links_on_group_id on public.project_group_links (cost=0.42..24.25 rows=27 width=4) (actual time=0.011..0.068 rows=27 loops=1)

  • Index Cond: (project_group_links.group_id = 9970)
  • Buffers: shared hit=28
23. 0.270 0.324 ↑ 1.0 1 27

Index Scan using projects_pkey on public.projects projects_1 (cost=0.43..9.04 rows=1 width=612) (actual time=0.012..0.012 rows=1 loops=27)

  • Index Cond: (projects_1.id = project_group_links.project_id)
  • Filter: ((alternatives: SubPlan 4 or hashed SubPlan 5) OR (projects_1.visibility_level = ANY ('{0,10,20}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=233
24.          

SubPlan (forIndex Scan)

25. 0.054 0.054 ↑ 1.0 1 27

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_2 (cost=0.56..4.58 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=27)

  • Index Cond: ((project_authorizations_2.user_id = 1) AND (project_authorizations_2.project_id = projects_1.id))
  • Heap Fetches: 6
  • Buffers: shared hit=125
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.56..6.38 rows=104 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (project_authorizations_3.user_id = 1)
  • Heap Fetches: 0
27. 0.623 19.936 ↑ 1.5 13 623

Limit (cost=0.57..31.04 rows=20 width=51) (actual time=0.009..0.032 rows=13 loops=623)

  • Buffers: shared hit=10652
28. 19.313 19.313 ↑ 84.0 13 623

Index Scan using index_events_on_project_id_and_id on public.events (cost=0.57..1,664.01 rows=1,092 width=51) (actual time=0.009..0.031 rows=13 loops=623)

  • Index Cond: (events.project_id = projects.id)
  • Buffers: shared hit=10652
29. 1.060 1.621 ↑ 1.2 54 1

Nested Loop (cost=1,664.12..9,073.08 rows=67 width=51) (actual time=0.782..1.621 rows=54 loops=1)

  • Buffers: shared hit=756
30. 0.561 0.561 ↑ 1.0 64 1

CTE Scan on base_and_descendants namespaces_1 (cost=1,659.84..8,784.14 rows=67 width=4) (actual time=0.035..0.561 rows=64 loops=1)

  • Filter: ((namespaces_1.visibility_level = ANY ('{0,10,20}'::integer[])) OR (alternatives: SubPlan 2 or hashed SubPlan 3))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=261
31.          

CTE base_and_descendants

32. 0.452 0.465 ↑ 2.0 64 1

Recursive Union (cost=0.43..1,659.84 rows=131 width=314) (actual time=0.029..0.465 rows=64 loops=1)

  • Buffers: shared hit=261
33. 0.013 0.013 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_2 (cost=0.43..4.45 rows=1 width=314) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (namespaces_2.id = 9970)