explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xmpj

Settings
# exclusive inclusive rows x rows loops node
1. 50.362 1,694.399 ↓ 6,826.0 13,652 1

Sort (cost=21,559.67..21,559.68 rows=2 width=1,295) (actual time=1,690.329..1,694.399 rows=13,652 loops=1)

  • Sort Key: ((SubPlan 1)), issues.id DESC
  • Sort Method: quicksort Memory: 23874kB
2. 12.627 1,644.037 ↓ 6,826.0 13,652 1

Group (cost=21,538.59..21,559.66 rows=2 width=1,295) (actual time=1,012.333..1,644.037 rows=13,652 loops=1)

  • Group Key: issues.id
3. 44.897 1,030.722 ↓ 6,826.0 13,652 1

Sort (cost=21,538.59..21,538.59 rows=2 width=1,291) (actual time=1,012.177..1,030.722 rows=13,652 loops=1)

  • Sort Key: issues.id DESC
  • Sort Method: quicksort Memory: 23870kB
4. 4.474 985.825 ↓ 6,826.0 13,652 1

Nested Loop (cost=21,531.93..21,538.58 rows=2 width=1,291) (actual time=896.573..985.825 rows=13,652 loops=1)

5. 12.539 899.439 ↓ 6,826.0 13,652 1

Sort (cost=21,531.37..21,531.38 rows=2 width=1,270) (actual time=896.531..899.439 rows=13,652 loops=1)

  • Sort Key: ((SubPlan 2)), issues_1.id DESC
  • Sort Method: quicksort Memory: 1024kB
6. 7.532 886.900 ↓ 6,826.0 13,652 1

Group (cost=21,510.28..21,531.36 rows=2 width=1,270) (actual time=275.070..886.900 rows=13,652 loops=1)

  • Group Key: issues_1.id
7. 7.721 278.680 ↓ 6,826.0 13,652 1

Sort (cost=21,510.28..21,510.29 rows=2 width=8) (actual time=274.972..278.680 rows=13,652 loops=1)

  • Sort Key: issues_1.id DESC
  • Sort Method: quicksort Memory: 1024kB
8. 4.524 270.959 ↓ 6,826.0 13,652 1

Nested Loop Left Join (cost=1,258.39..21,510.27 rows=2 width=8) (actual time=1.065..270.959 rows=13,652 loops=1)

  • Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
9. 1.938 239.131 ↓ 6,826.0 13,652 1

Nested Loop (cost=1,257.95..21,509.29 rows=2 width=12) (actual time=1.053..239.131 rows=13,652 loops=1)

10. 0.091 6.221 ↑ 2.8 791 1

Nested Loop (cost=1,257.39..13,593.13 rows=2,223 width=4) (actual time=0.958..6.221 rows=791 loops=1)

11. 0.067 0.960 ↑ 1.5 94 1

HashAggregate (cost=1,256.95..1,258.36 rows=141 width=4) (actual time=0.917..0.960 rows=94 loops=1)

  • Group Key: namespaces.id
12. 0.893 0.893 ↑ 1.5 94 1

CTE Scan on base_and_descendants namespaces (cost=1,252.37..1,255.19 rows=141 width=4) (actual time=0.024..0.893 rows=94 loops=1)

13.          

CTE base_and_descendants

14. 0.180 0.800 ↑ 1.5 94 1

Recursive Union (cost=0.43..1,252.37 rows=141 width=326) (actual time=0.022..0.800 rows=94 loops=1)

15. 0.015 0.015 ↑ 1.0 1 1

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

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
16. 0.031 0.605 ↓ 1.4 19 5

Nested Loop (cost=0.43..124.61 rows=14 width=326) (actual time=0.014..0.121 rows=19 loops=5)

17. 0.010 0.010 ↓ 1.9 19 5

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

18. 0.564 0.564 ↑ 1.0 1 94

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..12.43 rows=1 width=326) (actual time=0.004..0.006 rows=1 loops=94)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
19. 2.797 5.170 ↑ 2.0 8 94

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..87.32 rows=16 width=8) (actual time=0.011..0.055 rows=8 loops=94)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: ((alternatives: SubPlan 3 or hashed SubPlan 4) OR (visibility_level = ANY ('{0,10,20}'::integer[])))
20.          

SubPlan (for Index Scan)

21. 2.373 2.373 ↑ 1.0 1 791

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

  • Index Cond: ((user_id = 4156052) AND (project_id = projects.id))
  • Heap Fetches: 80
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_1 (cost=0.57..58.53 rows=323 width=4) (never executed)

  • Index Cond: (user_id = 4156052)
  • Heap Fetches: 0
23. 230.972 230.972 ↓ 17.0 17 791

Index Scan using index_issues_on_project_id_and_iid on issues issues_1 (cost=0.56..3.55 rows=1 width=8) (actual time=0.037..0.292 rows=17 loops=791)

  • Index Cond: (project_id = projects.id)
  • Filter: (milestone_id = 490705)
  • Rows Removed by Filter: 162
24. 27.304 27.304 ↑ 1.0 1 13,652

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.48 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=13,652)

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

SubPlan (for Group)

26. 13.652 600.688 ↑ 1.0 1 13,652

Aggregate (cost=10.52..10.53 rows=1 width=4) (actual time=0.044..0.044 rows=1 loops=13,652)

27. 0.000 587.036 ↓ 0.0 0 13,652

Nested Loop (cost=1.29..10.52 rows=1 width=4) (actual time=0.042..0.043 rows=0 loops=13,652)

28. 52.132 423.212 ↓ 3.5 7 13,652

Nested Loop (cost=1.00..9.88 rows=2 width=8) (actual time=0.009..0.031 rows=7 loops=13,652)

29. 177.476 177.476 ↓ 3.5 7 13,652

Index Scan using index_label_links_on_target_id_and_target_type on label_links label_links_1 (cost=0.56..4.45 rows=2 width=4) (actual time=0.006..0.013 rows=7 loops=13,652)

  • Index Cond: ((target_id = issues_1.id) AND ((target_type)::text = 'Issue'::text))
30. 193.604 193.604 ↑ 1.0 1 96,802

Index Only Scan using labels_pkey on labels labels_1 (cost=0.43..2.70 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=96,802)

  • Index Cond: (id = label_links_1.label_id)
  • Heap Fetches: 14157
31. 193.604 193.604 ↓ 0.0 0 96,802

Index Scan using index_label_priorities_on_label_id on label_priorities label_priorities_1 (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=96,802)

  • Index Cond: (label_id = labels_1.id)
  • Filter: (project_id = issues_1.project_id)
  • Rows Removed by Filter: 0
32. 81.912 81.912 ↑ 1.0 1 13,652

Index Scan using issues_pkey on issues (cost=0.56..3.58 rows=1 width=1,291) (actual time=0.006..0.006 rows=1 loops=13,652)

  • Index Cond: (id = issues_1.id)
33.          

SubPlan (for Group)

34. 0.000 600.688 ↑ 1.0 1 13,652

Aggregate (cost=10.52..10.53 rows=1 width=4) (actual time=0.044..0.044 rows=1 loops=13,652)

35. 0.000 600.688 ↓ 0.0 0 13,652

Nested Loop (cost=1.29..10.52 rows=1 width=4) (actual time=0.043..0.044 rows=0 loops=13,652)

36. 52.132 423.212 ↓ 3.5 7 13,652

Nested Loop (cost=1.00..9.88 rows=2 width=8) (actual time=0.009..0.031 rows=7 loops=13,652)

37. 177.476 177.476 ↓ 3.5 7 13,652

Index Scan using index_label_links_on_target_id_and_target_type on label_links (cost=0.56..4.45 rows=2 width=4) (actual time=0.006..0.013 rows=7 loops=13,652)

  • Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
38. 193.604 193.604 ↑ 1.0 1 96,802

Index Only Scan using labels_pkey on labels (cost=0.43..2.70 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=96,802)

  • Index Cond: (id = label_links.label_id)
  • Heap Fetches: 14157
39. 193.604 193.604 ↓ 0.0 0 96,802

Index Scan using index_label_priorities_on_label_id on label_priorities (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=96,802)

  • Index Cond: (label_id = labels.id)
  • Filter: (project_id = issues.project_id)
  • Rows Removed by Filter: 0
Planning time : 19.824 ms
Execution time : 1,698.993 ms