explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TZZo

Settings
# exclusive inclusive rows x rows loops node
1. 2.393 1,727.273 ↑ 1.0 2 1

GroupAggregate (cost=31,566.01..31,566.06 rows=2 width=10) (actual time=1,724.704..1,727.273 rows=2 loops=1)

  • Group Key: issues.state_id
2. 6.987 1,724.880 ↓ 7,932.7 23,798 1

Sort (cost=31,566.01..31,566.02 rows=3 width=2) (actual time=1,723.289..1,724.880 rows=23,798 loops=1)

  • Sort Key: issues.state_id
  • Sort Method: quicksort Memory: 1,884kB
3. 7.912 1,717.893 ↓ 7,932.7 23,798 1

Nested Loop Left Join (cost=1,420.33..31,565.99 rows=3 width=2) (actual time=14.159..1,717.893 rows=23,798 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: 85
4. 91.321 1,661.735 ↓ 7,961.0 23,883 1

Nested Loop Semi Join (cost=1,419.89..31,553.69 rows=3 width=6) (actual time=14.141..1,661.735 rows=23,883 loops=1)

5. 19.391 126.220 ↓ 35.6 160,466 1

Nested Loop (cost=1,419.33..28,268.52 rows=4,502 width=10) (actual time=1.284..126.220 rows=160,466 loops=1)

6. 0.228 9.029 ↑ 1.5 978 1

Nested Loop (cost=1,418.76..14,706.18 rows=1,499 width=4) (actual time=1.264..9.029 rows=978 loops=1)

7. 0.107 1.301 ↑ 1.2 125 1

HashAggregate (cost=1,418.33..1,419.84 rows=151 width=4) (actual time=1.228..1.301 rows=125 loops=1)

  • Group Key: namespaces.id
8. 1.194 1.194 ↑ 1.2 125 1

CTE Scan on base_and_descendants namespaces (cost=1,413.42..1,416.44 rows=151 width=4) (actual time=0.025..1.194 rows=125 loops=1)

9.          

CTE base_and_descendants

10. 0.247 1.050 ↑ 1.2 125 1

Recursive Union (cost=0.43..1,413.42 rows=151 width=329) (actual time=0.023..1.050 rows=125 loops=1)

11. 0.018 0.018 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=329) (actual time=0.018..0.018 rows=1 loops=1)

  • Index Cond: (id = 9,970)
  • Filter: ((type)::text = 'Group'::text)
12. 0.025 0.785 ↓ 1.7 25 5

Nested Loop (cost=0.56..140.69 rows=15 width=329) (actual time=0.016..0.157 rows=25 loops=5)

13. 0.010 0.010 ↓ 2.5 25 5

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

14. 0.750 0.750 ↑ 1.0 1 125

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..14.04 rows=1 width=329) (actual time=0.004..0.006 rows=1 loops=125)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
15. 3.588 7.500 ↑ 1.2 8 125

Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.43..87.89 rows=10 width=8) (actual time=0.011..0.060 rows=8 loops=125)

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

SubPlan (for Index Scan)

17. 3.912 3.912 ↑ 1.0 1 978

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=978)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 242
18. 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.57..264.83 rows=1,228 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 10))
  • Heap Fetches: 0
19. 97.800 97.800 ↑ 1.9 164 978

Index Only Scan using idx_issues_on_project_id_and_rel_position_and_state_id_and_id on issues (cost=0.56..5.99 rows=306 width=10) (actual time=0.007..0.100 rows=164 loops=978)

  • Index Cond: (project_id = projects.id)
  • Heap Fetches: 13,653
20. 1,444.194 1,444.194 ↓ 0.0 0 160,466

Index Scan using index_label_links_on_target_id_and_target_type on label_links (cost=0.56..0.73 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=160,466)

  • Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
  • Filter: (label_id = ANY ('{1924053,2191076,2229310,2251841,2278648,2379994,2414262,3519299,3791725,3892549,3892770,3899495,3969396,3999541,3999564,4007552,4007738,4049204,4057321,4063693,4117093,6190961,7142293,7157910,7256789,7717873,7841215,8761704,8827899,8828075,8828148,9084503,10778837,10778846,11602405,12073682,12932265,13682327,15339054}'::integer[]))
  • Rows Removed by Filter: 3
21. 47.766 47.766 ↑ 1.0 1 23,883

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=23,883)

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

SubPlan (for Nested Loop Left Join)

23. 0.480 0.480 ↑ 1.0 1 240

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=240)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 2
24. 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_3 (cost=0.57..264.83 rows=1,228 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 10))
  • Heap Fetches: 0
Planning time : 5.079 ms
Execution time : 1,727.553 ms