explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aoID

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 134.808 ↑ 1.0 15 1

Limit (cost=15,045.98..15,046.02 rows=15 width=12) (actual time=134.796..134.808 rows=15 loops=1)

2. 0.100 134.796 ↑ 19.6 15 1

Sort (cost=15,045.98..15,046.72 rows=294 width=12) (actual time=134.794..134.796 rows=15 loops=1)

  • Sort Key: (count(label_links.label_id)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
3. 3.631 134.696 ↓ 2.6 750 1

GroupAggregate (cost=15,033.63..15,038.77 rows=294 width=12) (actual time=126.766..134.696 rows=750 loops=1)

  • Group Key: label_links.label_id
4. 12.127 131.065 ↓ 106.6 31,335 1

Sort (cost=15,033.63..15,034.36 rows=294 width=4) (actual time=126.757..131.065 rows=31,335 loops=1)

  • Sort Key: label_links.label_id
  • Sort Method: quicksort Memory: 2237kB
5. 6.260 118.938 ↓ 106.6 31,335 1

Nested Loop (cost=1,241.70..15,021.57 rows=294 width=4) (actual time=2.776..118.938 rows=31,335 loops=1)

6. 2.719 35.748 ↓ 25.8 7,693 1

Nested Loop Left Join (cost=1,241.13..14,720.74 rows=298 width=4) (actual time=2.711..35.748 rows=7,693 loops=1)

  • Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
7. 0.815 17.643 ↓ 25.3 7,693 1

Nested Loop (cost=1,240.70..14,572.28 rows=304 width=8) (actual time=2.692..17.643 rows=7,693 loops=1)

8. 0.153 5.404 ↑ 2.9 714 1

Nested Loop (cost=1,240.14..12,701.97 rows=2,050 width=4) (actual time=2.670..5.404 rows=714 loops=1)

9. 0.060 0.974 ↑ 1.4 91 1

HashAggregate (cost=1,239.70..1,241.01 rows=131 width=4) (actual time=0.934..0.974 rows=91 loops=1)

  • Group Key: namespaces.id
10. 0.914 0.914 ↑ 1.4 91 1

CTE Scan on base_and_descendants namespaces (cost=1,235.44..1,238.06 rows=131 width=4) (actual time=0.027..0.914 rows=91 loops=1)

11.          

CTE base_and_descendants

12. 0.174 0.816 ↑ 1.4 91 1

Recursive Union (cost=0.43..1,235.44 rows=131 width=323) (actual time=0.024..0.816 rows=91 loops=1)

13. 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.016..0.017 rows=1 loops=1)

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
14. 0.069 0.625 ↓ 1.4 18 5

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

15. 0.010 0.010 ↓ 1.8 18 5

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

16. 0.546 0.546 ↑ 1.0 1 91

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

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
17. 3.139 4.277 ↑ 2.0 8 91

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..87.33 rows=16 width=8) (actual time=0.027..0.047 rows=8 loops=91)

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

SubPlan (for Index Scan)

19. 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
20. 1.138 1.138 ↓ 10.5 2,217 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.56..56.66 rows=211 width=4) (actual time=0.021..1.138 rows=2,217 loops=1)

  • Index Cond: (user_id = 4156052)
  • Heap Fetches: 313
21. 11.424 11.424 ↓ 1.1 11 714

Index Only Scan using idx_issues_on_project_id_and_created_at_and_id_and_state_id on issues (cost=0.56..0.81 rows=10 width=8) (actual time=0.006..0.016 rows=11 loops=714)

  • Index Cond: ((project_id = projects.id) AND (created_at <= '2020-02-20 00:00:00+00'::timestamp with time zone) AND (created_at >= '2019-12-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 991
22. 15.386 15.386 ↑ 1.0 1 7,693

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=7,693)

  • Index Cond: (projects.id = project_id)
23. 76.930 76.930 ↓ 1.3 4 7,693

Index Scan using index_label_links_on_target_id_and_target_type on label_links (cost=0.56..0.98 rows=3 width=8) (actual time=0.007..0.010 rows=4 loops=7,693)

  • Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
Planning time : 2.595 ms
Execution time : 135.281 ms