explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lmt9

Settings
# exclusive inclusive rows x rows loops node
1. 0.727 312.630 ↓ 436.0 436 1

GroupAggregate (cost=1,659.13..1,659.15 rows=1 width=16) (actual time=311.678..312.630 rows=436 loops=1)

  • Group Key: labels.id, (date(merge_requests.created_at))
2. 2.616 311.903 ↓ 3,928.0 3,928 1

Sort (cost=1,659.13..1,659.13 rows=1 width=12) (actual time=311.599..311.903 rows=3,928 loops=1)

  • Sort Key: labels.id, (date(merge_requests.created_at))
  • Sort Method: quicksort Memory: 281kB
3. 2.120 309.287 ↓ 3,928.0 3,928 1

Hash Semi Join (cost=1,268.10..1,659.12 rows=1 width=12) (actual time=5.260..309.287 rows=3,928 loops=1)

  • Hash Cond: (projects.namespace_id = namespaces.id)
4. 0.606 306.372 ↓ 3,928.0 3,928 1

Nested Loop Left Join (cost=2.31..393.32 rows=1 width=20) (actual time=4.443..306.372 rows=3,928 loops=1)

  • 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)))
5. 2.086 293.838 ↓ 3,928.0 3,928 1

Nested Loop (cost=1.87..389.22 rows=1 width=24) (actual time=4.425..293.838 rows=3,928 loops=1)

6. 13.400 268.184 ↓ 3,928.0 3,928 1

Nested Loop (cost=1.44..385.01 rows=1 width=20) (actual time=4.385..268.184 rows=3,928 loops=1)

7. 2.362 110.000 ↓ 6,032.7 18,098 1

Nested Loop (cost=0.87..381.36 rows=3 width=8) (actual time=0.039..110.000 rows=18,098 loops=1)

8. 0.172 0.172 ↑ 1.0 6 1

Index Only Scan using labels_pkey on labels (cost=0.43..13.21 rows=6 width=4) (actual time=0.014..0.172 rows=6 loops=1)

  • Index Cond: (id = ANY ('{2731248,10230929,4116705,2492649,2278648,2779806}'::integer[]))
  • Heap Fetches: 1
9. 107.466 107.466 ↓ 167.6 3,016 6

Index Scan using index_label_links_on_label_id on label_links (cost=0.44..61.18 rows=18 width=8) (actual time=0.020..17.911 rows=3,016 loops=6)

  • Index Cond: (label_id = labels.id)
  • Filter: ((target_type)::text = 'MergeRequest'::text)
  • Rows Removed by Filter: 8280
10. 144.784 144.784 ↓ 0.0 0 18,098

Index Scan using merge_requests_pkey on merge_requests (cost=0.56..1.21 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=18,098)

  • Index Cond: (id = label_links.target_id)
  • Filter: ((created_at <= '2019-08-01 00:00:00+00'::timestamp with time zone) AND (created_at >= '2019-05-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 1
11. 15.712 23.568 ↑ 1.0 1 3,928

Index Scan using projects_pkey on projects (cost=0.43..4.21 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=3,928)

  • Index Cond: (id = merge_requests.target_project_id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[])))
12.          

SubPlan (forIndex Scan)

13. 7.856 7.856 ↑ 1.0 1 3,928

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..3.59 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=3,928)

  • Index Cond: ((user_id = 4156052) AND (project_id = projects.id) AND (access_level >= 20))
  • Heap Fetches: 1432
14. 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.56..689.86 rows=2,132 width=4) (never executed)

  • Index Cond: ((user_id = 4156052) AND (access_level >= 20))
  • Heap Fetches: 0
15. 11.784 11.784 ↑ 1.0 1 3,928

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.49 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=3,928)

  • Index Cond: (projects.id = project_id)
16.          

SubPlan (forNested Loop Left Join)

17. 0.144 0.144 ↑ 1.0 1 48

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.56..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=48)

  • Index Cond: ((user_id = 4156052) AND (project_id = projects.id) AND (access_level >= 20))
  • Heap Fetches: 0
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_3 (cost=0.56..689.86 rows=2,132 width=4) (never executed)

  • Index Cond: ((user_id = 4156052) AND (access_level >= 20))
  • Heap Fetches: 0
19. 0.013 0.795 ↑ 2.0 65 1

Hash (cost=1,264.16..1,264.16 rows=131 width=4) (actual time=0.795..0.795 rows=65 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.782 0.782 ↑ 2.0 65 1

CTE Scan on base_and_descendants namespaces (cost=1,260.23..1,262.85 rows=131 width=4) (actual time=0.035..0.782 rows=65 loops=1)

21.          

CTE base_and_descendants

22. 0.140 0.687 ↑ 2.0 65 1

Recursive Union (cost=0.43..1,260.23 rows=131 width=322) (actual time=0.031..0.687 rows=65 loops=1)

23. 0.017 0.017 ↑ 1.0 1 1

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

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
24. 0.070 0.530 ↑ 1.0 13 5

Nested Loop (cost=0.43..125.42 rows=13 width=322) (actual time=0.017..0.106 rows=13 loops=5)

25. 0.005 0.005 ↓ 1.3 13 5

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

26. 0.455 0.455 ↑ 1.0 1 65

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..12.51 rows=1 width=322) (actual time=0.005..0.007 rows=1 loops=65)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
Planning time : 3.679 ms
Execution time : 313.773 ms