explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eD4

Settings
# exclusive inclusive rows x rows loops node
1. 168.089 764,238.870 ↑ 9.9 3,974 1

Unique (cost=12,861.60..28,893,708,993.24 rows=39,216 width=3,654) (actual time=3,995.530..764,238.870 rows=3,974 loops=1)

2.          

CTE licensed_elements

3. 7.007 19.250 ↓ 1.6 19,403 1

Hash Join (cost=417.65..840.50 rows=12,509 width=180) (actual time=3.250..19.250 rows=19,403 loops=1)

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

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

5. 0.238 3.314 ↓ 1.4 2,513 1

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

6. 0.697 3.076 ↓ 1.4 2,513 1

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

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

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

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

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

9. 0.006 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.020 0.020 ↓ 1.9 13 1

Seq Scan on negroup (cost=0.00..1.25 rows=7 width=8) (actual time=0.015..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[]))
11. 0.481 1.371 ↑ 1.0 2,908 1

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

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

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

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

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

14. 2.669 4.011 ↓ 1.6 16,890 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.016 0.070 ↓ 1.7 19 1

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

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

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

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

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

20. 0.004 0.014 ↓ 1.9 13 1

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

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

Seq Scan on negroup negroup_1 (cost=0.00..1.25 rows=7 width=8) (actual time=0.006..0.010 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.004 0.012 ↓ 1.3 4 1

Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.011..0.012 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.007..0.008 rows=4 loops=1)

24. 0.902 1.757 ↑ 1.0 3,322 1

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

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

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

26.          

CTE valid_ltp

27. 41.731 41.731 ↓ 1.0 32,206 1

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

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

CTE valid_eq

29. 1.963 1.963 ↑ 1.0 356 1

Seq Scan on equipment eq (cost=0.00..320.65 rows=356 width=123) (actual time=0.014..1.963 rows=356 loops=1)

  • Filter: ((type IS NULL) OR ((type)::text = 'CHASSIS'::text))
  • Rows Removed by Filter: 9117
30.          

CTE valid_lp

31. 57.634 57.634 ↓ 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..57.634 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
32. 19,269.957 764,070.781 ↑ 1,302.8 1,770,503 1

Nested Loop Left Join (cost=12.98..28,887,929,406.18 rows=2,306,695,378 width=3,654) (actual time=3,995.526..764,070.781 rows=1,770,503 loops=1)

  • Join Filter: (nz.networkelement_id = eqz.networkelement_id)
  • Rows Removed by Join Filter: 125751189
33. 276.010 730,830.751 ↑ 3,617.7 358,207 1

Nested Loop Left Join (cost=12.98..18,478,966,507.60 rows=1,295,896,280 width=5,139) (actual time=3,995.430..730,830.751 rows=358,207 loops=1)

34. 273.619 730,554.741 ↑ 3,617.7 358,207 1

Nested Loop Left Join (cost=12.70..18,065,626,079.90 rows=1,295,896,280 width=5,139) (actual time=3,995.422..730,554.741 rows=358,207 loops=1)

35. 283.019 730,281.122 ↑ 3,617.7 358,207 1

Nested Loop Left Join (cost=12.43..17,652,285,652.20 rows=1,295,896,280 width=5,139) (actual time=3,995.415..730,281.122 rows=358,207 loops=1)

36. 424.538 729,639.896 ↑ 3,617.7 358,207 1

Nested Loop Left Join (cost=12.14..17,247,364,655.96 rows=1,295,896,280 width=5,138) (actual time=3,995.406..729,639.896 rows=358,207 loops=1)

37. 28,750.851 728,498.944 ↑ 3,617.7 358,207 1

Nested Loop (cost=11.86..16,842,443,659.71 rows=1,295,896,280 width=5,137) (actual time=3,995.371..728,498.944 rows=358,207 loops=1)

  • Join Filter: (na.networkelement_id = ltpa.networkelement_id)
  • Rows Removed by Join Filter: 412430654
38. 7,187.489 531,494.785 ↑ 565.4 20,589 1

Nested Loop (cost=0.29..1,182,488,780.33 rows=11,640,125 width=3,573) (actual time=3,985.123..531,494.785 rows=20,589 loops=1)

  • Join Filter: (ltpz.networkelement_id = nz.networkelement_id)
  • Rows Removed by Join Filter: 85469029
39. 26,442.970 509,265.212 ↑ 42.2 4,406 1

Nested Loop (cost=0.29..823,031,543.37 rows=186,108 width=2,525) (actual time=3,954.900..509,265.212 rows=4,406 loops=1)

  • Join Filter: (fc.zend_lp = lpz.lp_id)
  • Rows Removed by Join Filter: 318289440
40. 28,126.449 434,686.692 ↑ 23.5 4,406 1

Nested Loop (cost=0.29..634,612,316.27 rows=103,464 width=2,533) (actual time=3,940.694..434,686.692 rows=4,406 loops=1)

  • Join Filter: (fc.aend_lp = lpa.lp_id)
  • Rows Removed by Join Filter: 338227956
41. 16,879.875 355,428.121 ↑ 12.3 4,682 1

Nested Loop (cost=0.29..421,686,931.33 rows=57,453 width=2,323) (actual time=3,865.598..355,428.121 rows=4,682 loops=1)

  • Join Filter: (fc.zend = ltpz.ltp_id)
  • Rows Removed by Join Filter: 197063832
42. 106,246.276 306,698.851 ↑ 7.8 6,119 1

Nested Loop (cost=0.29..190,781,254.28 rows=47,467 width=1,283) (actual time=3,858.962..306,698.851 rows=6,119 loops=1)

  • Join Filter: (fc.aend = ltpa.ltp_id)
  • Rows Removed by Join Filter: 1262984377
43. 372.543 372.543 ↑ 1.0 39,216 1

Index Scan using forwardingconstruct_pkey on forwardingconstruct fc (cost=0.29..13,659.99 rows=39,216 width=243) (actual time=0.046..372.543 rows=39,216 loops=1)

44. 200,080.032 200,080.032 ↓ 1.0 32,206 39,216

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

45. 31,849.395 31,849.395 ↓ 1.0 32,206 6,119

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

46. 51,132.122 51,132.122 ↓ 1.0 72,241 4,682

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

47. 48,135.550 48,135.550 ↓ 1.0 72,241 4,406

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

48. 15,042.084 15,042.084 ↓ 1.6 19,403 4,406

CTE Scan on licensed_elements nz (cost=0.00..250.18 rows=12,509 width=1,056) (actual time=0.006..3.414 rows=19,403 loops=4,406)

49. 95,489.469 168,253.308 ↑ 1.1 20,049 20,589

Hash Left Join (cost=11.57..1,078.59 rows=22,266 width=1,572) (actual time=0.003..8.172 rows=20,049 loops=20,589)

  • Hash Cond: (na.networkelement_id = eqa.networkelement_id)
50. 72,761.526 72,761.526 ↓ 1.6 19,403 20,589

CTE Scan on licensed_elements na (cost=0.00..250.18 rows=12,509 width=1,056) (actual time=0.002..3.534 rows=19,403 loops=20,589)

51. 0.083 2.313 ↑ 1.0 356 1

Hash (cost=7.12..7.12 rows=356 width=524) (actual time=2.313..2.313 rows=356 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
52. 2.230 2.230 ↑ 1.0 356 1

CTE Scan on valid_eq eqa (cost=0.00..7.12 rows=356 width=524) (actual time=0.017..2.230 rows=356 loops=1)

53. 716.414 716.414 ↑ 1.0 1 358,207

Index Scan using location_pkey on location la (cost=0.28..0.31 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=358,207)

  • Index Cond: (location_id = na.location_id)
54. 358.207 358.207 ↑ 1.0 1 358,207

Index Scan using location_pkey on location lz (cost=0.28..0.31 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=358,207)

  • Index Cond: (location_id = nz.location_id)
55. 0.000 0.000 ↓ 0.0 0 358,207

Index Scan using site_pkey on site sta (cost=0.28..0.32 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=358,207)

  • Index Cond: (na.site_id = site_id)
56. 0.000 0.000 ↓ 0.0 0 358,207

Index Scan using site_pkey on site stz (cost=0.28..0.32 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=358,207)

  • Index Cond: (nz.site_id = site_id)
57. 13,970.073 13,970.073 ↑ 1.0 356 358,207

CTE Scan on valid_eq eqz (cost=0.00..7.12 rows=356 width=524) (actual time=0.000..0.039 rows=356 loops=358,207)

Planning time : 7.529 ms
Execution time : 764,248.077 ms