explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 81Ib

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 1.430 ↓ 14.5 58 1

Nested Loop Left Join (cost=214.13..269.33 rows=4 width=379) (actual time=0.509..1.430 rows=58 loops=1)

2.          

CTE hier

3. 0.041 0.911 ↑ 12.7 64 1

Recursive Union (cost=0.28..213.30 rows=811 width=178) (actual time=0.015..0.911 rows=64 loops=1)

4. 0.002 0.014 ↑ 1.0 1 1

Nested Loop (cost=0.28..9.50 rows=1 width=116) (actual time=0.014..0.014 rows=1 loops=1)

  • Join Filter: (oh."OrganizationStructureID" = ost."OrganizationStructureID")
  • Rows Removed by Join Filter: 5
5. 0.009 0.009 ↑ 1.0 1 1

Index Scan using "pk_OrganizationHierarchy" on "OrganizationHierarchy" oh (cost=0.28..8.29 rows=1 width=42) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: ("OrganizationHierarchyID" = 87)
6. 0.003 0.003 ↑ 1.5 6 1

Seq Scan on "OrganizationStructure" ost (cost=0.00..1.09 rows=9 width=36) (actual time=0.002..0.003 rows=6 loops=1)

7. 0.344 0.856 ↑ 5.1 16 4

Hash Join (cost=0.33..18.76 rows=81 width=178) (actual time=0.085..0.214 rows=16 loops=4)

  • Hash Cond: (oh_1."ParentOrganizationHierarchyID" = hier_1."OrganizationHierarchyID")
8. 0.464 0.464 ↓ 1.0 665 4

Seq Scan on "OrganizationHierarchy" oh_1 (cost=0.00..14.56 rows=655 width=46) (actual time=0.003..0.116 rows=665 loops=4)

  • Filter: ("ParentOrganizationHierarchyID" IS NOT NULL)
  • Rows Removed by Filter: 1
9. 0.032 0.048 ↓ 1.6 16 4

Hash (cost=0.20..0.20 rows=10 width=140) (actual time=0.012..0.012 rows=16 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
10. 0.016 0.016 ↓ 1.6 16 4

WorkTable Scan on hier hier_1 (cost=0.00..0.20 rows=10 width=140) (actual time=0.001..0.004 rows=16 loops=4)

11. 0.037 1.297 ↓ 14.5 58 1

Nested Loop (cost=0.56..54.81 rows=4 width=369) (actual time=0.502..1.297 rows=58 loops=1)

12. 0.041 1.144 ↓ 14.5 58 1

Nested Loop (cost=0.28..51.45 rows=4 width=334) (actual time=0.494..1.144 rows=58 loops=1)

13. 0.975 0.975 ↓ 16.0 64 1

CTE Scan on hier (cost=0.00..18.25 rows=4 width=72) (actual time=0.018..0.975 rows=64 loops=1)

  • Filter: ("StructureName" = 'Division'::text)
14. 0.128 0.128 ↑ 1.0 1 64

Index Scan using "pk_Location" on "Location" loc (cost=0.28..8.29 rows=1 width=266) (actual time=0.002..0.002 rows=1 loops=64)

  • Index Cond: ("OrganizationHierarchyID" = hier."OrganizationHierarchyID")
  • Filter: "IsActive
15. 0.116 0.116 ↑ 1.0 1 58

Index Scan using "pk_Address" on "Address" add (cost=0.28..0.84 rows=1 width=39) (actual time=0.002..0.002 rows=1 loops=58)

  • Index Cond: ("AddressID" = loc."AddressID")
16. 0.116 0.116 ↑ 1.0 1 58

Index Scan using "pk_State" on "State" (cost=0.28..0.30 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=58)

  • Index Cond: ("StateID" = add."GoverningDistrict")
Planning time : 0.858 ms