explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C4c9

Settings
# exclusive inclusive rows x rows loops node
1. 8.002 1,051.411 ↓ 1,500.0 3,000 1

Sort (cost=21,559.67..21,559.68 rows=2 width=1,295) (actual time=1,050.458..1,051.411 rows=3,000 loops=1)

  • Sort Key: ((SubPlan 1)), issues.id DESC
  • Sort Method: quicksort Memory: 5100kB
2. 2.668 1,043.409 ↓ 1,500.0 3,000 1

Group (cost=21,538.58..21,559.66 rows=2 width=1,295) (actual time=901.401..1,043.409 rows=3,000 loops=1)

  • Group Key: issues.id
3. 7.199 902.741 ↓ 1,500.0 3,000 1

Sort (cost=21,538.58..21,538.59 rows=2 width=1,291) (actual time=901.288..902.741 rows=3,000 loops=1)

  • Sort Key: issues.id DESC
  • Sort Method: quicksort Memory: 5095kB
4. 2.112 895.542 ↓ 1,500.0 3,000 1

Nested Loop (cost=21,531.93..21,538.57 rows=2 width=1,291) (actual time=871.532..895.542 rows=3,000 loops=1)

5. 0.331 872.430 ↓ 1,500.0 3,000 1

Limit (cost=21,531.37..21,531.37 rows=2 width=1,270) (actual time=871.497..872.430 rows=3,000 loops=1)

6. 7.052 872.099 ↓ 1,500.0 3,000 1

Sort (cost=21,531.37..21,531.37 rows=2 width=1,270) (actual time=871.494..872.099 rows=3,000 loops=1)

  • Sort Key: ((SubPlan 2)), issues_1.id DESC
  • Sort Method: top-N heapsort Memory: 333kB
7. 12.615 865.047 ↓ 6,826.0 13,652 1

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

  • Group Key: issues_1.id
8. 8.765 292.700 ↓ 6,826.0 13,652 1

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

  • Sort Key: issues_1.id DESC
  • Sort Method: quicksort Memory: 1024kB
9. 3.970 283.935 ↓ 6,826.0 13,652 1

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

  • Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
10. 1.980 252.661 ↓ 6,826.0 13,652 1

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

11. 0.173 6.262 ↑ 2.8 791 1

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

12. 0.074 1.013 ↑ 1.5 94 1

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

  • Group Key: namespaces.id
13. 0.939 0.939 ↑ 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.026..0.939 rows=94 loops=1)

14.          

CTE base_and_descendants

15. 0.199 0.853 ↑ 1.5 94 1

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

16. 0.014 0.014 ↑ 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.013..0.014 rows=1 loops=1)

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
17. 0.066 0.640 ↓ 1.4 19 5

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

18. 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.001..0.002 rows=19 loops=5)

19. 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)
20. 2.703 5.076 ↑ 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.054 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[])))
21.          

SubPlan (for Index Scan)

22. 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
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_1 (cost=0.57..58.53 rows=323 width=4) (never executed)

  • Index Cond: (user_id = 4156052)
  • Heap Fetches: 0
24. 244.419 244.419 ↓ 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.039..0.309 rows=17 loops=791)

  • Index Cond: (project_id = projects.id)
  • Filter: (milestone_id = 490705)
  • Rows Removed by Filter: 162
25. 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)
26.          

SubPlan (for Group)

27. 0.000 559.732 ↑ 1.0 1 13,652

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

28. 67.022 559.732 ↓ 0.0 0 13,652

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

29. 38.480 395.908 ↓ 3.5 7 13,652

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

30. 163.824 163.824 ↓ 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.012 rows=7 loops=13,652)

  • Index Cond: ((target_id = issues_1.id) AND ((target_type)::text = 'Issue'::text))
31. 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: 14075
32. 96.802 96.802 ↓ 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.001..0.001 rows=0 loops=96,802)

  • Index Cond: (label_id = labels_1.id)
  • Filter: (project_id = issues_1.project_id)
  • Rows Removed by Filter: 0
33. 21.000 21.000 ↑ 1.0 1 3,000

Index Scan using issues_pkey on issues (cost=0.56..3.58 rows=1 width=1,291) (actual time=0.007..0.007 rows=1 loops=3,000)

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

SubPlan (for Group)

35. 0.000 138.000 ↑ 1.0 1 3,000

Aggregate (cost=10.52..10.53 rows=1 width=4) (actual time=0.046..0.046 rows=1 loops=3,000)

36. 0.000 138.000 ↓ 0.0 0 3,000

Nested Loop (cost=1.29..10.52 rows=1 width=4) (actual time=0.041..0.046 rows=0 loops=3,000)

37. 12.576 99.000 ↓ 3.5 7 3,000

Nested Loop (cost=1.00..9.88 rows=2 width=8) (actual time=0.010..0.033 rows=7 loops=3,000)

38. 45.000 45.000 ↓ 3.5 7 3,000

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.008..0.015 rows=7 loops=3,000)

  • Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
39. 41.424 41.424 ↑ 1.0 1 20,712

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=20,712)

  • Index Cond: (id = label_links.label_id)
  • Heap Fetches: 2988
40. 41.424 41.424 ↓ 0.0 0 20,712

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=20,712)

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