explain.depesz.com

PostgreSQL's explain analyze made readable

Result: okMx

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 40.133 ↓ 2.5 10 1

Limit (cost=15,323.65..15,323.78 rows=4 width=91) (actual time=40.067..40.133 rows=10 loops=1)

2. 0.045 40.118 ↓ 2.5 10 1

Unique (cost=15,323.65..15,323.78 rows=4 width=91) (actual time=40.066..40.118 rows=10 loops=1)

3. 0.732 40.073 ↓ 25.5 102 1

Sort (cost=15,323.65..15,323.66 rows=4 width=91) (actual time=40.065..40.073 rows=102 loops=1)

  • Sort Key: (count(labels.id) OVER (?)) DESC, labels.id, labels.title, labels.color, labels.project_id, labels.created_at, labels.updated_at, labels.template, labels.description, labels.description_html, labels.group_id, labels.cached_markdown_version
  • Sort Method: quicksort Memory: 225kB
4. 0.265 39.341 ↓ 83.0 332 1

WindowAgg (cost=15,323.54..15,323.61 rows=4 width=91) (actual time=39.038..39.341 rows=332 loops=1)

5. 0.348 39.076 ↓ 83.0 332 1

Sort (cost=15,323.54..15,323.55 rows=4 width=83) (actual time=39.020..39.076 rows=332 loops=1)

  • Sort Key: labels.id
  • Sort Method: quicksort Memory: 220kB
6. 0.201 38.728 ↓ 83.0 332 1

Nested Loop (cost=14,779.36..15,323.50 rows=4 width=83) (actual time=36.156..38.728 rows=332 loops=1)

7. 0.095 37.135 ↓ 1.7 348 1

Nested Loop (cost=14,778.93..15,226.93 rows=203 width=4) (actual time=36.135..37.135 rows=348 loops=1)

8. 0.062 36.140 ↑ 1.0 100 1

HashAggregate (cost=14,778.36..14,779.36 rows=100 width=4) (actual time=36.114..36.140 rows=100 loops=1)

  • Group Key: issues.id
9. 0.012 36.078 ↑ 1.0 100 1

Limit (cost=14,776.86..14,777.11 rows=100 width=4) (actual time=36.059..36.078 rows=100 loops=1)

10. 1.523 36.066 ↑ 2.9 100 1

Sort (cost=14,776.86..14,777.60 rows=294 width=4) (actual time=36.058..36.066 rows=100 loops=1)

  • Sort Key: issues.id DESC
  • Sort Method: top-N heapsort Memory: 29kB
11. 1.532 34.543 ↓ 23.9 7,041 1

Nested Loop Left Join (cost=1,244.94..14,765.63 rows=294 width=4) (actual time=1.005..34.543 rows=7,041 loops=1)

  • Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
12. 1.133 18.929 ↓ 23.5 7,041 1

Nested Loop (cost=1,244.51..14,619.56 rows=300 width=8) (actual time=0.990..18.929 rows=7,041 loops=1)

13. 0.129 5.641 ↑ 3.0 715 1

Nested Loop (cost=1,243.94..12,703.40 rows=2,178 width=4) (actual time=0.972..5.641 rows=715 loops=1)

14. 0.049 0.954 ↑ 1.5 86 1

HashAggregate (cost=1,243.51..1,244.82 rows=131 width=4) (actual time=0.932..0.954 rows=86 loops=1)

  • Group Key: namespaces.id
15. 0.905 0.905 ↑ 1.5 86 1

CTE Scan on base_and_descendants namespaces (cost=1,239.25..1,241.87 rows=131 width=4) (actual time=0.032..0.905 rows=86 loops=1)

16.          

CTE base_and_descendants

17. 0.203 0.800 ↑ 1.5 86 1

Recursive Union (cost=0.43..1,239.25 rows=131 width=323) (actual time=0.030..0.800 rows=86 loops=1)

18. 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=323) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
19. 0.054 0.580 ↓ 1.3 17 5

Nested Loop (cost=0.43..123.32 rows=13 width=323) (actual time=0.016..0.116 rows=17 loops=5)

20. 0.010 0.010 ↓ 1.7 17 5

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

21. 0.516 0.516 ↑ 1.0 1 86

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..12.30 rows=1 width=323) (actual time=0.004..0.006 rows=1 loops=86)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
22. 4.558 4.558 ↑ 2.1 8 86

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..87.30 rows=17 width=8) (actual time=0.011..0.053 rows=8 loops=86)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[])))
  • -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..3.58 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=7
  • Index Cond: ((user_id = 4156052) AND (project_id = projects.id))
  • Heap Fetches: 190
  • -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.56..67.09 rows=216 width=4) (never execu
  • Index Cond: (user_id = 4156052)
  • Heap Fetches: 0
23. 12.155 12.155 ↓ 1.1 10 715

Index Only Scan using idx_issues_on_project_id_and_created_at_and_id_and_state_id on issues (cost=0.56..0.79 rows=9 width=8) (actual time=0.007..0.017 rows=10 loops=715)

  • Index Cond: ((project_id = projects.id) AND (created_at <= '2020-02-06 23:59:59.999999+00'::timestamp with time zone) AND (created_at >= '2019-12-07 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 2298
24. 14.082 14.082 ↑ 1.0 1 7,041

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=7,041)

  • Index Cond: (projects.id = project_id)
25. 0.900 0.900 ↓ 1.5 3 100

Index Scan using index_label_links_on_target_id_and_target_type on label_links (cost=0.56..4.46 rows=2 width=8) (actual time=0.006..0.009 rows=3 loops=100)

  • Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
26. 1.392 1.392 ↑ 1.0 1 348

Index Scan using labels_pkey on labels (cost=0.43..0.47 rows=1 width=83) (actual time=0.004..0.004 rows=1 loops=348)

  • Index Cond: (id = label_links.label_id)
  • Filter: ((type)::text = 'GroupLabel'::text)
  • Rows Removed by Filter: 0
Planning time : 2.887 ms
Execution time : 40.542 ms