explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sUTD

Settings
# exclusive inclusive rows x rows loops node
1. 6.307 2,176.804 ↓ 1.2 3,162 1

Sort (cost=25,816.83..25,823.59 rows=2,706 width=2,550) (actual time=2,176.391..2,176.804 rows=3,162 loops=1)

  • Sort Key: merge_requests.id DESC
  • Sort Method: quicksort Memory: 3,929kB
  • Buffers: shared hit=48,853 read=10,457
  • I/O Timings: read=1,967.946
2. 353.089 2,170.497 ↓ 1.2 3,162 1

HashAggregate (cost=25,608.44..25,635.50 rows=2,706 width=2,550) (actual time=2,165.450..2,170.497 rows=3,162 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=48,853 read=10,457
  • I/O Timings: read=1,967.946
3. 1.405 1,817.408 ↓ 1.2 3,162 1

Append (cost=1.87..25,331.07 rows=2,706 width=2,550) (actual time=14.830..1,817.408 rows=3,162 loops=1)

  • Buffers: shared hit=47,539 read=9,283
  • I/O Timings: read=1,669.083
4. 7.378 1,815.991 ↓ 1.2 3,162 1

Nested Loop Left Join (cost=1.87..25,222.78 rows=2,699 width=756) (actual time=14.828..1,815.991 rows=3,162 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=47,538 read=9,283
  • I/O Timings: read=1,669.083
5. 9.942 1,745.133 ↓ 1.2 3,174 1

Nested Loop (cost=1.44..23,894.16 rows=2,736 width=760) (actual time=14.730..1,745.133 rows=3,174 loops=1)

  • Buffers: shared hit=34,938 read=9,185
  • I/O Timings: read=1,619.730
6. 9.530 1,627.275 ↓ 1.2 3,174 1

Nested Loop (cost=1.00..12,775.01 rows=2,736 width=756) (actual time=6.753..1,627.275 rows=3,174 loops=1)

  • Buffers: shared hit=9,575 read=9,063
  • I/O Timings: read=1,553.865
7. 322.753 322.753 ↓ 1.2 3,174 1

Index Scan using index_merge_request_assignees_on_user_id on public.merge_request_assignees (cost=0.44..2,998.79 rows=2,736 width=4) (actual time=2.664..322.753 rows=3,174 loops=1)

  • Index Cond: (merge_request_assignees.user_id = 1)
  • Buffers: shared hit=19 read=2,741
  • I/O Timings: read=308.172
8. 1,294.992 1,294.992 ↑ 1.0 1 3,174

Index Scan using merge_requests_pkey on public.merge_requests (cost=0.56..3.57 rows=1 width=756) (actual time=0.408..0.408 rows=1 loops=3,174)

  • Index Cond: (merge_requests.id = merge_request_assignees.merge_request_id)
  • Buffers: shared hit=9,556 read=6,322
  • I/O Timings: read=1,245.693
9. 66.654 107.916 ↑ 1.0 1 3,174

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

  • 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,363 read=122
  • I/O Timings: read=65.865
10.          

SubPlan (for Index Scan)

11. 41.262 41.262 ↑ 1.0 1 3,174

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.013..0.013 rows=1 loops=3,174)

  • Index Cond: ((project_authorizations.user_id = 1) AND (project_authorizations.project_id = projects.id))
  • Heap Fetches: 28
  • Buffers: shared hit=12,765 read=22
  • I/O Timings: read=25.162
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..1,020.29 rows=2,678 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (project_authorizations_1.user_id = 1)
  • Heap Fetches: 0
13. 63.480 63.480 ↑ 1.0 1 3,174

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.020..0.020 rows=1 loops=3,174)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=12,600 read=98
  • I/O Timings: read=49.353
14. 0.001 0.012 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.59..67.70 rows=7 width=756) (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.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=1.15..64.30 rows=7 width=760) (actual time=0.011..0.011 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=756) (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=756) (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..1,020.29 rows=2,678 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)