explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NX3R

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 39.633 ↓ 2.5 10 1

Limit (cost=15,323.64..15,323.77 rows=4 width=91) (actual time=39.575..39.633 rows=10 loops=1)

2. 0.038 39.622 ↓ 2.5 10 1

Unique (cost=15,323.64..15,323.77 rows=4 width=91) (actual time=39.574..39.622 rows=10 loops=1)

3. 0.584 39.584 ↓ 25.5 102 1

Sort (cost=15,323.64..15,323.65 rows=4 width=91) (actual time=39.574..39.584 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.220 39.000 ↓ 83.0 332 1

WindowAgg (cost=15,323.53..15,323.60 rows=4 width=91) (actual time=38.749..39.000 rows=332 loops=1)

5. 0.334 38.780 ↓ 83.0 332 1

Sort (cost=15,323.53..15,323.54 rows=4 width=83) (actual time=38.734..38.780 rows=332 loops=1)

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

Nested Loop (cost=14,779.36..15,323.49 rows=4 width=83) (actual time=35.888..38.446 rows=332 loops=1)

7. 0.025 36.888 ↓ 1.7 348 1

Nested Loop (cost=14,778.92..15,226.92 rows=203 width=4) (actual time=35.863..36.888 rows=348 loops=1)

8. 0.056 35.863 ↑ 1.0 100 1

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

  • Group Key: issues.id
9. 0.011 35.807 ↑ 1.0 100 1

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

10. 1.526 35.796 ↑ 2.9 100 1

Sort (cost=14,776.86..14,777.59 rows=294 width=4) (actual time=35.781..35.796 rows=100 loops=1)

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

Nested Loop Left Join (cost=1,244.94..14,765.62 rows=294 width=4) (actual time=1.184..34.270 rows=7,041 loops=1)

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

Nested Loop (cost=1,244.51..14,619.55 rows=300 width=8) (actual time=1.163..19.163 rows=7,041 loops=1)

13. 0.124 5.722 ↑ 3.0 715 1

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

14. 0.049 1.040 ↑ 1.5 86 1

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

  • Group Key: namespaces.id
15. 0.991 0.991 ↑ 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.037..0.991 rows=86 loops=1)

16.          

CTE base_and_descendants

17. 0.201 0.878 ↑ 1.5 86 1

Recursive Union (cost=0.43..1,239.25 rows=131 width=323) (actual time=0.034..0.878 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.048 0.660 ↓ 1.3 17 5

Nested Loop (cost=0.43..123.32 rows=13 width=323) (actual time=0.017..0.132 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.001..0.002 rows=17 loops=5)

21. 0.602 0.602 ↑ 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.005..0.007 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.012..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.870 12.870 ↓ 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.006..0.018 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: 2289
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. 1.000 1.000 ↓ 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.010 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.989 ms
Execution time : 40.027 ms