explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WV0d

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7.716 6,195.827 ↓ 8.4 1,080 1

Sort (cost=1,285.25..1,285.57 rows=129 width=770) (actual time=6,195.614..6,195.827 rows=1,080 loops=1)

  • Sort Key: merge_requests.id DESC
  • Sort Method: quicksort Memory: 1,544kB
  • Buffers: shared hit=16,056 read=3,625 dirtied=46
  • I/O Timings: read=6,087.284
2. 4.080 6,188.111 ↓ 8.4 1,080 1

Nested Loop Left Join (cost=1.87..1,280.73 rows=129 width=770) (actual time=47.611..6,188.111 rows=1,080 loops=1)

  • Filter: ((project_features.merge_requests_access_level > 0) OR (project_features.merge_requests_access_level IS NULL))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=16,053 read=3,625 dirtied=46
  • I/O Timings: read=6,087.284
3. 5.914 5,876.459 ↓ 8.3 1,083 1

Nested Loop (cost=1.44..1,216.99 rows=131 width=774) (actual time=40.913..5,876.459 rows=1,083 loops=1)

  • Buffers: shared hit=11,879 read=3,452 dirtied=44
  • I/O Timings: read=5,788.776
4. 5.782 5,470.918 ↓ 7.8 1,083 1

Nested Loop (cost=1.00..652.09 rows=139 width=770) (actual time=21.796..5,470.918 rows=1,083 loops=1)

  • Buffers: shared hit=3,080 read=3,245 dirtied=37
  • I/O Timings: read=5,424.778
5. 1,293.420 1,293.420 ↓ 7.8 1,083 1

Index Scan using index_merge_request_assignees_on_user_id on public.merge_request_assignees (cost=0.44..154.13 rows=139 width=4) (actual time=12.949..1,293.420 rows=1,083 loops=1)

  • Index Cond: (merge_request_assignees.user_id = 895,869)
  • Buffers: shared hit=1 read=905 dirtied=7
  • I/O Timings: read=1,283.565
6. 4,171.716 4,171.716 ↑ 1.0 1 1,083

Index Scan using merge_requests_pkey on public.merge_requests (cost=0.56..3.58 rows=1 width=770) (actual time=3.852..3.852 rows=1 loops=1,083)

  • Index Cond: (merge_requests.id = merge_request_assignees.merge_request_id)
  • Buffers: shared hit=3,079 read=2,340 dirtied=30
  • I/O Timings: read=4,141.213
7. 340.062 399.627 ↑ 1.0 1 1,083

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

  • 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=8,799 read=207 dirtied=7
  • I/O Timings: read=363.998
8.          

SubPlan (for Index Scan)

9. 59.565 59.565 ↑ 1.0 1 1,083

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.055..0.055 rows=1 loops=1,083)

  • Index Cond: ((project_authorizations.user_id = 895,869) AND (project_authorizations.project_id = projects.id))
  • Heap Fetches: 34
  • Buffers: shared hit=4,641 read=33
  • I/O Timings: read=49.579
10. 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..949.07 rows=2,735 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (project_authorizations_1.user_id = 895,869)
  • Heap Fetches: 0
11. 307.572 307.572 ↑ 1.0 1 1,083

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.284..0.284 rows=1 loops=1,083)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=4,174 read=173 dirtied=2
  • I/O Timings: read=298.508