explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1gbO : Optimization for: plan #WV0d

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4.307 56.149 ↓ 8.4 1,080 1

Sort (cost=1,285.25..1,285.57 rows=129 width=770) (actual time=55.934..56.149 rows=1,080 loops=1)

  • Sort Key: merge_requests.id DESC
  • Sort Method: quicksort Memory: 1,544kB
  • Buffers: shared hit=19,678
2. 2.100 51.842 ↓ 8.4 1,080 1

Nested Loop Left Join (cost=1.87..1,280.73 rows=129 width=770) (actual time=0.452..51.842 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=19,678
3. 2.091 45.410 ↓ 8.3 1,083 1

Nested Loop (cost=1.44..1,216.99 rows=131 width=774) (actual time=0.401..45.410 rows=1,083 loops=1)

  • Buffers: shared hit=15,331
4. 1.576 32.489 ↓ 7.8 1,083 1

Nested Loop (cost=1.00..652.09 rows=139 width=770) (actual time=0.208..32.489 rows=1,083 loops=1)

  • Buffers: shared hit=6,325
5. 8.170 8.170 ↓ 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=0.081..8.170 rows=1,083 loops=1)

  • Index Cond: (merge_request_assignees.user_id = 895,869)
  • Buffers: shared hit=906
6. 22.743 22.743 ↑ 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=0.021..0.021 rows=1 loops=1,083)

  • Index Cond: (merge_requests.id = merge_request_assignees.merge_request_id)
  • Buffers: shared hit=5,419
7. 6.498 10.830 ↑ 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.010..0.010 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=9,006
8.          

SubPlan (for Index Scan)

9. 4.332 4.332 ↑ 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.004..0.004 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,674
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. 4.332 4.332 ↑ 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.004..0.004 rows=1 loops=1,083)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=4,347