explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sKdJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.249 23.833 ↓ 156.0 156 1

Nested Loop (cost=81.68..102.82 rows=1 width=16) (actual time=17.390..23.833 rows=156 loops=1)

  • Join Filter: (organizational_unit.organizational_hierarchy_id = organizational_hierarchy.id)
2. 0.147 23.584 ↓ 156.0 156 1

Nested Loop (cost=81.68..101.80 rows=1 width=32) (actual time=17.385..23.584 rows=156 loops=1)

3. 1.707 23.281 ↓ 156.0 156 1

Nested Loop (cost=81.53..93.55 rows=1 width=32) (actual time=17.372..23.281 rows=156 loops=1)

  • Join Filter: (organizational_unit.id = person_assignment.organizational_unit_id)
  • Rows Removed by Join Filter: 1716
4. 0.316 15.958 ↓ 1,872.0 1,872 1

Nested Loop (cost=81.53..92.10 rows=1 width=48) (actual time=1.356..15.958 rows=1,872 loops=1)

5. 0.016 0.016 ↓ 26.0 26 1

Seq Scan on organizational_unit (cost=0.00..1.32 rows=1 width=64) (actual time=0.005..0.016 rows=26 loops=1)

  • Filter: (state = 'ACTIVE'::text)
6. 1.170 15.626 ↓ 72.0 72 26

Nested Loop Anti Join (cost=81.53..90.77 rows=1 width=16) (actual time=0.302..0.601 rows=72 loops=26)

7. 0.416 10.712 ↓ 72.0 72 26

Subquery Scan on alias_755097 (cost=81.25..82.29 rows=1 width=16) (actual time=0.299..0.412 rows=72 loops=26)

  • Filter: (alias_755097.r = 1)
  • Rows Removed by Filter: 76
8. 2.262 10.296 ↓ 4.6 148 26

WindowAgg (cost=81.25..81.89 rows=32 width=44) (actual time=0.298..0.396 rows=148 loops=26)

9. 1.300 8.034 ↓ 4.6 148 26

Sort (cost=81.25..81.33 rows=32 width=20) (actual time=0.296..0.309 rows=148 loops=26)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 25kB
10. 0.758 6.734 ↓ 4.6 148 26

Hash Join (cost=72.42..80.45 rows=32 width=20) (actual time=0.221..0.259 rows=148 loops=26)

  • Hash Cond: (employee.organization_id = organization_assignment.organization_id)
11. 0.360 0.360 ↑ 1.0 167 12

Seq Scan on employee (cost=0.00..7.09 rows=167 width=36) (actual time=0.002..0.030 rows=167 loops=12)

  • Filter: (state = 'ACTIVE'::text)
12. 1.326 5.616 ↓ 4.6 396 26

Hash (cost=71.34..71.34 rows=86 width=16) (actual time=0.216..0.216 rows=396 loops=26)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
13. 1.398 4.290 ↓ 4.6 396 26

Nested Loop (cost=19.75..71.34 rows=86 width=16) (actual time=0.029..0.165 rows=396 loops=26)

14. 0.260 0.260 ↓ 4.0 4 26

Seq Scan on organizational_unit ou2 (cost=0.00..1.39 rows=1 width=16) (actual time=0.004..0.010 rows=4 loops=26)

  • Filter: ((materialized_path @> organizational_unit.materialized_path) AND (state = 'ACTIVE'::text))
  • Rows Removed by Filter: 22
15. 1.974 2.632 ↑ 4.1 110 94

Bitmap Heap Scan on organization_assignment (cost=19.75..65.47 rows=448 width=32) (actual time=0.008..0.028 rows=110 loops=94)

  • Recheck Cond: ((organizational_unit_id = ou2.id) AND (state = 'ACTIVE'::text))
  • Heap Blocks: exact=897
16. 0.658 0.658 ↑ 4.1 110 94

Bitmap Index Scan on organizational_assignment_org_unit_id_org_id_idx (cost=0.00..19.64 rows=448 width=0) (actual time=0.007..0.007 rows=110 loops=94)

  • Index Cond: (organizational_unit_id = ou2.id)
17. 0.000 3.744 ↓ 0.0 0 1,872

Nested Loop (cost=0.28..8.47 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1,872)

18. 1.872 1.872 ↑ 1.0 1 1,872

Index Scan using person_to_person_type_person_idx on person_to_person_type (cost=0.14..8.16 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1,872)

  • Index Cond: (person_id = alias_755097.person_id)
19. 1.872 1.872 ↓ 0.0 0 1,872

Index Scan using person_type_pkey on person_type (cost=0.14..0.22 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1,872)

  • Index Cond: (id = person_to_person_type.person_type_id)
  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 1
20. 5.616 5.616 ↑ 1.0 1 1,872

Seq Scan on person_assignment (cost=0.00..1.44 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,872)

  • Filter: ((state = 'ACTIVE'::text) AND (person_id = '8e459ecd-2208-44b9-836a-b9bbf182ec03'::uuid))
  • Rows Removed by Filter: 28
21. 0.156 0.156 ↑ 1.0 1 156

Index Only Scan using person_id_state_idx on person (cost=0.14..8.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=156)

  • Index Cond: (id = alias_755097.person_id)
  • Heap Fetches: 156
22. 0.000 0.000 ↑ 1.0 1 156

Seq Scan on organizational_hierarchy (cost=0.00..1.01 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=156)

  • Filter: (state = 'ACTIVE'::text)
Planning time : 1.244 ms
Execution time : 23.911 ms