explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hTkJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 29,187.516 ↑ 1.0 20 1

Limit (cost=4,144,516.86..4,144,516.91 rows=20 width=729) (actual time=29,187.445..29,187.516 rows=20 loops=1)

2. 3.020 29,187.501 ↑ 27,593.8 20 1

Sort (cost=4,144,516.86..4,145,896.55 rows=551,876 width=729) (actual time=29,187.433..29,187.501 rows=20 loops=1)

  • Sort Key: merge_requests.updated_at DESC, merge_requests.id DESC
  • Sort Method: top-N heapsort Memory: 62kB
3. 144.077 29,184.481 ↑ 229.0 2,410 1

Merge Join (cost=1,978,611.55..4,129,831.64 rows=551,876 width=729) (actual time=21,630.988..29,184.481 rows=2,410 loops=1)

  • Merge Cond: (projects.id = merge_requests.target_project_id)
4. 2.150 21,612.904 ↑ 4,101.5 945 1

Sort (cost=1,978,609.37..1,988,299.25 rows=3,875,954 width=4) (actual time=21,611.107..21,612.904 rows=945 loops=1)

  • Sort Key: projects.id
  • Sort Method: quicksort Memory: 69kB
5. 3,444.043 21,610.754 ↑ 4,101.5 945 1

Hash Left Join (cost=1,071,230.28..1,452,259.64 rows=3,875,954 width=4) (actual time=18,166.607..21,610.754 rows=945 loops=1)

  • Hash Cond: (projects.id = project_features.project_id)
  • Filter: ((project_features.merge_requests_access_level IS NULL) OR (project_features.merge_requests_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.merge_requests_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 33
6. 0.235 18.868 ↑ 4,105.9 978 1

Nested Loop (cost=1,461.84..20,058.33 rows=4,015,572 width=4) (actual time=4.835..18.868 rows=978 loops=1)

7. 0.125 4.710 ↑ 1.3 153 1

HashAggregate (cost=1,461.40..1,463.40 rows=200 width=4) (actual time=4.633..4.710 rows=153 loops=1)

  • Group Key: namespaces.id
8. 4.585 4.585 ↑ 5.0 153 1

CTE Scan on base_and_descendants namespaces (cost=1,436.34..1,451.76 rows=771 width=4) (actual time=0.092..4.585 rows=153 loops=1)

9.          

CTE base_and_descendants

10. 0.381 4.363 ↑ 5.0 153 1

Recursive Union (cost=0.43..1,436.34 rows=771 width=347) (actual time=0.089..4.363 rows=153 loops=1)

11. 0.082 0.082 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=347) (actual time=0.082..0.082 rows=1 loops=1)

  • Index Cond: (id = 9,970)
12. 0.060 3.900 ↑ 2.6 30 5

Nested Loop (cost=0.56..141.75 rows=77 width=347) (actual time=0.072..0.780 rows=30 loops=5)

13. 0.015 0.015 ↓ 3.1 31 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.003 rows=31 loops=5)

14. 3.825 3.825 ↑ 8.0 1 153

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..14.07 rows=8 width=347) (actual time=0.014..0.025 rows=1 loops=153)

  • Index Cond: (parent_id = base_and_descendants.id)
15. 10.011 13.923 ↑ 1.7 6 153

Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.43..92.87 rows=10 width=8) (actual time=0.019..0.091 rows=6 loops=153)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: ((NOT archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[]))))
  • Rows Removed by Filter: 0
16.          

SubPlan (for Index Scan)

17. 3.912 3.912 ↑ 1.0 1 978

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=978)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 20))
  • Heap Fetches: 372
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.57..403.67 rows=1,266 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 20))
  • Heap Fetches: 0
19. 3,392.759 18,144.065 ↓ 1.0 14,631,135 1

Hash (cost=662,242.04..662,242.04 rows=14,489,792 width=8) (actual time=18,144.065..18,144.065 rows=14,631,135 loops=1)

  • Buckets: 4,194,304 Batches: 16 Memory Usage: 68,499kB
20. 14,751.306 14,751.306 ↓ 1.0 14,631,135 1

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..662,242.04 rows=14,489,792 width=8) (actual time=0.056..14,751.306 rows=14,631,135 loops=1)

21.          

SubPlan (for Hash Left Join)

22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 20))
  • Heap Fetches: 0
23. 3.778 3.778 ↓ 2.6 3,321 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.57..403.67 rows=1,266 width=4) (actual time=0.021..3.778 rows=3,321 loops=1)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 20))
  • Heap Fetches: 877
24. 7,427.500 7,427.500 ↓ 1.0 2,054,135 1

Index Scan using idx_merge_requests_on_target_project_id_and_iid_opened on merge_requests (cost=0.43..2,121,202.07 rows=2,049,761 width=729) (actual time=0.058..7,427.500 rows=2,054,135 loops=1)

Planning time : 4.039 ms
Execution time : 29,199.578 ms