explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GqJp

Settings
# exclusive inclusive rows x rows loops node
1. 1.012 86.806 ↑ 1.8 8,341 1

Nested Loop (cost=49,102.06..19,561,632.47 rows=15,254 width=16) (actual time=48.633..86.806 rows=8,341 loops=1)

2. 1.417 60.324 ↑ 2.4 8,490 1

Hash Anti Join (cost=49,101.64..19,395,786.75 rows=20,115 width=16) (actual time=48.622..60.324 rows=8,490 loops=1)

  • Hash Cond: (alias_755097.person_id = person_to_person_type.person_id)
3. 0.903 58.383 ↑ 6.5 8,490 1

Nested Loop (cost=42,601.10..19,388,901.73 rows=54,981 width=16) (actual time=48.084..58.383 rows=8,490 loops=1)

4. 0.113 1.368 ↓ 1.1 501 1

Nested Loop (cost=27.70..85.90 rows=447 width=96) (actual time=0.626..1.368 rows=501 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.280 1.244 ↓ 1.1 501 1

Hash Right Join (cost=27.70..79.30 rows=447 width=112) (actual time=0.613..1.244 rows=501 loops=1)

  • Hash Cond: (person_assignment.organizational_unit_id = organizational_unit.id)
7. 0.380 0.380 ↑ 1.0 487 1

Seq Scan on person_assignment (cost=0.00..50.31 rows=487 width=32) (actual time=0.009..0.380 rows=487 loops=1)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 739
8. 0.197 0.584 ↑ 1.0 447 1

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

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

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

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 42
10. 1.503 56.112 ↑ 7.2 17 501

Subquery Scan on alias_755097 (cost=42,573.40..43,374.20 rows=123 width=16) (actual time=0.094..0.112 rows=17 loops=501)

  • Filter: (alias_755097.r = 1)
  • Rows Removed by Filter: 3
11. 7.014 54.609 ↑ 1,232.0 20 501

WindowAgg (cost=42,573.40..43,066.20 rows=24,640 width=44) (actual time=0.093..0.109 rows=20 loops=501)

12. 5.511 47.595 ↑ 1,232.0 20 501

Sort (cost=42,573.40..42,635.00 rows=24,640 width=20) (actual time=0.093..0.095 rows=20 loops=501)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 25kB
13. 1.151 42.084 ↑ 1,232.0 20 501

Result (cost=216.16..40,776.07 rows=24,640 width=20) (actual time=0.004..0.084 rows=20 loops=501)

  • One-Time Filter: (person_assignment.person_id = '2af3cd7b-7636-4205-a299-8b8fa8081d96'::uuid)
14. 5.995 40.933 ↑ 2.5 10,011 1

Nested Loop (cost=216.16..40,776.07 rows=24,640 width=20) (actual time=2.070..40.933 rows=10,011 loops=1)

15. 0.625 9.414 ↑ 2.3 4,254 1

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

16. 0.247 0.247 ↑ 2.0 1 1

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

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

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

  • Recheck Cond: ((organizational_unit_id = ou2.id) AND (state = 'ACTIVE'::text))
  • Heap Blocks: exact=4014
18. 0.888 0.888 ↑ 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=0.888..0.888 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.005..0.006 rows=2 loops=4,254)

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

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

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

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

22. 0.014 0.014 ↑ 1.0 1 1

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

  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 47
23. 0.319 0.353 ↑ 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.353 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.566 ms
Execution time : 87.270 ms