explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XSP8

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1.161 ↑ 2.8 75 1

Sort (cost=85.90..86.42 rows=211 width=840) (actual time=1.156..1.161 rows=75 loops=1)

  • Sort Key: l1.org_name NULLS FIRST, l2.org_name NULLS FIRST, l3.org_name NULLS FIRST
  • Sort Method: quicksort Memory: 30kB
2.          

CTE org_struct

3. 0.131 0.824 ↑ 2.8 75 1

Recursive Union (cost=2.00..63.50 rows=211 width=83) (actual time=0.553..0.824 rows=75 loops=1)

4. 0.039 0.498 ↑ 1.0 1 1

Hash Right Join (cost=2.00..3.96 rows=1 width=83) (actual time=0.494..0.498 rows=1 loops=1)

  • Hash Cond: (c_1.org_unit_id = o.id)
5. 0.214 0.214 ↑ 1.0 75 1

Seq Scan on org_unit_cst c_1 (cost=0.00..1.75 rows=75 width=16) (actual time=0.209..0.214 rows=75 loops=1)

6. 0.003 0.245 ↑ 1.0 1 1

Hash (cost=1.99..1.99 rows=1 width=35) (actual time=0.245..0.245 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
7. 0.242 0.242 ↑ 1.0 1 1

Seq Scan on org_unit o (cost=0.00..1.99 rows=1 width=35) (actual time=0.240..0.242 rows=1 loops=1)

  • Filter: ((parent_id IS NULL) AND ((cust_org_unit_type)::text = 'MEDAŞ'::text))
  • Rows Removed by Filter: 78
8. 0.058 0.195 ↓ 1.2 25 3

Hash Right Join (cost=3.20..5.53 rows=21 width=83) (actual time=0.046..0.065 rows=25 loops=3)

  • Hash Cond: (c_2.org_unit_id = o_1.id)
9. 0.014 0.014 ↑ 1.0 75 2

Seq Scan on org_unit_cst c_2 (cost=0.00..1.75 rows=75 width=16) (actual time=0.001..0.007 rows=75 loops=2)

10. 0.024 0.123 ↓ 1.2 25 3

Hash (cost=2.93..2.93 rows=21 width=71) (actual time=0.041..0.041 rows=25 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
11. 0.033 0.099 ↓ 1.2 25 3

Hash Join (cost=0.33..2.93 rows=21 width=71) (actual time=0.019..0.033 rows=25 loops=3)

  • Hash Cond: (o_1.parent_id = o2.codenum)
12. 0.039 0.039 ↓ 1.3 74 3

Seq Scan on org_unit o_1 (cost=0.00..2.19 rows=57 width=35) (actual time=0.002..0.013 rows=74 loops=3)

  • Filter: (((cust_org_unit_type)::text = 'OC'::text) OR ((cust_org_unit_type)::text = 'SOC'::text))
  • Rows Removed by Filter: 5
13. 0.015 0.027 ↓ 2.5 25 3

Hash (cost=0.20..0.20 rows=10 width=40) (actual time=0.009..0.009 rows=25 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
14. 0.012 0.012 ↓ 2.5 25 3

WorkTable Scan on org_struct o2 (cost=0.00..0.20 rows=10 width=40) (actual time=0.001..0.004 rows=25 loops=3)

15. 0.028 1.020 ↑ 2.8 75 1

Hash Left Join (cost=8.33..14.25 rows=211 width=840) (actual time=0.637..1.020 rows=75 loops=1)

  • Hash Cond: (os.path[3] = l3.id)
16. 0.025 0.971 ↑ 2.8 75 1

Hash Left Join (cost=5.55..10.90 rows=211 width=56) (actual time=0.611..0.971 rows=75 loops=1)

  • Hash Cond: (os.path[2] = l2.id)
17. 0.031 0.925 ↑ 2.8 75 1

Hash Left Join (cost=2.78..7.56 rows=211 width=46) (actual time=0.586..0.925 rows=75 loops=1)

  • Hash Cond: (os.path[1] = l1.id)
18. 0.869 0.869 ↑ 2.8 75 1

CTE Scan on org_struct os (cost=0.00..4.22 rows=211 width=36) (actual time=0.554..0.869 rows=75 loops=1)

19. 0.013 0.025 ↑ 1.0 79 1

Hash (cost=1.79..1.79 rows=79 width=14) (actual time=0.025..0.025 rows=79 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
20. 0.012 0.012 ↑ 1.0 79 1

Seq Scan on org_unit l1 (cost=0.00..1.79 rows=79 width=14) (actual time=0.004..0.012 rows=79 loops=1)

21. 0.012 0.021 ↑ 1.0 79 1

Hash (cost=1.79..1.79 rows=79 width=14) (actual time=0.021..0.021 rows=79 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
22. 0.009 0.009 ↑ 1.0 79 1

Seq Scan on org_unit l2 (cost=0.00..1.79 rows=79 width=14) (actual time=0.002..0.009 rows=79 loops=1)

23. 0.012 0.021 ↑ 1.0 79 1

Hash (cost=1.79..1.79 rows=79 width=14) (actual time=0.021..0.021 rows=79 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
24. 0.009 0.009 ↑ 1.0 79 1

Seq Scan on org_unit l3 (cost=0.00..1.79 rows=79 width=14) (actual time=0.002..0.009 rows=79 loops=1)

25. 0.008 0.300 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=18) (actual time=0.300..0.300 rows=22 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
26. 0.292 0.292 ↑ 1.0 22 1

Seq Scan on elstate_cl k (cost=0.00..1.22 rows=22 width=18) (actual time=0.290..0.292 rows=22 loops=1)

27. 0.007 0.257 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=25) (actual time=0.257..0.257 rows=25 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
28. 0.250 0.250 ↑ 1.0 25 1

Seq Scan on ellghttype_cl l (cost=0.00..1.25 rows=25 width=25) (actual time=0.247..0.250 rows=25 loops=1)

29. 5,274.775 5,274.775 ↑ 1.0 1 479,525

Index Scan using elarm_cst_oid_idx on elarm_cst m (cost=0.42..0.54 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=479,525)

  • Index Cond: (dp_oid = a.dp_oid)
  • Filter: (a.dp_otype = dp_otype)
Planning time : 5.027 ms
Execution time : 170,128.843 ms