explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SEdH : Optimization for: plan #9wAF

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 6.255 100.783 ↓ 1.1 3,165 1

Sort (cost=29,580.65..29,587.99 rows=2,936 width=2,550) (actual time=100.372..100.783 rows=3,165 loops=1)

  • Sort Key: merge_requests.id DESC
  • Sort Method: quicksort Memory: 3,934kB
  • Buffers: shared hit=59,369
2. 29.138 94.528 ↓ 1.1 3,165 1

HashAggregate (cost=29,352.82..29,382.18 rows=2,936 width=2,550) (actual time=89.469..94.528 rows=3,165 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=59,369
3. 0.743 65.390 ↓ 1.1 3,165 1

Append (cost=1.87..29,051.88 rows=2,936 width=2,550) (actual time=0.074..65.390 rows=3,165 loops=1)

  • Buffers: shared hit=56,873
4. 5.399 64.635 ↓ 1.1 3,165 1

Nested Loop Left Join (cost=1.87..28,940.13 rows=2,929 width=753) (actual time=0.073..64.635 rows=3,165 loops=1)

  • Filter: ((project_features.merge_requests_access_level > 0) OR (project_features.merge_requests_access_level IS NULL))
  • Rows Removed by Filter: 12
  • Buffers: shared hit=56,872
5. 5.234 52.882 ↓ 1.1 3,177 1

Nested Loop (cost=1.44..27,497.28 rows=2,963 width=757) (actual time=0.062..52.882 rows=3,177 loops=1)

  • Buffers: shared hit=44,162
6. 5.134 28.586 ↓ 1.0 3,177 1

Nested Loop (cost=1.00..14,691.69 rows=3,151 width=753) (actual time=0.037..28.586 rows=3,177 loops=1)

  • Buffers: shared hit=18,656
7. 4.390 4.390 ↓ 1.0 3,177 1

Index Scan using index_merge_request_assignees_on_user_id on public.merge_request_assignees (cost=0.44..3,437.74 rows=3,151 width=4) (actual time=0.020..4.390 rows=3,177 loops=1)

  • Index Cond: (merge_request_assignees.user_id = 1)
  • Buffers: shared hit=2,763
8. 19.062 19.062 ↑ 1.0 1 3,177

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

  • Index Cond: (merge_requests.id = merge_request_assignees.merge_request_id)
  • Buffers: shared hit=15,893
9. 9.531 19.062 ↑ 1.0 1 3,177

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

  • 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=25,506
10.          

SubPlan (for Index Scan)

11. 9.531 9.531 ↑ 1.0 1 3,177

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,177)

  • Index Cond: ((project_authorizations.user_id = 1) AND (project_authorizations.project_id = projects.id))
  • Heap Fetches: 25
  • Buffers: shared hit=12,796
12. 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 = 1)
  • Heap Fetches: 0
13. 6.354 6.354 ↑ 1.0 1 3,177

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.002..0.002 rows=1 loops=3,177)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=12,710
14. 0.000 0.012 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.59..67.71 rows=7 width=753) (actual time=0.012..0.012 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
15. 0.001 0.012 ↓ 0.0 0 1

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

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

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

  • Buffers: shared hit=1
17. 0.010 0.010 ↓ 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.010..0.010 rows=0 loops=1)

  • Index Cond: (merge_request_reviewers.user_id = 1)
  • Buffers: shared hit=1
18. 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)
19. 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
20.          

SubPlan (for Index Scan)

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_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 = 1) AND (project_authorizations_2.project_id = projects_1.id))
  • Heap Fetches: 0
22. 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 = 1)
  • Heap Fetches: 0
23. 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)