explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7hYI

Settings
# exclusive inclusive rows x rows loops node
1. 0.912 86.880 ↑ 4.6 8,341 1

Nested Loop (cost=49,102.06..49,003,579.90 rows=38,220 width=16) (actual time=47.807..86.880 rows=8,341 loops=1)

2. 1.407 60.498 ↑ 5.9 8,490 1

Hash Anti Join (cost=49,101.64..48,588,038.05 rows=50,400 width=16) (actual time=47.796..60.498 rows=8,490 loops=1)

  • Hash Cond: (alias_755097.person_id = person_to_person_type.person_id)
3. 1.137 58.576 ↑ 16.2 8,490 1

Nested Loop (cost=42,601.10..48,580,574.16 rows=137,760 width=16) (actual time=47.268..58.576 rows=8,490 loops=1)

4. 0.266 1.855 ↓ 1.0 1,158 1

Nested Loop (cost=27.70..93.21 rows=1,120 width=103) (actual time=0.610..1.855 rows=1,158 loops=1)

  • Join Filter: (organizational_unit.organizational_hierarchy_id = organizational_hierarchy.id)
5. 0.011 0.011 ↑ 1.0 1 1

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

  • Filter: (state = 'ACTIVE'::text)
6. 0.744 1.578 ↓ 1.0 1,158 1

Hash Right Join (cost=27.70..78.20 rows=1,120 width=119) (actual time=0.596..1.578 rows=1,158 loops=1)

  • Hash Cond: (person_assignment.organizational_unit_id = organizational_unit.id)
7. 0.304 0.304 ↓ 1.0 1,226 1

Seq Scan on person_assignment (cost=0.00..47.25 rows=1,225 width=39) (actual time=0.007..0.304 rows=1,226 loops=1)

8. 0.196 0.530 ↑ 1.0 447 1

Hash (cost=22.11..22.11 rows=447 width=112) (actual time=0.529..0.530 rows=447 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 72kB
9. 0.334 0.334 ↑ 1.0 447 1

Seq Scan on organizational_unit (cost=0.00..22.11 rows=447 width=112) (actual time=0.009..0.334 rows=447 loops=1)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 42
10. 1.158 55.584 ↑ 17.6 7 1,158

Subquery Scan on alias_755097 (cost=42,573.40..43,374.20 rows=123 width=16) (actual time=0.040..0.048 rows=7 loops=1,158)

  • Filter: (alias_755097.r = 1)
  • Rows Removed by Filter: 1
11. 8.106 54.426 ↑ 2,737.8 9 1,158

WindowAgg (cost=42,573.40..43,066.20 rows=24,640 width=44) (actual time=0.040..0.047 rows=9 loops=1,158)

12. 5.790 46.320 ↑ 2,737.8 9 1,158

Sort (cost=42,573.40..42,635.00 rows=24,640 width=20) (actual time=0.039..0.040 rows=9 loops=1,158)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 25kB
13. 1.363 40.530 ↑ 2,737.8 9 1,158

Result (cost=216.17..40,776.07 rows=24,640 width=20) (actual time=0.003..0.035 rows=9 loops=1,158)

  • One-Time Filter: ((person_assignment.state = 'ACTIVE'::text) AND (person_assignment.person_id = '2af3cd7b-7636-4205-a299-8b8fa8081d96'::uuid))
14. 4.010 39.167 ↑ 2.5 10,011 1

Nested Loop (cost=216.17..40,776.07 rows=24,640 width=20) (actual time=2.771..39.167 rows=10,011 loops=1)

15. 0.560 9.633 ↑ 2.3 4,254 1

Nested Loop (cost=215.74..33,555.68 rows=9,585 width=16) (actual time=2.708..9.633 rows=4,254 loops=1)

16. 0.312 0.312 ↑ 2.0 1 1

Seq Scan on organizational_unit ou2 (cost=0.00..23.34 rows=2 width=16) (actual time=0.281..0.312 rows=1 loops=1)

  • Filter: ((materialized_path @> organizational_unit.materialized_path) AND (state = 'ACTIVE'::text))
  • Rows Removed by Filter: 488
17. 7.594 8.761 ↑ 1.6 4,254 1

Bitmap Heap Scan on organization_assignment (cost=215.74..16,699.97 rows=6,620 width=32) (actual time=2.423..8.761 rows=4,254 loops=1)

  • Recheck Cond: ((organizational_unit_id = ou2.id) AND (state = 'ACTIVE'::text))
  • Heap Blocks: exact=4014
18. 1.167 1.167 ↑ 1.6 4,254 1

Bitmap Index Scan on organizational_assignment_org_unit_id_org_id_idx (cost=0.00..214.08 rows=6,620 width=0) (actual time=1.167..1.167 rows=4,254 loops=1)

  • Index Cond: (organizational_unit_id = ou2.id)
19. 25.524 25.524 ↑ 5.0 2 4,254

Index Scan using employee_organization_id_state_idx on employee (cost=0.42..0.65 rows=10 width=36) (actual time=0.004..0.006 rows=2 loops=4,254)

  • Index Cond: ((organization_id = organization_assignment.organization_id) AND (state = 'ACTIVE'::text))
20. 0.088 0.515 ↑ 11.9 627 1

Hash (cost=6,407.45..6,407.45 rows=7,447 width=16) (actual time=0.515..0.515 rows=627 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 94kB
21. 0.072 0.427 ↑ 11.9 627 1

Nested Loop (cost=276.76..6,407.45 rows=7,447 width=16) (actual time=0.067..0.427 rows=627 loops=1)

22. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on person_type (cost=0.00..2.60 rows=1 width=16) (actual time=0.012..0.016 rows=1 loops=1)

  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 47
23. 0.305 0.339 ↑ 19.0 627 1

Bitmap Heap Scan on person_to_person_type (cost=276.76..6,285.70 rows=11,915 width=32) (actual time=0.052..0.339 rows=627 loops=1)

  • Recheck Cond: (person_type_id = person_type.id)
  • Heap Blocks: exact=170
24. 0.034 0.034 ↑ 19.0 627 1

Bitmap Index Scan on person_to_person_type_person_type_idx (cost=0.00..273.79 rows=11,915 width=0) (actual time=0.034..0.034 rows=627 loops=1)

  • Index Cond: (person_type_id = person_type.id)
25. 25.470 25.470 ↑ 1.0 1 8,490

Index Only Scan using person_id_active_idx on person (cost=0.42..8.24 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=8,490)

  • Index Cond: (id = alias_755097.person_id)
  • Heap Fetches: 8341
Planning time : 3.545 ms
Execution time : 87.340 ms