explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xQA8

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 44.621 ↑ 1.0 20 1

Limit (cost=79,147.29..79,147.34 rows=20 width=51) (actual time=44.611..44.621 rows=20 loops=1)

  • Buffers: shared hit=13238
2. 1.843 44.613 ↑ 1,962.0 20 1

Sort (cost=79,147.29..79,245.39 rows=39,240 width=51) (actual time=44.611..44.613 rows=20 loops=1)

  • Sort Key: events.id DESC
  • Sort Method: top-N heapsort Memory: 26kB
  • Buffers: shared hit=13238
3. 2.405 42.770 ↑ 5.0 7,920 1

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

  • Buffers: shared hit=13235
4. 2.466 9.838 ↑ 3.1 623 1

HashAggregate (cost=16,381.60..16,401.22 rows=1,962 width=3,665) (actual time=9.207..9.838 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
5. 0.123 7.372 ↑ 3.1 624 1

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

  • Buffers: shared hit=2557
6. 0.193 6.841 ↑ 3.2 597 1

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

  • Buffers: shared hit=2296
7. 0.059 1.080 ↑ 2.0 64 1

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

  • Group Key: namespaces.id
  • Buffers: shared hit=261
8. 1.021 1.021 ↑ 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.061..1.021 rows=64 loops=1)

  • Buffers: shared hit=261
9.          

CTE base_and_descendants

10. 0.145 0.935 ↑ 2.0 64 1

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

  • Buffers: shared hit=261
11. 0.050 0.050 ↑ 1.0 1 1

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

  • Index Cond: (namespaces_1.id = 9970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
12. 0.021 0.740 ↑ 1.0 13 5

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

  • Buffers: shared hit=257
13. 0.015 0.015 ↓ 1.3 13 5

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

14. 0.704 0.704 ↑ 1.0 1 64

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

  • Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=257
15. 3.782 5.568 ↑ 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.052..0.087 rows=9 loops=64)

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

SubPlan (forIndex Scan)

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

  • Index Cond: ((project_authorizations_2.user_id = 1) AND (project_authorizations_2.project_id = projects.id))
  • Heap Fetches: 0
18. 1.786 1.786 ↓ 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_3 (cost=0.56..6.38 rows=104 width=4) (actual time=0.081..1.786 rows=2,500 loops=1)

  • Index Cond: (project_authorizations_3.user_id = 1)
  • Heap Fetches: 576
  • Buffers: shared hit=1246
19. 0.023 0.408 ↓ 1.1 27 1

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

  • Buffers: shared hit=261
20. 0.061 0.061 ↑ 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.061 rows=27 loops=1)

  • Index Cond: (project_group_links.group_id = 9970)
  • Buffers: shared hit=28
21. 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 1 or hashed SubPlan 2) OR (projects_1.visibility_level = ANY ('{0,10,20}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=233
22.          

SubPlan (forIndex Scan)

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

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

  • Index Cond: (project_authorizations_1.user_id = 1)
  • Heap Fetches: 0
25. 0.623 30.527 ↑ 1.5 13 623

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

  • Buffers: shared hit=10652
26. 29.904 29.904 ↑ 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.012..0.048 rows=13 loops=623)

  • Index Cond: (events.project_id = projects.id)
  • Buffers: shared hit=10652