explain.depesz.com

PostgreSQL's explain analyze made readable

Result: upHH

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7.597 23,462.714 ↑ 1.1 3,334 1

Sort (cost=37,427.89..37,437.18 rows=3,716 width=2,550) (actual time=23,462.272..23,462.714 rows=3,334 loops=1)

  • Sort Key: merge_requests.id DESC
  • Sort Method: quicksort Memory: 4,858kB
  • Buffers: shared hit=56,634 read=14,208 dirtied=225
  • I/O Timings: read=22,709.098
2. 7,000.944 23,455.117 ↑ 1.1 3,334 1

HashAggregate (cost=37,133.22..37,170.38 rows=3,716 width=2,550) (actual time=23,449.240..23,455.117 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=56,631 read=14,208 dirtied=225
  • I/O Timings: read=22,709.098
3. 3.593 16,454.173 ↑ 1.1 3,334 1

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

  • Buffers: shared hit=51,293 read=9,848 dirtied=182
  • I/O Timings: read=15,957.914
4. 16.468 16,450.472 ↑ 1.1 3,334 1

Nested Loop Left Join (cost=1.87..36,628.88 rows=3,709 width=753) (actual time=29.962..16,450.472 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=51,289 read=9,848 dirtied=182
  • I/O Timings: read=15,957.914
5. 29.314 16,037.258 ↑ 1.1 3,334 1

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

  • Buffers: shared hit=38,101 read=9,700 dirtied=181
  • I/O Timings: read=15,597.200
6. 26.031 15,474.504 ↑ 1.2 3,334 1

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

  • Buffers: shared hit=10,385 read=9,519 dirtied=167
  • I/O Timings: read=15,183.518
7. 4,392.929 4,392.929 ↑ 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=12.583..4,392.929 rows=3,334 loops=1)

  • Index Cond: (merge_request_assignees.user_id = 64,248)
  • Buffers: shared hit=183 read=3,044 dirtied=86
  • I/O Timings: read=4,302.229
8. 11,055.544 11,055.544 ↑ 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=3.316..3.316 rows=1 loops=3,334)

  • Index Cond: (merge_requests.id = merge_request_assignees.merge_request_id)
  • Buffers: shared hit=10,202 read=6,475 dirtied=81
  • I/O Timings: read=10,881.289
9. 420.084 533.440 ↑ 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.160..0.160 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,716 read=181 dirtied=14
  • I/O Timings: read=413.682
10.          

SubPlan (for Index Scan)

11. 113.356 113.356 ↑ 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.034..0.034 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,532 read=29 dirtied=6
  • I/O Timings: read=67.856
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 = 64,248)
  • Heap Fetches: 0
13. 396.746 396.746 ↑ 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.119..0.119 rows=1 loops=3,334)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=13,188 read=148 dirtied=1
  • I/O Timings: read=360.714
14. 0.000 0.108 ↓ 0.0 0 1

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

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

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

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

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

  • Index Cond: (merge_request_reviewers.user_id = 64,248)
  • Buffers: shared hit=4
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 = 64,248) 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 = 64,248)
  • 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)