explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CYJ4

Settings
# exclusive inclusive rows x rows loops node
1. 0.128 25.589 ↓ 34.2 137 1

Unique (cost=14,877.29..14,877.42 rows=4 width=91) (actual time=25.429..25.589 rows=137 loops=1)

2. 0.524 25.461 ↓ 83.2 333 1

Sort (cost=14,877.29..14,877.30 rows=4 width=91) (actual time=25.428..25.461 rows=333 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: 227kB
3. 0.198 24.937 ↓ 83.2 333 1

WindowAgg (cost=14,877.18..14,877.25 rows=4 width=91) (actual time=24.688..24.937 rows=333 loops=1)

4. 0.252 24.739 ↓ 83.2 333 1

Sort (cost=14,877.18..14,877.19 rows=4 width=83) (actual time=24.677..24.739 rows=333 loops=1)

  • Sort Key: labels.id
  • Sort Method: quicksort Memory: 222kB
5. 0.090 24.487 ↓ 83.2 333 1

Nested Loop (cost=14,332.42..14,877.14 rows=4 width=83) (actual time=22.023..24.487 rows=333 loops=1)

6. 0.102 23.001 ↓ 1.7 349 1

Nested Loop (cost=14,331.99..14,780.08 rows=204 width=4) (actual time=22.004..23.001 rows=349 loops=1)

7. 0.038 21.999 ↑ 1.0 100 1

HashAggregate (cost=14,331.43..14,332.43 rows=100 width=4) (actual time=21.984..21.999 rows=100 loops=1)

  • Group Key: issues.id
8. 0.015 21.961 ↑ 1.0 100 1

Limit (cost=14,329.93..14,330.18 rows=100 width=4) (actual time=21.937..21.961 rows=100 loops=1)

9. 0.735 21.946 ↑ 1.4 100 1

Sort (cost=14,329.93..14,330.28 rows=141 width=4) (actual time=21.936..21.946 rows=100 loops=1)

  • Sort Key: issues.id DESC
  • Sort Method: top-N heapsort Memory: 29kB
10. 0.649 21.211 ↓ 31.3 4,414 1

Nested Loop Left Join (cost=1,241.13..14,324.89 rows=141 width=4) (actual time=1.949..21.211 rows=4,414 loops=1)

  • Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
11. 0.847 11.734 ↓ 30.7 4,414 1

Nested Loop (cost=1,240.70..14,254.78 rows=144 width=8) (actual time=1.933..11.734 rows=4,414 loops=1)

12. 0.089 4.452 ↑ 3.0 715 1

Nested Loop (cost=1,240.14..12,706.31 rows=2,172 width=4) (actual time=1.914..4.452 rows=715 loops=1)

13. 0.060 0.923 ↑ 1.5 86 1

HashAggregate (cost=1,239.70..1,241.01 rows=131 width=4) (actual time=0.889..0.923 rows=86 loops=1)

  • Group Key: namespaces.id
14. 0.863 0.863 ↑ 1.5 86 1

CTE Scan on base_and_descendants namespaces (cost=1,235.44..1,238.06 rows=131 width=4) (actual time=0.024..0.863 rows=86 loops=1)

15.          

CTE base_and_descendants

16. 0.174 0.774 ↑ 1.5 86 1

Recursive Union (cost=0.43..1,235.44 rows=131 width=323) (actual time=0.022..0.774 rows=86 loops=1)

17. 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=323) (actual time=0.014..0.015 rows=1 loops=1)

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
18. 0.064 0.585 ↓ 1.3 17 5

Nested Loop (cost=0.43..122.94 rows=13 width=323) (actual time=0.015..0.117 rows=17 loops=5)

19. 0.005 0.005 ↓ 1.7 17 5

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

20. 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.26 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)
21. 2.914 3.440 ↑ 2.1 8 86

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..87.35 rows=17 width=8) (actual time=0.020..0.040 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[])))
22.          

SubPlan (for Index Scan)

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 (cost=0.56..3.58 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 4156052) AND (project_id = projects.id))
  • Heap Fetches: 0
24. 0.526 0.526 ↓ 10.4 2,222 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.56..8.79 rows=213 width=4) (actual time=0.019..0.526 rows=2,222 loops=1)

  • Index Cond: (user_id = 4156052)
  • Heap Fetches: 2
25. 6.435 6.435 ↓ 1.5 6 715

Index Only Scan using idx_issues_on_project_id_and_created_at_and_id_and_state_id on issues (cost=0.56..0.67 rows=4 width=8) (actual time=0.006..0.009 rows=6 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 >= '2020-01-07 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 298
26. 8.828 8.828 ↑ 1.0 1 4,414

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=4,414)

  • Index Cond: (projects.id = project_id)
27. 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))
28. 1.396 1.396 ↑ 1.0 1 349

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=349)

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