explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vIZ1

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 7,459.448 ↑ 1.0 100 1

Limit (cost=5,038,688.83..5,038,689.08 rows=100 width=3,664) (actual time=7,459.426..7,459.448 rows=100 loops=1)

2. 363.663 7,459.435 ↑ 13,682.3 100 1

Sort (cost=5,038,688.83..5,042,109.41 rows=1,368,232 width=3,664) (actual time=7,459.425..7,459.435 rows=100 loops=1)

  • Sort Key: projects.created_at DESC, projects.id DESC
  • Sort Method: top-N heapsort Memory: 71kB
3. 1,090.323 7,095.772 ↑ 1.0 1,366,250 1

Unique (cost=4,716,170.17..4,972,713.67 rows=1,368,232 width=3,664) (actual time=5,440.578..7,095.772 rows=1,366,250 loops=1)

4. 2,605.238 6,005.449 ↑ 1.0 1,367,035 1

Sort (cost=4,716,170.17..4,719,590.75 rows=1,368,232 width=3,664) (actual time=5,440.577..6,005.449 rows=1,367,035 loops=1)

  • Sort 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
  • Sort Method: external merge Disk: 396360kB
5. 122.939 3,400.211 ↑ 1.0 1,367,035 1

Append (cost=0.56..418,909.77 rows=1,368,232 width=3,664) (actual time=0.032..3,400.211 rows=1,367,035 loops=1)

6. 3,260.789 3,260.789 ↑ 1.0 1,365,317 1

Index Scan using index_projects_on_visibility_level on projects (cost=0.56..404,897.53 rows=1,368,146 width=593) (actual time=0.032..3,260.789 rows=1,365,317 loops=1)

  • Index Cond: (visibility_level = ANY ('{10,20}'::integer[]))
  • Filter: (NOT pending_delete)
  • Rows Removed by Filter: 53
7. 0.455 16.483 ↓ 20.0 1,718 1

Nested Loop (cost=31.72..329.92 rows=86 width=593) (actual time=1.744..16.483 rows=1,718 loops=1)

8. 1.010 2.284 ↓ 20.0 1,718 1

HashAggregate (cost=31.29..32.15 rows=86 width=4) (actual time=1.723..2.284 rows=1,718 loops=1)

  • Group Key: project_authorizations.project_id
9. 1.274 1.274 ↓ 20.0 1,718 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..31.07 rows=86 width=4) (actual time=0.025..1.274 rows=1,718 loops=1)

  • Index Cond: (user_id = 1562869)
  • Heap Fetches: 572
10. 13.744 13.744 ↑ 1.0 1 1,718

Index Scan using projects_pkey on projects projects_1 (cost=0.43..3.45 rows=1 width=593) (actual time=0.008..0.008 rows=1 loops=1,718)

  • Index Cond: (id = project_authorizations.project_id)
  • Filter: (NOT pending_delete)
Planning time : 1.400 ms
Execution time : 7,532.467 ms