explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bkKS

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 0.147 ↓ 2.0 2 1

GroupAggregate (cost=34.57..34.59 rows=1 width=10) (actual time=0.146..0.147 rows=2 loops=1)

  • Group Key: issues.state_id
2. 0.007 0.143 ↓ 11.0 11 1

Sort (cost=34.57..34.57 rows=1 width=2) (actual time=0.143..0.143 rows=11 loops=1)

  • Sort Key: issues.state_id
  • Sort Method: quicksort Memory: 25kB
3. 0.002 0.136 ↓ 11.0 11 1

Nested Loop Semi Join (cost=29.48..34.56 rows=1 width=2) (actual time=0.097..0.136 rows=11 loops=1)

  • Join Filter: (projects.namespace_id = namespaces.id)
4. 0.012 0.112 ↓ 11.0 11 1

Hash Left Join (cost=7.77..12.38 rows=1 width=6) (actual time=0.078..0.112 rows=11 loops=1)

  • Hash Cond: (projects.id = project_features.project_id)
  • Filter: ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
5. 0.005 0.088 ↓ 11.0 11 1

Nested Loop (cost=6.36..10.97 rows=1 width=10) (actual time=0.057..0.088 rows=11 loops=1)

6. 0.003 0.050 ↓ 11.0 11 1

Nested Loop (cost=6.22..8.43 rows=1 width=6) (actual time=0.032..0.050 rows=11 loops=1)

7. 0.010 0.025 ↓ 11.0 11 1

HashAggregate (cost=5.95..6.13 rows=1 width=4) (actual time=0.023..0.025 rows=11 loops=1)

  • Group Key: label_links.target_id
  • Filter: (count(1) = 2)
8. 0.015 0.015 ↓ 1.5 22 1

Index Scan using index_label_links_on_label_id on label_links (cost=0.15..5.88 rows=15 width=4) (actual time=0.009..0.015 rows=22 loops=1)

  • Index Cond: (label_id = ANY ('{71,75}'::integer[]))
  • Filter: ((target_type)::text = 'Issue'::text)
  • Rows Removed by Filter: 8
9. 0.022 0.022 ↑ 1.0 1 11

Index Scan using issues_pkey on issues (cost=0.27..2.29 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=11)

  • Index Cond: (id = label_links.target_id)
10. 0.021 0.033 ↑ 1.0 1 11

Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects (cost=0.14..2.33 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=11)

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

SubPlan (for Index Scan)

12. 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.14..2.17 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 1) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
13. 0.012 0.012 ↓ 1.2 10 1

Seq Scan on project_authorizations project_authorizations_1 (cost=0.00..2.09 rows=8 width=4) (actual time=0.007..0.012 rows=10 loops=1)

  • Filter: ((access_level >= 10) AND (user_id = 1))
  • Rows Removed by Filter: 68
14. 0.006 0.012 ↓ 1.2 21 1

Hash (cost=1.18..1.18 rows=18 width=8) (actual time=0.012..0.012 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.006 0.006 ↓ 1.2 21 1

Seq Scan on project_features (cost=0.00..1.18 rows=18 width=8) (actual time=0.003..0.006 rows=21 loops=1)

16.          

SubPlan (for Hash Left Join)

17. 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_2 (cost=0.14..2.17 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 1) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on project_authorizations project_authorizations_3 (cost=0.00..2.09 rows=8 width=4) (never executed)

  • Filter: ((access_level >= 10) AND (user_id = 1))
19. 0.022 0.022 ↑ 11.0 1 11

CTE Scan on base_and_descendants namespaces (cost=21.71..21.93 rows=11 width=4) (actual time=0.002..0.002 rows=1 loops=11)

20.          

CTE base_and_descendants

21. 0.005 0.014 ↑ 11.0 1 1

Recursive Union (cost=0.00..21.71 rows=11 width=389) (actual time=0.014..0.014 rows=1 loops=1)

22. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on namespaces namespaces_1 (cost=0.00..1.77 rows=1 width=389) (actual time=0.009..0.009 rows=1 loops=1)

  • Filter: (((type)::text = 'Group'::text) AND (id = 22))
  • Rows Removed by Filter: 45
23. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.73..1.97 rows=1 width=389) (never executed)

  • Hash Cond: (base_and_descendants.id = namespaces_2.parent_id)
24. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.64..1.64 rows=7 width=389) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Seq Scan on namespaces namespaces_2 (cost=0.00..1.64 rows=7 width=389) (never executed)

  • Filter: ((type)::text = 'Group'::text)