explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N0hY

Settings
# exclusive inclusive rows x rows loops node
1. 1.466 58,068,561.364 ↑ 646,039.7 70 1

Sort (cost=507,983,741.06..508,096,798.00 rows=45,222,776 width=756) (actual time=58,068,561.218..58,068,561.364 rows=70 loops=1)

  • Sort Key: merge_requests.id DESC
  • Sort Method: quicksort Memory: 122kB
  • Buffers: shared hit=26,244,989 read=51,373,955 dirtied=115,865 written=373
  • I/O Timings: read=57,331,977.030 write=14.824
2. 1,942.602 58,068,559.898 ↑ 646,039.7 70 1

Merge Right Join (cost=6,981.03..473,019,804.84 rows=45,222,776 width=756) (actual time=65,405.443..58,068,559.898 rows=70 loops=1)

  • Merge Cond: (project_features.project_id = projects.id)
  • 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=26,244,989 read=51,373,955 dirtied=115,865 written=373
  • I/O Timings: read=57,331,977.030 write=14.824
3. 288,411.200 288,411.200 ↑ 1.4 10,997,879 1

Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..719,835.37 rows=15,482,801 width=8) (actual time=4.094..288,411.200 rows=10,997,879 loops=1)

  • Buffers: shared hit=6,763,656 read=289,208 dirtied=2,545
  • I/O Timings: read=275,296.218
4. 1.394 57,778,206.096 ↑ 654,912.1 70 1

Materialize (cost=6,980.59..471,573,604.77 rows=45,843,846 width=760) (actual time=64,400.189..57,778,206.096 rows=70 loops=1)

  • Buffers: shared hit=19,481,333 read=51,084,747 dirtied=113,320 written=373
  • I/O Timings: read=57,056,680.812 write=14.824
5. 3,795.615 57,778,204.702 ↑ 654,912.1 70 1

Merge Join (cost=6,980.59..471,458,995.16 rows=45,843,846 width=760) (actual time=64,400.171..57,778,204.702 rows=70 loops=1)

  • Merge Cond: (merge_requests.target_project_id = projects.id)
  • Buffers: shared hit=19,481,333 read=51,084,747 dirtied=113,320 written=373
  • I/O Timings: read=57,056,680.812 write=14.824
6. 54,761,993.674 54,762,202.539 ↑ 654,912.1 70 1

Index Scan using index_merge_requests_on_target_project_id_and_iid on public.merge_requests (cost=0.56..413,941,965.47 rows=45,843,846 width=756) (actual time=59,352.901..54,762,202.539 rows=70 loops=1)

  • Filter: ((alternatives: SubPlan 3 or hashed SubPlan 4) OR (alternatives: SubPlan 5 or hashed SubPlan 6))
  • Rows Removed by Filter: 61,151,387
  • Buffers: shared hit=13,582,191 read=46,056,945 dirtied=107,999 written=373
  • I/O Timings: read=54,110,479.041 write=14.824
7.          

SubPlan (for Index Scan)

8. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_merge_request_assignees_on_merge_request_id_and_user_id on public.merge_request_assignees (cost=0.44..3.46 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((merge_request_assignees.merge_request_id = merge_requests.id) AND (merge_request_assignees.user_id = 4,901,936))
  • Heap Fetches: 0
9. 208.802 208.802 ↑ 2.0 70 1

Index Scan using index_merge_request_assignees_on_user_id on public.merge_request_assignees merge_request_assignees_1 (cost=0.44..153.40 rows=138 width=4) (actual time=12.516..208.802 rows=70 loops=1)

  • Index Cond: (merge_request_assignees_1.user_id = 4,901,936)
  • Buffers: shared read=74 dirtied=1
  • I/O Timings: read=204.722
10. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_merge_request_reviewers_on_merge_request_id_and_user_id on public.merge_request_reviewers (cost=0.15..3.17 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((merge_request_reviewers.merge_request_id = merge_requests.id) AND (merge_request_reviewers.user_id = 4,901,936))
  • Heap Fetches: 0
11. 0.063 0.063 ↓ 0.0 0 1

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

  • Index Cond: (merge_request_reviewers_1.user_id = 4,901,936)
  • Buffers: shared hit=4
12. 3,010,225.880 3,012,206.548 ↑ 1.4 10,997,878 1

Index Scan using projects_pkey on public.projects (cost=0.43..56,913,715.38 rows=15,438,401 width=4) (actual time=1,993.036..3,012,206.548 rows=10,997,878 loops=1)

  • 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=5,899,142 read=5,027,802 dirtied=5,321
  • I/O Timings: read=2,946,201.772
13.          

SubPlan (for Index Scan)

14. 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 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations.user_id = 4,901,936) AND (project_authorizations.project_id = projects.id))
  • Heap Fetches: 0
15. 1,980.668 1,980.668 ↓ 1.3 3,610 1

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=16.819..1,980.668 rows=3,610 loops=1)

  • Index Cond: (project_authorizations_1.user_id = 4,901,936)
  • Heap Fetches: 856
  • Buffers: shared hit=1,448 read=779 dirtied=144
  • I/O Timings: read=1,954.523