explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gLUA

Settings
# exclusive inclusive rows x rows loops node
1. 1.017 654.876 ↓ 455.0 455 1

GroupAggregate (cost=1,758.66..1,758.68 rows=1 width=16) (actual time=653.384..654.876 rows=455 loops=1)

  • Group Key: labels.id, (date(issues.created_at))
2. 3.621 653.859 ↓ 6,920.0 6,920 1

Sort (cost=1,758.66..1,758.66 rows=1 width=12) (actual time=653.369..653.859 rows=6,920 loops=1)

  • Sort Key: labels.id, (date(issues.created_at))
  • Sort Method: quicksort Memory: 517kB
3. 3.143 650.238 ↓ 6,920.0 6,920 1

Hash Semi Join (cost=1,268.48..1,758.65 rows=1 width=12) (actual time=1.227..650.238 rows=6,920 loops=1)

  • Hash Cond: (projects.namespace_id = namespaces.id)
4. 5.143 646.299 ↓ 6,923.0 6,923 1

Nested Loop Left Join (cost=2.18..492.34 rows=1 width=20) (actual time=0.406..646.299 rows=6,923 loops=1)

  • Filter: ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 7 or hashed SubPlan 8)))
5. 0.965 627.264 ↓ 6,923.0 6,923 1

Nested Loop (cost=1.75..488.25 rows=1 width=24) (actual time=0.392..627.264 rows=6,923 loops=1)

6. 4.501 591.684 ↓ 6,923.0 6,923 1

Nested Loop (cost=1.31..483.89 rows=1 width=20) (actual time=0.355..591.684 rows=6,923 loops=1)

7. 8.043 140.128 ↓ 5,322.1 63,865 1

Nested Loop (cost=0.87..392.06 rows=12 width=8) (actual time=0.037..140.128 rows=63,865 loops=1)

8. 0.091 0.091 ↑ 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.091 rows=6 loops=1)

  • Index Cond: (id = ANY ('{2731248,10230929,4116705,2492649,2278648,2779806}'::integer[]))
  • Heap Fetches: 1
9. 131.994 131.994 ↓ 145.8 10,644 6

Index Scan using index_label_links_on_label_id on label_links (cost=0.44..62.41 rows=73 width=8) (actual time=0.030..21.999 rows=10,644 loops=6)

  • Index Cond: (label_id = labels.id)
  • Filter: ((target_type)::text = 'Issue'::text)
  • Rows Removed by Filter: 3,073
10. 444.400 447.055 ↓ 0.0 0 63,865

Index Scan using issues_pkey on issues (cost=0.44..7.64 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=63,865)

  • 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) AND ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 4,156,052) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))))
  • Rows Removed by Filter: 1
11.          

SubPlan (for Index Scan)

12. 1.776 1.776 ↓ 0.0 0 296

Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on issue_assignees (cost=0.43..3.45 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=296)

  • Index Cond: ((issue_id = issues.id) AND (user_id = 4,156,052))
  • Heap Fetches: 3
13. 0.000 0.000 ↓ 0.0 0

Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..69.48 rows=65 width=4) (never executed)

  • Index Cond: (user_id = 4,156,052)
14. 0.879 0.879 ↑ 1.0 1 293

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.003..0.003 rows=1 loops=293)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = issues.project_id) AND (access_level >= 20))
  • Heap Fetches: 6
15. 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..7.91 rows=98 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 20))
  • Heap Fetches: 0
16. 20.769 34.615 ↑ 1.0 1 6,923

Index Scan using projects_pkey on projects (cost=0.43..4.34 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=6,923)

  • Index Cond: (id = issues.project_id)
  • Filter: ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (visibility_level = ANY ('{10,20}'::integer[])))
17.          

SubPlan (for Index Scan)

18. 13.846 13.846 ↑ 1.0 1 6,923

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.002..0.002 rows=1 loops=6,923)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 150
19. 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..7.97 rows=104 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 10))
  • Heap Fetches: 0
20. 13.846 13.846 ↑ 1.0 1 6,923

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.002 rows=1 loops=6,923)

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

SubPlan (for Nested Loop Left Join)

22. 0.046 0.046 ↑ 1.0 1 23

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

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 2
23. 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_5 (cost=0.56..7.97 rows=104 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 10))
  • Heap Fetches: 0
24. 0.021 0.796 ↑ 2.0 65 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
25. 0.775 0.775 ↑ 2.0 65 1

CTE Scan on base_and_descendants namespaces (cost=1,260.73..1,263.35 rows=131 width=4) (actual time=0.047..0.775 rows=65 loops=1)

26.          

CTE base_and_descendants

27. 0.147 0.686 ↑ 2.0 65 1

Recursive Union (cost=0.43..1,260.73 rows=131 width=322) (actual time=0.038..0.686 rows=65 loops=1)

28. 0.019 0.019 ↑ 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.018..0.019 rows=1 loops=1)

  • Index Cond: (id = 9,970)
  • Filter: ((type)::text = 'Group'::text)
29. 0.000 0.520 ↑ 1.0 13 5

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

30. 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)

31. 0.520 0.520 ↑ 1.0 1 65

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

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