explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kKQ4

Settings
# exclusive inclusive rows x rows loops node
1. 15.715 174,256.006 ↑ 59.0 2 1

HashAggregate (cost=93,434.11..93,435.29 rows=118 width=10) (actual time=174,256.005..174,256.006 rows=2 loops=1)

  • Group Key: issues.state_id
2.          

CTE issues

3. 142.043 128,974.205 ↓ 61.0 132,943 1

Nested Loop (cost=1,639.41..93,378.78 rows=2,179 width=778) (actual time=234.181..128,974.205 rows=132,943 loops=1)

4. 6.338 2,849.987 ↑ 1.9 605 1

Nested Loop Left Join (cost=1,638.97..23,872.01 rows=1,160 width=4) (actual time=221.714..2,849.987 rows=605 loops=1)

  • 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)))
  • Rows Removed by Filter: 79
5. 1.063 949.879 ↑ 1.8 684 1

Nested Loop (cost=1,638.54..16,439.92 rows=1,217 width=4) (actual time=211.951..949.879 rows=684 loops=1)

6. 0.411 200.764 ↑ 1.4 92 1

HashAggregate (cost=1,638.10..1,639.41 rows=131 width=4) (actual time=200.530..200.764 rows=92 loops=1)

  • Group Key: namespaces_2.id
7. 200.353 200.353 ↑ 1.4 92 1

CTE Scan on base_and_descendants namespaces_2 (cost=1,633.84..1,636.46 rows=131 width=4) (actual time=3.138..200.353 rows=92 loops=1)

8.          

CTE base_and_descendants

9. 0.970 199.927 ↑ 1.4 92 1

Recursive Union (cost=0.43..1,633.84 rows=131 width=323) (actual time=3.135..199.927 rows=92 loops=1)

10. 3.087 3.087 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces (cost=0.43..4.45 rows=1 width=323) (actual time=3.085..3.087 rows=1 loops=1)

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
11. 0.275 195.870 ↓ 1.4 18 5

Nested Loop (cost=0.43..162.68 rows=13 width=323) (actual time=3.791..39.174 rows=18 loops=5)

12. 0.095 0.095 ↓ 1.8 18 5

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

13. 195.500 195.500 ↑ 1.0 1 92

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_1 (cost=0.43..16.24 rows=1 width=323) (actual time=1.223..2.125 rows=1 loops=92)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
14. 726.848 748.052 ↑ 1.3 7 92

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..112.89 rows=9 width=8) (actual time=2.340..8.131 rows=7 loops=92)

  • Index Cond: (namespace_id = namespaces_2.id)
  • Filter: ((NOT archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[]))))
  • Rows Removed by Filter: 0
15.          

SubPlan (for Index Scan)

16. 21.204 21.204 ↑ 1.0 1 684

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..4.59 rows=1 width=0) (actual time=0.031..0.031 rows=1 loops=684)

  • Index Cond: ((user_id = 1562869) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 105
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_1 (cost=0.56..62.56 rows=205 width=4) (never executed)

  • Index Cond: ((user_id = 1562869) AND (access_level >= 10))
  • Heap Fetches: 0
18. 1,478.808 1,478.808 ↑ 1.0 1 684

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..1.50 rows=1 width=8) (actual time=2.159..2.162 rows=1 loops=684)

  • Index Cond: (projects.id = project_id)
19.          

SubPlan (for Nested Loop Left Join)

20. 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.56..4.59 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 1562869) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
21. 414.962 414.962 ↓ 10.9 2,230 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.56..62.56 rows=205 width=4) (actual time=1.702..414.962 rows=2,230 loops=1)

  • Index Cond: ((user_id = 1562869) AND (access_level >= 10))
  • Heap Fetches: 403
22. 125,982.175 125,982.175 ↓ 1.8 220 605

Index Scan using index_issues_on_project_id_and_iid on issues issues_1 (cost=0.44..58.67 rows=125 width=778) (actual time=1.514..208.235 rows=220 loops=605)

  • Index Cond: (project_id = projects.id)
23. 174,240.291 174,240.291 ↓ 11.7 1,998 1

CTE Scan on issues (cost=0.00..54.48 rows=171 width=2) (actual time=372.020..174,240.291 rows=1,998 loops=1)

  • Filter: (((title)::text ~~* '%foo%'::text) OR (description ~~* '%foo%'::text))
  • Rows Removed by Filter: 130945
Planning time : 401.734 ms
Execution time : 174,263.916 ms