explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jspr

Settings
# exclusive inclusive rows x rows loops node
1. 315.576 19,449.033 ↑ 9.9 3,974 1

Unique (cost=81,170,588.20..81,212,422.50 rows=39,216 width=3,654) (actual time=18,528.209..19,449.033 rows=3,974 loops=1)

2.          

CTE licensed_elements

3. 18.183 56.013 ↓ 15.0 20,049 1

Hash Join (cost=743.98..1,194.26 rows=1,341 width=686) (actual time=17.542..56.013 rows=20,049 loops=1)

  • Hash Cond: ("*SELECT* 1".ne_id = n.networkelement_id)
4. 3.388 23.227 ↓ 1.6 19,403 1

Append (cost=206.91..596.87 rows=12,509 width=8) (actual time=2.924..23.227 rows=19,403 loops=1)

5. 0.509 6.925 ↓ 1.4 2,513 1

Subquery Scan on *SELECT* 1 (cost=206.91..283.50 rows=1,846 width=8) (actual time=2.923..6.925 rows=2,513 loops=1)

6. 1.553 6.416 ↓ 1.4 2,513 1

Hash Join (cost=206.91..265.04 rows=1,846 width=1,042) (actual time=2.923..6.416 rows=2,513 loops=1)

  • Hash Cond: (negroupassociation.networkelement_id = networkelement.networkelement_id)
7. 1.298 2.002 ↓ 1.2 2,532 1

Hash Join (cost=1.33..53.92 rows=2,109 width=8) (actual time=0.049..2.002 rows=2,532 loops=1)

  • Hash Cond: (negroupassociation.negroup_id = negroup.negroup_id)
8. 0.678 0.678 ↑ 1.1 2,532 1

Seq Scan on negroupassociation (cost=0.00..42.12 rows=2,712 width=16) (actual time=0.011..0.678 rows=2,532 loops=1)

9. 0.007 0.026 ↓ 1.9 13 1

Hash (cost=1.25..1.25 rows=7 width=8) (actual time=0.026..0.026 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.019 0.019 ↓ 1.9 13 1

Seq Scan on negroup (cost=0.00..1.25 rows=7 width=8) (actual time=0.009..0.019 rows=13 loops=1)

  • Filter: ((name)::text = ANY ('{"All Profiles",ERICS_BO-TX,OCM_B2BD,5620SAM,GNOC_BO-TX,U2000,OCM_ING-TX,1353NM_1,OCM_BO,Nomad,5min_counters,TOKOS,15min_counters,OCM_CAP-MA}'::text[]))
11. 0.829 2.861 ↑ 1.0 2,908 1

Hash (cost=169.22..169.22 rows=2,908 width=8) (actual time=2.861..2.861 rows=2,908 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 146kB
12. 2.032 2.032 ↑ 1.0 2,908 1

Seq Scan on networkelement (cost=0.00..169.22 rows=2,908 width=8) (actual time=0.007..2.032 rows=2,908 loops=1)

  • Filter: ((networkelement_id IS NOT NULL) AND licensed)
  • Rows Removed by Filter: 414
13. 3.679 12.914 ↓ 1.6 16,890 1

Subquery Scan on *SELECT* 2 (cost=3.77..313.37 rows=10,663 width=8) (actual time=0.132..12.914 rows=16,890 loops=1)

14. 6.465 9.235 ↓ 1.6 16,890 1

Hash Join (cost=3.77..206.74 rows=10,663 width=1,042) (actual time=0.131..9.235 rows=16,890 loops=1)

  • Hash Cond: (networkelement_1.software_id = softwaregroupassociation.software_id)
15. 2.675 2.675 ↑ 1.0 2,908 1

Seq Scan on networkelement networkelement_1 (cost=0.00..169.22 rows=2,908 width=16) (actual time=0.011..2.675 rows=2,908 loops=1)

  • Filter: ((networkelement_id IS NOT NULL) AND licensed)
  • Rows Removed by Filter: 414
16. 0.009 0.095 ↓ 1.7 19 1

Hash (cost=3.63..3.63 rows=11 width=16) (actual time=0.095..0.095 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.024 0.086 ↓ 1.7 19 1

Hash Join (cost=2.40..3.63 rows=11 width=16) (actual time=0.066..0.086 rows=19 loops=1)

  • Hash Cond: (softwaregroupassociation.software_id = software.software_id)
18. 0.018 0.051 ↓ 1.7 19 1

Hash Join (cost=1.33..2.52 rows=11 width=8) (actual time=0.039..0.051 rows=19 loops=1)

  • Hash Cond: (softwaregroupassociation.negroup_id = negroup_1.negroup_id)
19. 0.008 0.008 ↓ 1.4 19 1

Seq Scan on softwaregroupassociation (cost=0.00..1.14 rows=14 width=16) (actual time=0.005..0.008 rows=19 loops=1)

20. 0.005 0.025 ↓ 1.9 13 1

Hash (cost=1.25..1.25 rows=7 width=8) (actual time=0.025..0.025 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.020 0.020 ↓ 1.9 13 1

Seq Scan on negroup negroup_1 (cost=0.00..1.25 rows=7 width=8) (actual time=0.010..0.020 rows=13 loops=1)

  • Filter: ((name)::text = ANY ('{"All Profiles",ERICS_BO-TX,OCM_B2BD,5620SAM,GNOC_BO-TX,U2000,OCM_ING-TX,1353NM_1,OCM_BO,Nomad,5min_counters,TOKOS,15min_counters,OCM_CAP-MA}'::text[]))
22. 0.003 0.011 ↓ 1.3 4 1

Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.008 0.008 ↓ 1.3 4 1

Seq Scan on software (cost=0.00..1.03 rows=3 width=8) (actual time=0.006..0.008 rows=4 loops=1)

24. 2.763 14.603 ↓ 9.7 3,458 1

Hash (cost=532.62..532.62 rows=356 width=185) (actual time=14.603..14.603 rows=3,458 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 603kB
25. 5.789 11.840 ↓ 9.7 3,458 1

Hash Right Join (cost=210.75..532.62 rows=356 width=185) (actual time=2.148..11.840 rows=3,458 loops=1)

  • Hash Cond: (eq.networkelement_id = n.networkelement_id)
  • Filter: ((eq.type IS NULL) OR ((eq.type)::text = 'CHASSIS'::text))
  • Rows Removed by Filter: 8911
26. 3.930 3.930 ↑ 1.0 9,473 1

Seq Scan on equipment eq (cost=0.00..296.92 rows=9,492 width=20) (actual time=0.012..3.930 rows=9,473 loops=1)

27. 1.195 2.121 ↑ 1.0 3,322 1

Hash (cost=169.22..169.22 rows=3,322 width=180) (actual time=2.121..2.121 rows=3,322 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 574kB
28. 0.926 0.926 ↑ 1.0 3,322 1

Seq Scan on networkelement n (cost=0.00..169.22 rows=3,322 width=180) (actual time=0.008..0.926 rows=3,322 loops=1)

29.          

CTE valid_ltp

30. 22.618 22.618 ↓ 1.0 32,206 1

Seq Scan on ltp (cost=0.00..5,108.05 rows=31,717 width=1,109) (actual time=0.018..22.618 rows=32,206 loops=1)

  • Filter: (parentltp_id IS NULL)
  • Rows Removed by Filter: 148999
31.          

CTE valid_lp

32. 51.824 51.824 ↓ 1.0 72,241 1

Seq Scan on layerprotocol lp (cost=0.00..6,579.42 rows=68,938 width=85) (actual time=0.023..51.824 rows=72,241 loops=1)

  • Filter: ((layerprotocolname)::text = ANY ('{LR_OPTICAL_MULTIPLEX_SECTION,LR_OPTICAL_SUPERVISION_CHANNEL,LR_PHYSICAL_OPTICAL,LR_OPTICAL_TRANSMISSION_SECTION,LR_OPTICAL_SECTION}'::text[]))
  • Rows Removed by Filter: 148231
33. 5,940.857 19,133.457 ↑ 4.7 1,770,503 1

Sort (cost=81,157,706.46..81,178,623.61 rows=8,366,861 width=3,654) (actual time=18,528.207..19,133.457 rows=1,770,503 loops=1)

  • Sort Key: fc.forwardingconstruct_id
  • Sort Method: external merge Disk: 465824kB
34. 9,316.555 13,192.600 ↑ 4.7 1,770,503 1

Hash Left Join (cost=480,292.55..1,266,103.11 rows=8,366,861 width=3,654) (actual time=677.073..13,192.600 rows=1,770,503 loops=1)

  • Hash Cond: (nz.site_id = stz.site_id)
35. 411.040 3,874.724 ↑ 4.7 1,770,503 1

Hash Left Join (cost=480,194.58..1,139,407.63 rows=8,366,861 width=4,623) (actual time=675.674..3,874.724 rows=1,770,503 loops=1)

  • Hash Cond: (na.site_id = sta.site_id)
36. 650.137 3,462.310 ↑ 4.7 1,770,503 1

Hash Left Join (cost=480,096.60..1,117,297.90 rows=8,366,861 width=4,623) (actual time=674.264..3,462.310 rows=1,770,503 loops=1)

  • Hash Cond: (nz.location_id = lz.location_id)
37. 2,060.239 2,809.485 ↑ 4.7 1,770,503 1

Hash Join (cost=479,973.96..1,095,190.59 rows=8,366,861 width=4,622) (actual time=671.482..2,809.485 rows=1,770,503 loops=1)

  • Hash Cond: (ltpz.ltp_id = fc.zend)
38. 169.091 289.096 ↓ 2.3 495,961 1

Hash Join (cost=220.58..16,852.43 rows=212,662 width=2,096) (actual time=105.384..289.096 rows=495,961 loops=1)

  • Hash Cond: (ltpz.networkelement_id = nz.networkelement_id)
39. 14.656 14.656 ↓ 1.0 32,206 1

CTE Scan on valid_ltp ltpz (cost=0.00..634.34 rows=31,717 width=1,048) (actual time=0.021..14.656 rows=32,206 loops=1)

40. 11.202 105.349 ↓ 15.0 20,049 1

Hash (cost=26.82..26.82 rows=1,341 width=1,056) (actual time=105.349..105.349 rows=20,049 loops=1)

  • Buckets: 1024 Batches: 2 Memory Usage: 839kB
41. 94.147 94.147 ↓ 15.0 20,049 1

CTE Scan on licensed_elements nz (cost=0.00..26.82 rows=1,341 width=1,056) (actual time=17.548..94.147 rows=20,049 loops=1)

42. 74.148 460.150 ↑ 14.5 71,055 1

Hash (cost=144,692.46..144,692.46 rows=1,030,954 width=2,534) (actual time=460.150..460.150 rows=71,055 loops=1)

  • Buckets: 512 Batches: 4096 Memory Usage: 26kB
43. 23.253 386.002 ↑ 14.5 71,055 1

Hash Join (cost=31,925.11..144,692.46 rows=1,030,954 width=2,534) (actual time=285.100..386.002 rows=71,055 loops=1)

  • Hash Cond: (ltpa.networkelement_id = na.networkelement_id)
44. 40.272 327.547 ↑ 33.3 4,614 1

Hash Join (cost=31,576.36..50,956.14 rows=153,759 width=1,485) (actual time=248.680..327.547 rows=4,614 loops=1)

  • Hash Cond: (ltpa.ltp_id = fc.aend)
45. 42.154 42.154 ↓ 1.0 32,206 1

CTE Scan on valid_ltp ltpa (cost=0.00..634.34 rows=31,717 width=1,048) (actual time=0.003..42.154 rows=32,206 loops=1)

46. 9.927 245.121 ↑ 8.6 14,801 1

Hash (cost=22,668.45..22,668.45 rows=127,033 width=445) (actual time=245.121..245.121 rows=14,801 loops=1)

  • Buckets: 2048 Batches: 128 Memory Usage: 33kB
47. 42.854 235.194 ↑ 8.6 14,801 1

Hash Join (cost=12,832.16..22,668.45 rows=127,033 width=445) (actual time=174.549..235.194 rows=14,801 loops=1)

  • Hash Cond: (lpa.lp_id = fc.aend_lp)
48. 18.226 18.226 ↓ 1.0 72,241 1

CTE Scan on valid_lp lpa (cost=0.00..1,378.76 rows=68,938 width=226) (actual time=0.025..18.226 rows=72,241 loops=1)

49. 7.285 174.114 ↑ 4.8 14,801 1

Hash (cost=9,676.39..9,676.39 rows=70,541 width=235) (actual time=174.114..174.114 rows=14,801 loops=1)

  • Buckets: 4096 Batches: 32 Memory Usage: 94kB
50. 38.868 166.829 ↑ 4.8 14,801 1

Hash Join (cost=5,318.36..9,676.39 rows=70,541 width=235) (actual time=46.140..166.829 rows=14,801 loops=1)

  • Hash Cond: (lpz.lp_id = fc.zend_lp)
51. 82.002 82.002 ↓ 1.0 72,241 1

CTE Scan on valid_lp lpz (cost=0.00..1,378.76 rows=68,938 width=8) (actual time=0.001..82.002 rows=72,241 loops=1)

52. 26.236 45.959 ↑ 1.0 39,216 1

Hash (cost=3,525.16..3,525.16 rows=39,216 width=243) (actual time=45.959..45.959 rows=39,216 loops=1)

  • Buckets: 4096 Batches: 16 Memory Usage: 441kB
53. 19.723 19.723 ↑ 1.0 39,216 1

Seq Scan on forwardingconstruct fc (cost=0.00..3,525.16 rows=39,216 width=243) (actual time=0.009..19.723 rows=39,216 loops=1)

54. 11.113 35.202 ↓ 15.0 20,049 1

Hash (cost=152.98..152.98 rows=1,341 width=1,057) (actual time=35.202..35.202 rows=20,049 loops=1)

  • Buckets: 1024 Batches: 2 Memory Usage: 819kB
55. 13.440 24.089 ↓ 15.0 20,049 1

Hash Left Join (cost=122.64..152.98 rows=1,341 width=1,057) (actual time=2.483..24.089 rows=20,049 loops=1)

  • Hash Cond: (na.location_id = la.location_id)
56. 8.219 8.219 ↓ 15.0 20,049 1

CTE Scan on licensed_elements na (cost=0.00..26.82 rows=1,341 width=1,056) (actual time=0.028..8.219 rows=20,049 loops=1)

57. 1.295 2.430 ↑ 1.1 3,854 1

Hash (cost=68.95..68.95 rows=4,295 width=17) (actual time=2.430..2.430 rows=3,854 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 259kB
58. 1.135 1.135 ↑ 1.1 3,854 1

Seq Scan on location la (cost=0.00..68.95 rows=4,295 width=17) (actual time=0.021..1.135 rows=3,854 loops=1)

59. 1.443 2.688 ↑ 1.1 3,854 1

Hash (cost=68.95..68.95 rows=4,295 width=17) (actual time=2.688..2.688 rows=3,854 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 259kB
60. 1.245 1.245 ↑ 1.1 3,854 1

Seq Scan on location lz (cost=0.00..68.95 rows=4,295 width=17) (actual time=0.035..1.245 rows=3,854 loops=1)

61. 0.704 1.374 ↑ 1.0 1,910 1

Hash (cost=74.10..74.10 rows=1,910 width=16) (actual time=1.374..1.374 rows=1,910 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 110kB
62. 0.670 0.670 ↑ 1.0 1,910 1

Seq Scan on site sta (cost=0.00..74.10 rows=1,910 width=16) (actual time=0.021..0.670 rows=1,910 loops=1)

63. 0.678 1.321 ↑ 1.0 1,910 1

Hash (cost=74.10..74.10 rows=1,910 width=16) (actual time=1.321..1.321 rows=1,910 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 110kB
64. 0.643 0.643 ↑ 1.0 1,910 1

Seq Scan on site stz (cost=0.00..74.10 rows=1,910 width=16) (actual time=0.008..0.643 rows=1,910 loops=1)

Planning time : 5.667 ms
Execution time : 19,689.581 ms