explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kvoJ : Optimization for: plan #upHH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 82.610 158.508 ↑ 1.1 3,334 1

HashAggregate (cost=37,133.22..37,170.38 rows=3,716 width=2,550) (actual time=152.978..158.508 rows=3,334 loops=1)

  • Group Key: merge_requests.id, merge_requests.target_branch, merge_requests.source_branch, merge_requests.source_project_id, merge_requests.author_id, merge_requests.assignee_id, merge_requests.title, merge_requests.created_at, merge_requests.updated_at, merge_requests.milestone_id, merge_requests.merge_status, merge_requests.target_project_id, merge_requests.iid, merge_requests.description, merge_requests.updated_by_id, merge_requests.merge_error, merge_requests.merge_params, merge_requests.merge_when_pipeline_succeeds, merge_requests.merge_user_id, merge_requests.merge_commit_sha, merge_requests.approvals_before_merge, merge_requests.rebase_commit_sha, merge_requests.in_progress_merge_commit_sha, merge_requests.lock_version, merge_requests.title_html, merge_requests.description_html, merge_requests.time_estimate, merge_requests.squash, merge_requests.cached_markdown_version, merge_requests.last_edited_at, merge_requests.last_edited_by_id, merge_requests.head_pipeline_id, merge_requests.merge_jid, merge_requests.discussion_locked, merge_requests.latest_merge_request_diff_id, merge_requests.allow_maintainer_to_push, merge_requests.state_id, merge_requests.rebase_jid, merge_requests.squash_commit_sha, merge_requests.sprint_id, merge_requests.merge_ref_sha
  • Buffers: shared hit=70,796
2. 0.759 75.898 ↑ 1.1 3,334 1

Append (cost=1.87..36,752.33 rows=3,716 width=2,550) (actual time=0.128..75.898 rows=3,334 loops=1)

  • Buffers: shared hit=61,117
3. 3.892 75.131 ↑ 1.1 3,334 1

Nested Loop Left Join (cost=1.87..36,628.88 rows=3,709 width=753) (actual time=0.128..75.131 rows=3,334 loops=1)

  • Filter: ((project_features.merge_requests_access_level > 0) OR (project_features.merge_requests_access_level IS NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=61,116
4. 4.946 61.237 ↑ 1.1 3,334 1

Nested Loop (cost=1.44..34,800.85 rows=3,754 width=757) (actual time=0.113..61.237 rows=3,334 loops=1)

  • Buffers: shared hit=47,780
5. 4.095 32.953 ↑ 1.2 3,334 1

Nested Loop (cost=1.00..18,581.52 rows=3,991 width=753) (actual time=0.076..32.953 rows=3,334 loops=1)

  • Buffers: shared hit=19,883
6. 5.520 5.520 ↑ 1.2 3,334 1

Index Scan using index_merge_request_assignees_on_user_id on public.merge_request_assignees (cost=0.44..4,340.76 rows=3,991 width=4) (actual time=0.052..5.520 rows=3,334 loops=1)

  • Index Cond: (merge_request_assignees.user_id = 64,248)
  • Buffers: shared hit=3,206
7. 23.338 23.338 ↑ 1.0 1 3,334

Index Scan using merge_requests_pkey on public.merge_requests (cost=0.56..3.57 rows=1 width=753) (actual time=0.007..0.007 rows=1 loops=3,334)

  • Index Cond: (merge_requests.id = merge_request_assignees.merge_request_id)
  • Buffers: shared hit=16,677
8. 13.336 23.338 ↑ 1.0 1 3,334

Index Scan using projects_pkey on public.projects (cost=0.43..4.06 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=3,334)

  • Index Cond: (projects.id = merge_requests.target_project_id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{0,10,20}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=27,897
9.          

SubPlan (for Index Scan)

10. 10.002 10.002 ↑ 1.0 1 3,334

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=3,334)

  • Index Cond: ((project_authorizations.user_id = 64,248) AND (project_authorizations.project_id = projects.id))
  • Heap Fetches: 500
  • Buffers: shared hit=14,561
11. 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..947.07 rows=2,735 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (project_authorizations_1.user_id = 64,248)
  • Heap Fetches: 0
12. 10.002 10.002 ↑ 1.0 1 3,334

Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..0.47 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=3,334)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=13,336
13. 0.000 0.008 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.59..67.71 rows=7 width=753) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: ((project_features_1.merge_requests_access_level > 0) OR (project_features_1.merge_requests_access_level IS NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1
14. 0.001 0.008 ↓ 0.0 0 1

Nested Loop (cost=1.15..64.30 rows=7 width=757) (actual time=0.008..0.008 rows=0 loops=1)

  • Buffers: shared hit=1
15. 0.000 0.007 ↓ 0.0 0 1

Nested Loop (cost=0.72..35.85 rows=7 width=753) (actual time=0.007..0.007 rows=0 loops=1)

  • Buffers: shared hit=1
16. 0.007 0.007 ↓ 0.0 0 1

Index Scan using index_merge_request_reviewers_on_user_id on public.merge_request_reviewers (cost=0.15..10.78 rows=7 width=8) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: (merge_request_reviewers.user_id = 64,248)
  • Buffers: shared hit=1
17. 0.000 0.000 ↓ 0.0 0 0

Index Scan using merge_requests_pkey on public.merge_requests merge_requests_1 (cost=0.56..3.58 rows=1 width=753) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (merge_requests_1.id = merge_request_reviewers.merge_request_id)
18. 0.000 0.000 ↓ 0.0 0 0

Index Scan using projects_pkey on public.projects projects_1 (cost=0.43..4.06 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (projects_1.id = merge_requests_1.target_project_id)
  • Filter: ((alternatives: SubPlan 3 or hashed SubPlan 4) OR (projects_1.visibility_level = ANY ('{0,10,20}'::integer[])))
  • Rows Removed by Filter: 0
19.          

SubPlan (for Index Scan)

20. 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 = 64,248) AND (project_authorizations_2.project_id = projects_1.id))
  • Heap Fetches: 0
21. 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..947.07 rows=2,735 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (project_authorizations_3.user_id = 64,248)
  • Heap Fetches: 0
22. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_project_features_on_project_id on public.project_features project_features_1 (cost=0.43..0.47 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (projects_1.id = project_features_1.project_id)