explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2qk2

Settings
# exclusive inclusive rows x rows loops node
1. 0.228 1.599 ↓ 156.0 156 1

Nested Loop (cost=83.02..102.77 rows=1 width=16) (actual time=0.579..1.599 rows=156 loops=1)

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

Nested Loop (cost=83.02..101.75 rows=1 width=32) (actual time=0.573..1.371 rows=156 loops=1)

3. 0.023 1.163 ↓ 156.0 156 1

Nested Loop (cost=82.87..93.50 rows=1 width=32) (actual time=0.567..1.163 rows=156 loops=1)

4. 0.014 0.038 ↓ 29.0 29 1

Hash Join (cost=1.34..2.71 rows=1 width=96) (actual time=0.024..0.038 rows=29 loops=1)

  • Hash Cond: (person_assignment.organizational_unit_id = organizational_unit.id)
5. 0.008 0.008 ↑ 1.0 29 1

Seq Scan on person_assignment (cost=0.00..1.29 rows=29 width=64) (actual time=0.005..0.008 rows=29 loops=1)

6. 0.006 0.016 ↓ 26.0 26 1

Hash (cost=1.32..1.32 rows=1 width=64) (actual time=0.015..0.016 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
7. 0.010 0.010 ↓ 26.0 26 1

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

  • Filter: (state = 'ACTIVE'::text)
8. 0.123 1.102 ↓ 5.0 5 29

Nested Loop Anti Join (cost=81.54..90.77 rows=1 width=16) (actual time=0.019..0.038 rows=5 loops=29)

9. 0.029 0.667 ↓ 5.0 5 29

Subquery Scan on alias_755097 (cost=81.26..82.30 rows=1 width=16) (actual time=0.017..0.023 rows=5 loops=29)

  • Filter: (alias_755097.r = 1)
  • Rows Removed by Filter: 0
10. 0.145 0.638 ↑ 5.3 6 29

WindowAgg (cost=81.26..81.90 rows=32 width=44) (actual time=0.017..0.022 rows=6 loops=29)

11. 0.087 0.493 ↑ 5.3 6 29

Sort (cost=81.26..81.34 rows=32 width=20) (actual time=0.017..0.017 rows=6 loops=29)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 25kB
12. 0.019 0.406 ↑ 5.3 6 29

Result (cost=72.42..80.46 rows=32 width=20) (actual time=0.011..0.014 rows=6 loops=29)

  • One-Time Filter: ((person_assignment.state = 'ACTIVE'::text) AND (person_assignment.person_id = '8e459ecd-2208-44b9-836a-b9bbf182ec03'::uuid))
13. 0.045 0.387 ↓ 5.2 167 1

Hash Join (cost=72.42..80.46 rows=32 width=20) (actual time=0.314..0.387 rows=167 loops=1)

  • Hash Cond: (employee.organization_id = organization_assignment.organization_id)
14. 0.036 0.036 ↑ 1.0 167 1

Seq Scan on employee (cost=0.00..7.09 rows=167 width=36) (actual time=0.004..0.036 rows=167 loops=1)

  • Filter: (state = 'ACTIVE'::text)
15. 0.082 0.306 ↓ 5.2 448 1

Hash (cost=71.34..71.34 rows=86 width=16) (actual time=0.306..0.306 rows=448 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
16. 0.061 0.224 ↓ 5.2 448 1

Nested Loop (cost=19.75..71.34 rows=86 width=16) (actual time=0.036..0.224 rows=448 loops=1)

17. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on organizational_unit ou2 (cost=0.00..1.39 rows=1 width=16) (actual time=0.007..0.011 rows=1 loops=1)

  • Filter: ((materialized_path @> organizational_unit.materialized_path) AND (state = 'ACTIVE'::text))
  • Rows Removed by Filter: 25
18. 0.130 0.152 ↑ 1.0 448 1

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

  • Recheck Cond: ((organizational_unit_id = ou2.id) AND (state = 'ACTIVE'::text))
  • Heap Blocks: exact=39
19. 0.022 0.022 ↑ 1.0 448 1

Bitmap Index Scan on organizational_assignment_org_unit_id_org_id_idx (cost=0.00..19.64 rows=448 width=0) (actual time=0.022..0.022 rows=448 loops=1)

  • Index Cond: (organizational_unit_id = ou2.id)
20. 0.000 0.312 ↓ 0.0 0 156

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

21. 0.156 0.156 ↑ 1.0 1 156

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

  • Index Cond: (person_id = alias_755097.person_id)
22. 0.156 0.156 ↓ 0.0 0 156

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

  • Index Cond: (id = person_to_person_type.person_type_id)
  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 1
23. 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
24. 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.231 ms
Execution time : 1.676 ms