explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FJH6

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 21.949 ↓ 156.0 156 1

Nested Loop (cost=81.92..124.20 rows=1 width=16) (actual time=4.060..21.949 rows=156 loops=1)

2. 0.007 21.781 ↓ 156.0 156 1

Nested Loop (cost=81.77..116.02 rows=1 width=32) (actual time=4.056..21.781 rows=156 loops=1)

3. 0.023 21.618 ↓ 156.0 156 1

Nested Loop (cost=81.62..107.84 rows=1 width=32) (actual time=4.054..21.618 rows=156 loops=1)

4. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on person_assignment (cost=0.00..1.36 rows=1 width=23) (actual time=0.006..0.008 rows=1 loops=1)

  • Filter: (person_id = '8e459ecd-2208-44b9-836a-b9bbf182ec03'::uuid)
  • Rows Removed by Filter: 28
5. 0.207 21.587 ↓ 156.0 156 1

Nested Loop (cost=81.62..106.47 rows=1 width=48) (actual time=4.047..21.587 rows=156 loops=1)

  • Join Filter: (person_assignment.organizational_unit_id = organizational_unit.id)
  • Rows Removed by Join Filter: 1,716
6. 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)
7. 0.156 21.372 ↓ 72.0 72 26

Nested Loop Anti Join (cost=81.48..98.29 rows=1 width=16) (actual time=0.590..0.822 rows=72 loops=26)

8. 0.364 17.472 ↓ 72.0 72 26

Subquery Scan on alias_755097 (cost=81.18..81.28 rows=1 width=16) (actual time=0.588..0.672 rows=72 loops=26)

  • Filter: (alias_755097.r = 1)
  • Rows Removed by Filter: 76
9. 1.586 17.108 ↓ 49.3 148 26

WindowAgg (cost=81.18..81.24 rows=3 width=20) (actual time=0.587..0.658 rows=148 loops=26)

10. 1.196 15.522 ↓ 49.3 148 26

Sort (cost=81.18..81.19 rows=3 width=20) (actual time=0.585..0.597 rows=148 loops=26)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 90kB
11. 0.364 14.326 ↓ 49.3 148 26

Result (cost=20.17..81.16 rows=3 width=20) (actual time=0.031..0.551 rows=148 loops=26)

  • One-Time Filter: (person_assignment.state = 'ACTIVE'::text)
12. 0.000 13.962 ↓ 49.3 148 26

Nested Loop (cost=20.17..81.16 rows=3 width=20) (actual time=0.031..0.537 rows=148 loops=26)

13. 1.238 3.874 ↓ 49.5 396 26

Nested Loop (cost=19.90..78.12 rows=8 width=16) (actual time=0.029..0.149 rows=396 loops=26)

14. 0.286 0.286 ↓ 4.0 4 26

Index Scan using idx_organizational_unit_state on organizational_unit ou2 (cost=0.15..8.17 rows=1 width=16) (actual time=0.004..0.011 rows=4 loops=26)

  • Index Cond: (state = 'ACTIVE'::text)
  • Filter: (materialized_path @> organizational_unit.materialized_path)
  • Rows Removed by Filter: 22
15. 1.598 2.350 ↑ 4.1 110 94

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

  • Recheck Cond: ((organizational_unit_id = ou2.id) AND (state = 'ACTIVE'::text))
  • Heap Blocks: exact=897
16. 0.752 0.752 ↑ 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.008..0.008 rows=110 loops=94)

  • Index Cond: (organizational_unit_id = ou2.id)
17. 10.304 10.304 ↓ 0.0 0 10,304

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=10,304)

  • Index Cond: ((organization_id = organization_assignment.organization_id) AND (state = 'ACTIVE'::text))
18. 0.000 3.744 ↓ 0.0 0 1,872

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

19. 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)
20. 1.872 1.872 ↓ 0.0 0 1,872

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=1,872)

  • Index Cond: (id = person_to_person_type.person_type_id)
  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 1
21. 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)
22. 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.001..0.001 rows=1 loops=156)

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