explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ne7v

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1.564 ↓ 156.0 156 1

Nested Loop (cost=89.95..124.32 rows=1 width=16) (actual time=0.789..1.564 rows=156 loops=1)

2. 0.013 1.420 ↓ 156.0 156 1

Nested Loop (cost=89.80..116.14 rows=1 width=32) (actual time=0.787..1.420 rows=156 loops=1)

3. 0.035 1.251 ↓ 156.0 156 1

Nested Loop (cost=89.66..107.96 rows=1 width=32) (actual time=0.784..1.251 rows=156 loops=1)

4. 0.006 0.027 ↓ 29.0 29 1

Hash Join (cost=8.18..9.66 rows=1 width=71) (actual time=0.024..0.027 rows=29 loops=1)

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

Seq Scan on person_assignment (cost=0.00..1.36 rows=29 width=39) (actual time=0.004..0.005 rows=29 loops=1)

  • Filter: (state = 'ACTIVE'::text)
6. 0.008 0.016 ↓ 26.0 26 1

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

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

Index Scan using idx_organizational_unit_state on organizational_unit (cost=0.15..8.17 rows=1 width=64) (actual time=0.004..0.008 rows=26 loops=1)

  • Index Cond: (state = 'ACTIVE'::text)
8. 0.007 1.189 ↓ 5.0 5 29

Nested Loop Anti Join (cost=81.48..98.30 rows=1 width=16) (actual time=0.026..0.041 rows=5 loops=29)

9. 0.029 0.870 ↓ 5.0 5 29

Subquery Scan on alias_755097 (cost=81.19..81.28 rows=1 width=16) (actual time=0.026..0.030 rows=5 loops=29)

  • Filter: (alias_755097.r = 1)
  • Rows Removed by Filter: 0
10. 0.116 0.841 ↓ 2.0 6 29

WindowAgg (cost=81.19..81.25 rows=3 width=20) (actual time=0.025..0.029 rows=6 loops=29)

11. 0.058 0.725 ↓ 2.0 6 29

Sort (cost=81.19..81.19 rows=3 width=20) (actual time=0.025..0.025 rows=6 loops=29)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 25kB
12. 0.022 0.667 ↓ 2.0 6 29

Result (cost=20.17..81.16 rows=3 width=20) (actual time=0.002..0.023 rows=6 loops=29)

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

Nested Loop (cost=20.17..81.16 rows=3 width=20) (actual time=0.052..0.645 rows=167 loops=1)

14. 0.039 0.167 ↓ 56.0 448 1

Nested Loop (cost=19.90..78.12 rows=8 width=16) (actual time=0.049..0.167 rows=448 loops=1)

15. 0.015 0.015 ↑ 1.0 1 1

Index Scan using idx_organizational_unit_state on organizational_unit ou2 (cost=0.15..8.17 rows=1 width=16) (actual time=0.008..0.015 rows=1 loops=1)

  • Index Cond: (state = 'ACTIVE'::text)
  • Filter: (materialized_path @> organizational_unit.materialized_path)
  • Rows Removed by Filter: 25
16. 0.079 0.113 ↑ 1.0 448 1

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

  • Recheck Cond: ((organizational_unit_id = ou2.id) AND (state = 'ACTIVE'::text))
  • Heap Blocks: exact=39
17. 0.034 0.034 ↑ 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.034..0.034 rows=448 loops=1)

  • Index Cond: (organizational_unit_id = ou2.id)
18. 0.448 0.448 ↓ 0.0 0 448

Index Scan using employee_organization_id_state_idx on employee (cost=0.27..0.35 rows=3 width=36) (actual time=0.001..0.001 rows=0 loops=448)

  • Index Cond: ((organization_id = organization_assignment.organization_id) AND (state = 'ACTIVE'::text))
19. 0.000 0.312 ↓ 0.0 0 156

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

20. 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)
21. 0.156 0.156 ↓ 0.0 0 156

Index Scan using person_type_pkey on person_type (cost=0.15..0.48 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
22. 0.156 0.156 ↑ 1.0 1 156

Index Scan using person_id_active_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)
23. 0.156 0.156 ↑ 1.0 1 156

Index Scan using pk_organizational_hierarchy on organizational_hierarchy (cost=0.15..8.17 rows=1 width=16) (actual time=0.000..0.001 rows=1 loops=156)

  • Index Cond: (id = organizational_unit.organizational_hierarchy_id)
  • Filter: (state = 'ACTIVE'::text)
Planning time : 1.400 ms
Execution time : 1.648 ms