explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aC8q

Settings
# exclusive inclusive rows x rows loops node
1. 149,522.163 170,077.827 ↓ 12.6 479,525 1

Nested Loop Left Join (cost=1,149,943.74..1,561,802.10 rows=38,161 width=805) (actual time=10,437.920..170,077.827 rows=479,525 loops=1)

2. 202.678 15,280.889 ↓ 12.6 479,525 1

Hash Left Join (cost=1,149,943.32..1,501,941.30 rows=38,161 width=217) (actual time=10,425.779..15,280.889 rows=479,525 loops=1)

  • Hash Cond: (b.light_type = l.codenum)
3. 199.789 15,077.954 ↓ 12.6 479,525 1

Hash Left Join (cost=1,149,941.76..1,501,822.60 rows=38,161 width=200) (actual time=10,425.508..15,077.954 rows=479,525 loops=1)

  • Hash Cond: (a.dp_state = k.codenum)
4. 3,039.174 14,877.865 ↓ 12.6 479,525 1

Hash Join (cost=1,149,940.26..1,501,701.63 rows=38,161 width=186) (actual time=10,425.191..14,877.865 rows=479,525 loops=1)

  • Hash Cond: ((n.dp_oid = a.dp_oid) AND (n.dp_otype = a.dp_otype))
5. 1,416.467 1,416.467 ↑ 1.0 5,915,764 1

Seq Scan on symg n (cost=0.00..191,647.35 rows=5,938,188 width=48) (actual time=0.016..1,416.467 rows=5,915,764 loops=1)

  • Filter: (dp_ctype = 4,002)
  • Rows Removed by Filter: 613,544
6. 215.423 10,422.224 ↑ 1.0 479,525 1

Hash (cost=1,131,579.63..1,131,579.63 rows=503,242 width=146) (actual time=10,422.224..10,422.224 rows=479,525 loops=1)

  • Buckets: 32,768 Batches: 32 Memory Usage: 2,153kB
7. 107.791 10,206.801 ↑ 1.0 479,525 1

Hash Left Join (cost=51,722.92..1,131,579.63 rows=503,242 width=146) (actual time=8,034.591..10,206.801 rows=479,525 loops=1)

  • Hash Cond: (c.org_unit = d.codenum)
8. 99.817 10,097.831 ↑ 1.0 479,525 1

Hash Left Join (cost=51,631.75..1,112,664.87 rows=479,693 width=150) (actual time=8,033.406..10,097.831 rows=479,525 loops=1)

  • Hash Cond: (a.dp_otype = h.dp_otype)
9. 91.266 9,997.264 ↑ 1.0 479,525 1

Hash Left Join (cost=51,615.08..1,111,379.53 rows=479,693 width=140) (actual time=8,032.651..9,997.264 rows=479,525 loops=1)

  • Hash Cond: (a.supplier = g.codenum)
10. 97.803 9,905.843 ↑ 1.0 479,525 1

Hash Left Join (cost=51,613.76..1,109,870.12 rows=479,693 width=138) (actual time=8,032.492..9,905.843 rows=479,525 loops=1)

  • Hash Cond: (a.arm_type = f.codenum)
11. 95.327 9,807.866 ↑ 1.0 479,525 1

Hash Left Join (cost=51,612.65..1,107,546.02 rows=479,693 width=126) (actual time=8,032.314..9,807.866 rows=479,525 loops=1)

  • Hash Cond: (a.owner = e.codenum)
12. 399.550 9,712.247 ↑ 1.0 479,525 1

Hash Left Join (cost=51,611.54..1,105,218.57 rows=479,693 width=121) (actual time=8,032.014..9,712.247 rows=479,525 loops=1)

  • Hash Cond: ((a.dp_oid = b.dp_oid) AND (a.dp_otype = b.dp_otype))
13. 5,339.475 9,126.357 ↑ 1.0 479,525 1

Hash Right Join (cost=29,703.49..1,055,538.89 rows=479,693 width=117) (actual time=266.807..9,126.357 rows=479,525 loops=1)

  • Hash Cond: ((c.dp_otype = a.dp_otype) AND (c.dp_oid = a.dp_oid))
14. 3,524.077 3,524.077 ↑ 1.0 16,798,427 1

Seq Scan on elname c (cost=0.00..404,276.99 rows=16,798,499 width=93) (actual time=0.007..3,524.077 rows=16,798,427 loops=1)

15. 127.138 262.805 ↑ 1.0 479,525 1

Hash (cost=19,228.09..19,228.09 rows=479,693 width=32) (actual time=262.805..262.805 rows=479,525 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 2,343kB
16. 135.667 135.667 ↑ 1.0 479,525 1

Seq Scan on elarm a (cost=0.00..19,228.09 rows=479,693 width=32) (actual time=0.007..135.667 rows=479,525 loops=1)

  • Filter: (dp_state = ANY ('{150,160,180,200,250,300,400,410,420}'::integer[]))
  • Rows Removed by Filter: 34,597
17. 99.032 186.340 ↑ 1.0 514,122 1

Hash (cost=11,685.22..11,685.22 rows=514,122 width=12) (actual time=186.340..186.340 rows=514,122 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 3,790kB
18. 87.308 87.308 ↑ 1.0 514,122 1

Seq Scan on ellght b (cost=0.00..11,685.22 rows=514,122 width=12) (actual time=0.005..87.308 rows=514,122 loops=1)

19. 0.003 0.292 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=13) (actual time=0.292..0.292 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.289 0.289 ↑ 1.0 5 1

Seq Scan on elowner_cl e (cost=0.00..1.05 rows=5 width=13) (actual time=0.288..0.289 rows=5 loops=1)

21. 0.002 0.174 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=20) (actual time=0.174..0.174 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.172 0.172 ↑ 1.0 5 1

Seq Scan on elarmtype_cl f (cost=0.00..1.05 rows=5 width=20) (actual time=0.171..0.172 rows=5 loops=1)

23. 0.003 0.155 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=10) (actual time=0.155..0.155 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.152 0.152 ↑ 1.0 14 1

Seq Scan on cstarmbrand_cl g (cost=0.00..1.14 rows=14 width=10) (actual time=0.150..0.152 rows=14 loops=1)

25. 0.087 0.750 ↑ 1.0 563 1

Hash (cost=9.63..9.63 rows=563 width=14) (actual time=0.750..0.750 rows=563 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
26. 0.663 0.663 ↑ 1.0 563 1

Seq Scan on dp_objtypes h (cost=0.00..9.63 rows=563 width=14) (actual time=0.242..0.663 rows=563 loops=1)

27. 0.009 1.179 ↑ 2.8 75 1

Hash (cost=88.53..88.53 rows=211 width=4) (actual time=1.179..1.179 rows=75 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
28. 0.009 1.170 ↑ 2.8 75 1

Subquery Scan on d (cost=85.90..88.53 rows=211 width=4) (actual time=1.157..1.170 rows=75 loops=1)

29. 0.141 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
30.          

CTE org_struct

31. 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)

32. 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)
33. 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)

34. 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
35. 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
36. 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)
37. 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)

38. 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
39. 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)
40. 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
41. 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
42. 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)

43. 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)
44. 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)
45. 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)
46. 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)

47. 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
48. 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)

49. 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
50. 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)

51. 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
52. 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)

53. 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
54. 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)

55. 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
56. 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)

57. 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