explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r75T

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 25,108.141 ↓ 0.0 0 1

Insert on target_highpriority (cost=368,912.00..368,921.49 rows=2 width=144) (actual time=25,108.141..25,108.141 rows=0 loops=1)

2.          

CTE attype

3. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on attributetype (cost=0.00..1.27 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (type = 'avoid'::text)
  • Rows Removed by Filter: 21
4.          

CTE attype_trusted

5. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on attributetype attributetype_1 (cost=0.00..1.27 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: (type = 'trusted'::text)
  • Rows Removed by Filter: 21
6.          

CTE attype_target

7. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on attributetype attributetype_2 (cost=0.00..1.27 rows=1 width=4) (actual time=0.014..0.016 rows=1 loops=1)

  • Filter: (type = 'target'::text)
  • Rows Removed by Filter: 21
8.          

CTE targets

9. 31.144 25,106.403 ↓ 6,841.0 13,682 1

Sort (cost=368,889.31..368,889.31 rows=2 width=121) (actual time=25,104.725..25,106.403 rows=13,682 loops=1)

  • Sort Key: targets.zone_id, targets.collector_id, st_1.id, targets.cidrval
  • Sort Method: quicksort Memory: 2153kB
10.          

CTE protocolcfg

11. 0.012 4.683 ↓ 14.0 14 1

Hash Join (cost=414.69..417.54 rows=1 width=83) (actual time=4.568..4.683 rows=14 loops=1)

  • Hash Cond: ((c.zone_id = cfg.zone_id) AND (c.collector_id = cfg.collector_id))
12. 0.135 4.491 ↑ 2.1 14 1

GroupAggregate (cost=403.21..405.53 rows=30 width=72) (actual time=4.380..4.491 rows=14 loops=1)

  • Group Key: c.zone_id, c.collector_id, (regexp_replace(c.key, '^(.+Discovery)\..+'::text, '\1'::text))
13. 0.041 4.356 ↑ 2.3 42 1

Sort (cost=403.21..403.46 rows=98 width=70) (actual time=4.349..4.356 rows=42 loops=1)

  • Sort Key: c.zone_id, c.collector_id, (regexp_replace(c.key, '^(.+Discovery)\..+'::text, '\1'::text))
  • Sort Method: quicksort Memory: 29kB
14. 4.085 4.315 ↑ 2.3 42 1

Nested Loop (cost=0.00..399.97 rows=98 width=70) (actual time=0.383..4.315 rows=42 loops=1)

  • Join Filter: (c.key ~ (('.+\.'::text || (unnest('{udp,icmp,dns,snmp,udpPorts,tcpPorts}'::text[]))) || '$'::text))
  • Rows Removed by Join Filter: 1140
15. 0.033 0.033 ↑ 1.0 197 1

Seq Scan on config c (cost=0.00..3.97 rows=197 width=38) (actual time=0.003..0.033 rows=197 loops=1)

16. 0.192 0.197 ↑ 16.7 6 197

Materialize (cost=0.00..2.01 rows=100 width=32) (actual time=0.000..0.001 rows=6 loops=197)

17. 0.005 0.005 ↑ 16.7 6 1

Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.003..0.005 rows=6 loops=1)

18. 0.004 0.180 ↑ 1.0 17 1

Hash (cost=11.22..11.22 rows=17 width=19) (actual time=0.180..0.180 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.105 0.176 ↑ 1.0 17 1

HashAggregate (cost=10.88..11.05 rows=17 width=19) (actual time=0.164..0.176 rows=17 loops=1)

  • Group Key: cfg.collector_id, cfg.zone_id
  • Filter: (bool_or(((cfg.key = 'enabled'::text) AND (cfg.value = 'true'::text))) AND (NOT bool_or(((cfg.key = 'archived'::text) AND (cfg.value = 'true'::text)))))
  • Rows Removed by Filter: 2
20. 0.071 0.071 ↑ 1.2 34 1

Seq Scan on config cfg (cost=0.00..6.68 rows=41 width=38) (actual time=0.006..0.071 rows=34 loops=1)

  • Filter: (key = ANY ('{pathDiscovery.traceDiscoveredRoutes,pathDiscovery.traceToHosts,hostDiscovery.targetDiscoveredRoutes,leakDiscovery.doInbound,leakDiscovery.doOutbound,leakDiscovery.enabled,hostDiscovery.enabled,pathDiscovery.enabled,dnsDiscovery.enabled,enabled,archived}'::text[]))
  • Rows Removed by Filter: 163
21.          

CTE devs

22. 3.854 57.446 ↓ 86.7 8,668 1

Nested Loop (cost=0.43..446.50 rows=100 width=51) (actual time=8.167..57.446 rows=8,668 loops=1)

23. 10.242 10.242 ↓ 86.7 8,670 1

Function Scan on json_to_recordset (cost=0.00..1.00 rows=100 width=36) (actual time=8.148..10.242 rows=8,670 loops=1)

24. 43.350 43.350 ↑ 1.0 1 8,670

Index Scan using pk_device on device d (cost=0.43..4.45 rows=1 width=15) (actual time=0.004..0.005 rows=1 loops=8,670)

  • Index Cond: (id = json_to_recordset.device_id)
  • Filter: (ip IS NOT NULL)
25.          

CTE rts

26. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=72) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
27.          

CTE targets

28. 166.124 21,825.951 ↓ 6.0 96,873 1

HashAggregate (cost=342,087.35..342,249.67 rows=16,232 width=149) (actual time=21,789.150..21,825.951 rows=96,873 loops=1)

  • Group Key: "*SELECT* 1".zone_id, d_1.priority, "*SELECT* 1".collector_id, d_1.id, (NULL::integer), (NULL::integer), d_1.ip, ('snmpDiscovery'::text), "*SELECT* 1".config, (false)
29. 22.307 21,659.827 ↓ 15.8 256,057 1

Append (cost=2,216.40..341,681.55 rows=16,232 width=149) (actual time=1,047.652..21,659.827 rows=256,057 loops=1)

30. 37.779 1,089.064 ↓ 228,528.0 228,528 1

Hash Join (cost=2,216.40..2,229.66 rows=1 width=149) (actual time=1,047.650..1,089.064 rows=228,528 loops=1)

  • Hash Cond: (d_1.zone_id = "*SELECT* 1".zone_id)
  • Join Filter: (((CASE WHEN dv.trusted THEN NULL::integer ELSE dr.collector_id END) IS NULL) OR ((CASE WHEN dv.trusted THEN NULL::integer ELSE dr.collector_id END) = "*SELECT* 1".collector_id))
31. 38.094 1,036.634 ↓ 27.7 14,283 1

HashAggregate (cost=1,789.37..1,794.52 rows=515 width=81) (actual time=1,032.984..1,036.634 rows=14,283 loops=1)

  • Group Key: d_1.zone_id, d_1.priority, dv.trusted, dr.collector_id, d_1.id, d_1.ip
32. 12.826 998.540 ↓ 82.3 42,360 1

Nested Loop Left Join (cost=1.28..1,781.65 rows=515 width=77) (actual time=8.196..998.540 rows=42,360 loops=1)

33. 8.334 136.250 ↓ 109.7 8,668 1

Nested Loop (cost=0.85..491.87 rows=79 width=73) (actual time=8.182..136.250 rows=8,668 loops=1)

34. 4.085 93.244 ↓ 94.2 8,668 1

Nested Loop (cost=0.43..447.50 rows=92 width=76) (actual time=8.173..93.244 rows=8,668 loops=1)

35. 63.155 63.155 ↓ 86.7 8,668 1

CTE Scan on devs d_1 (cost=0.00..2.00 rows=100 width=72) (actual time=8.169..63.155 rows=8,668 loops=1)

36. 26.004 26.004 ↑ 1.0 1 8,668

Index Scan using pk_device on device zd (cost=0.43..4.45 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=8,668)

  • Index Cond: (id = d_1.id)
  • Filter: ((NOT meta) AND (ip IS NOT NULL))
37. 34.672 34.672 ↑ 1.0 1 8,668

Index Scan using idx_device_values_devid on device_values dv (cost=0.42..0.47 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=8,668)

  • Index Cond: (device_id = zd.id)
  • Filter: (target OR trusted)
38. 849.464 849.464 ↑ 1.4 5 8,668

Index Scan using idx_device_response_devid on device_response dr (cost=0.43..16.26 rows=7 width=8) (actual time=0.020..0.098 rows=5 loops=8,668)

  • Index Cond: (device_id = d_1.id)
39. 0.008 14.651 ↑ 2.0 16 1

Hash (cost=426.62..426.62 rows=32 width=72) (actual time=14.651..14.651 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
40. 0.006 14.643 ↑ 2.0 16 1

Group (cost=425.90..426.30 rows=32 width=72) (actual time=14.636..14.643 rows=16 loops=1)

  • Group Key: "*SELECT* 1".zone_id, "*SELECT* 1".collector_id, ('snmpDiscovery'::text), "*SELECT* 1".config
41. 0.015 14.637 ↑ 2.0 16 1

Sort (cost=425.90..425.98 rows=32 width=72) (actual time=14.634..14.637 rows=16 loops=1)

  • Sort Key: "*SELECT* 1".zone_id, "*SELECT* 1".collector_id, ('snmpDiscovery'::text), "*SELECT* 1".config
  • Sort Method: quicksort Memory: 26kB
42. 0.052 14.622 ↑ 2.0 16 1

HashAggregate (cost=424.46..424.78 rows=32 width=72) (actual time=14.616..14.622 rows=16 loops=1)

  • Group Key: "*SELECT* 1".zone_id, "*SELECT* 1".collector_id, ('snmpDiscovery'::text), "*SELECT* 1".config
43. 0.004 14.570 ↑ 2.0 16 1

Append (cost=43.47..424.14 rows=32 width=72) (actual time=8.956..14.570 rows=16 loops=1)

44. 0.002 8.977 ↑ 7.0 2 1

Subquery Scan on *SELECT* 1 (cost=43.47..44.03 rows=14 width=72) (actual time=8.955..8.977 rows=2 loops=1)

45. 0.050 8.975 ↑ 7.0 2 1

Group (cost=43.47..43.89 rows=14 width=104) (actual time=8.954..8.975 rows=2 loops=1)

  • Group Key: sa.zone_id, sa.collector_id, (array_agg(DISTINCT sa.alias ORDER BY sa.alias))
46. 0.009 8.925 ↑ 7.0 2 1

Sort (cost=43.47..43.51 rows=14 width=40) (actual time=8.925..8.925 rows=2 loops=1)

  • Sort Key: sa.zone_id, sa.collector_id, (array_agg(DISTINCT sa.alias ORDER BY sa.alias))
  • Sort Method: quicksort Memory: 26kB
47. 0.012 8.916 ↑ 7.0 2 1

Hash Join (cost=35.26..43.21 rows=14 width=40) (actual time=8.721..8.916 rows=2 loops=1)

  • Hash Cond: (sa.collector_id = phases.collector_id)
48. 0.212 8.742 ↑ 66.7 3 1

GroupAggregate (cost=29.43..34.49 rows=200 width=40) (actual time=8.550..8.742 rows=3 loops=1)

  • Group Key: sa.zone_id, sa.collector_id
49. 0.057 8.530 ↑ 4.3 60 1

Sort (cost=29.43..30.07 rows=256 width=40) (actual time=8.513..8.530 rows=60 loops=1)

  • Sort Key: sa.zone_id, sa.collector_id
  • Sort Method: quicksort Memory: 28kB
50. 0.109 8.473 ↑ 4.3 60 1

HashAggregate (cost=14.07..16.63 rows=256 width=40) (actual time=8.451..8.473 rows=60 loops=1)

  • Group Key: sa.zone_id, sa.collector_id, sa.alias
51. 0.035 8.364 ↑ 2.3 112 1

Append (cost=2.05..12.15 rows=256 width=40) (actual time=0.077..8.364 rows=112 loops=1)

52. 0.085 0.098 ↓ 1.1 60 1

HashAggregate (cost=2.05..2.61 rows=56 width=15) (actual time=0.076..0.098 rows=60 loops=1)

  • Group Key: sa.zone_id, sa.collector_id, sa.alias
53. 0.013 0.013 ↑ 1.0 60 1

Seq Scan on snmpalias sa (cost=0.00..1.60 rows=60 width=15) (actual time=0.008..0.013 rows=60 loops=1)

54. 0.036 8.231 ↑ 3.8 52 1

Nested Loop (cost=0.00..6.98 rows=200 width=40) (actual time=8.176..8.231 rows=52 loops=1)

55. 8.135 8.135 ↑ 1.0 1 1

Seq Scan on snmpaliasgroup sg (cost=0.00..1.01 rows=1 width=604) (actual time=8.134..8.135 rows=1 loops=1)

  • Filter: (name = 'common'::text)
56. 0.060 0.060 ↑ 2.0 1 1

Seq Scan on config c_1 (cost=0.00..4.96 rows=2 width=8) (actual time=0.027..0.060 rows=1 loops=1)

  • Filter: ((key = 'snmpDiscovery.useCommonCredentials'::text) AND (value = 'true'::text))
  • Rows Removed by Filter: 196
57. 0.004 0.162 ↑ 7.0 2 1

Hash (cost=5.65..5.65 rows=14 width=4) (actual time=0.162..0.162 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
58. 0.002 0.158 ↑ 7.0 2 1

Subquery Scan on phases (cost=5.37..5.65 rows=14 width=4) (actual time=0.153..0.158 rows=2 loops=1)

59. 0.065 0.156 ↑ 7.0 2 1

HashAggregate (cost=5.37..5.51 rows=14 width=11) (actual time=0.153..0.156 rows=2 loops=1)

  • Group Key: cfg_1.collector_id, cfg_1.zone_id
  • Filter: (bool_or(((cfg_1.key = 'snmpDiscovery.enabled'::text) AND (cfg_1.value = 'true'::text))) AND bool_or(((cfg_1.key = 'enabled'::text) AND (cfg_1.value = 'true'::text))) AND (NOT bool_or(((cfg_1.key = 'archived'::text) AND (cfg_1.value = 'true'::text)))))
  • Rows Removed by Filter: 17
60. 0.091 0.091 ↑ 1.0 24 1

Seq Scan on config cfg_1 (cost=0.00..4.71 rows=24 width=38) (actual time=0.023..0.091 rows=24 loops=1)

  • Filter: (key = ANY ('{snmpDiscovery.enabled,enabled,archived}'::text[]))
  • Rows Removed by Filter: 173
61. 0.032 0.883 ↑ 2.1 7 1

Nested Loop Left Join (cost=322.03..379.86 rows=15 width=72) (actual time=0.688..0.883 rows=7 loops=1)

  • Join Filter: (cvp.collector_id = c_2.id)
  • Rows Removed by Join Filter: 42
62. 0.015 0.816 ↑ 2.1 7 1

Nested Loop Left Join (cost=322.03..373.70 rows=15 width=86) (actual time=0.671..0.816 rows=7 loops=1)

  • Join Filter: (cip.collector_id = c_2.id)
  • Rows Removed by Join Filter: 42
63. 0.001 0.766 ↑ 2.1 7 1

Nested Loop Left Join (cost=322.03..367.83 rows=15 width=63) (actual time=0.662..0.766 rows=7 loops=1)

  • Join Filter: (cst.collector_id = c_2.id)
64. 0.013 0.737 ↑ 2.1 7 1

Hash Right Join (cost=322.03..362.65 rows=15 width=40) (actual time=0.635..0.737 rows=7 loops=1)

  • Hash Cond: (cpe.collector_id = c_2.id)
65. 0.100 0.612 ↑ 2.7 7 1

GroupAggregate (cost=314.22..354.51 rows=19 width=40) (actual time=0.515..0.612 rows=7 loops=1)

  • Group Key: cpe.zone_id, cpe.collector_id
66. 0.060 0.512 ↑ 31.7 126 1

Sort (cost=314.22..324.22 rows=4,000 width=12) (actual time=0.493..0.512 rows=126 loops=1)

  • Sort Key: cpe.zone_id, cpe.collector_id
  • Sort Method: quicksort Memory: 30kB
67. 0.235 0.452 ↑ 31.7 126 1

Hash Join (cost=9.61..34.91 rows=4,000 width=12) (actual time=0.214..0.452 rows=126 loops=1)

  • Hash Cond: ((cpe.key = (regexp_split_to_array(unnest('{profileDiscovery.collectHTTP:profileDiscovery.httpPorts,profileDiscovery.collectHTTP:profileDiscovery.httpsPorts,portDiscovery.enabled:portDiscovery.tcpPorts}'::text[]), '\:'::text))[1]) AND (cpe.collector_id = cpp.collector_id))
68. 0.044 0.044 ↑ 1.0 104 1

Seq Scan on config cpe (cost=0.00..4.46 rows=104 width=31) (actual time=0.004..0.044 rows=104 loops=1)

  • Filter: (value = 'true'::text)
  • Rows Removed by Filter: 93
69. 0.011 0.173 ↑ 3.1 21 1

Hash (cost=8.62..8.62 rows=66 width=43) (actual time=0.173..0.173 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
70. 0.050 0.162 ↑ 3.1 21 1

Hash Join (cost=2.76..8.62 rows=66 width=43) (actual time=0.073..0.162 rows=21 loops=1)

  • Hash Cond: (cpp.key = (regexp_split_to_array(unnest('{profileDiscovery.collectHTTP:profileDiscovery.httpPorts,profileDiscovery.collectHTTP:profileDiscovery.httpsPorts,portDiscovery.enabled:portDiscovery.tcpPorts}'::text[]), '\:'::text))[2])
71. 0.053 0.053 ↓ 2.8 183 1

Seq Scan on config cpp (cost=0.00..4.96 rows=66 width=34) (actual time=0.006..0.053 rows=183 loops=1)

  • Filter: (length(value) > 0)
  • Rows Removed by Filter: 14
72. 0.003 0.059 ↑ 33.3 3 1

Hash (cost=1.51..1.51 rows=100 width=32) (actual time=0.059..0.059 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
73. 0.056 0.056 ↑ 33.3 3 1

Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.046..0.056 rows=3 loops=1)

74. 0.002 0.112 ↑ 2.1 7 1

Hash (cost=7.63..7.63 rows=15 width=8) (actual time=0.112..0.112 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
75. 0.019 0.110 ↑ 2.1 7 1

Hash Join (cost=5.94..7.63 rows=15 width=8) (actual time=0.097..0.110 rows=7 loops=1)

  • Hash Cond: (c_2.id = phases_1.collector_id)
76. 0.009 0.009 ↑ 1.0 39 1

Seq Scan on collector c_2 (cost=0.00..1.39 rows=39 width=8) (actual time=0.006..0.009 rows=39 loops=1)

77. 0.001 0.082 ↑ 2.1 7 1

Hash (cost=5.75..5.75 rows=15 width=4) (actual time=0.082..0.082 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
78. 0.005 0.081 ↑ 2.1 7 1

Subquery Scan on phases_1 (cost=5.45..5.75 rows=15 width=4) (actual time=0.075..0.081 rows=7 loops=1)

79. 0.036 0.076 ↑ 2.1 7 1

HashAggregate (cost=5.45..5.60 rows=15 width=11) (actual time=0.073..0.076 rows=7 loops=1)

  • Group Key: cfg_2.collector_id, cfg_2.zone_id
  • Filter: (bool_or(((cfg_2.key = 'portDiscovery.enabled'::text) AND (cfg_2.value = 'true'::text))) AND bool_or(((cfg_2.key = 'enabled'::text) AND (cfg_2.value = 'true'::text))) AND (NOT bool_or(((cfg_2.key = 'archived'::text) AND (cfg_2.value = 'true'::text)))))
  • Rows Removed by Filter: 12
80. 0.040 0.040 ↑ 1.0 27 1

Seq Scan on config cfg_2 (cost=0.00..4.71 rows=27 width=38) (actual time=0.006..0.040 rows=27 loops=1)

  • Filter: (key = ANY ('{portDiscovery.enabled,enabled,archived}'::text[]))
  • Rows Removed by Filter: 170
81. 0.005 0.028 ↓ 0.0 0 7

Materialize (cost=0.00..4.96 rows=1 width=27) (actual time=0.004..0.004 rows=0 loops=7)

82. 0.023 0.023 ↓ 0.0 0 1

Seq Scan on config cst (cost=0.00..4.96 rows=1 width=27) (actual time=0.023..0.023 rows=0 loops=1)

  • Filter: ((key = 'profileDiscovery.scanStandardTCPPorts'::text) AND (value = 'true'::text))
  • Rows Removed by Filter: 197
83. 0.007 0.035 ↓ 1.8 7 7

Materialize (cost=0.00..4.97 rows=4 width=27) (actual time=0.001..0.005 rows=7 loops=7)

84. 0.028 0.028 ↓ 1.8 7 1

Seq Scan on config cip (cost=0.00..4.96 rows=4 width=27) (actual time=0.005..0.028 rows=7 loops=1)

  • Filter: ((key = 'portDiscovery.useInfectionPorts'::text) AND (value = 'true'::text))
  • Rows Removed by Filter: 190
85. 0.006 0.035 ↓ 1.8 7 7

Materialize (cost=0.00..4.97 rows=4 width=27) (actual time=0.001..0.005 rows=7 loops=7)

86. 0.029 0.029 ↓ 1.8 7 1

Seq Scan on config cvp (cost=0.00..4.96 rows=4 width=27) (actual time=0.006..0.029 rows=7 loops=1)

  • Filter: ((key = 'portDiscovery.useVulnerablePorts'::text) AND (value = 'true'::text))
  • Rows Removed by Filter: 190
87. 4.701 4.701 ↓ 7.0 7 1

CTE Scan on protocolcfg (cost=0.00..0.02 rows=1 width=72) (actual time=4.581..4.701 rows=7 loops=1)

  • Filter: dnsenabled
  • Rows Removed by Filter: 7
88. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on protocolcfg protocolcfg_1 (cost=0.00..0.02 rows=1 width=72) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (inboundleak AND leakenabled AND (scantype = 'leakDiscovery'::text))
  • Rows Removed by Filter: 14
89. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on protocolcfg protocolcfg_2 (cost=0.00..0.02 rows=1 width=72) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (outboundleak AND leakenabled AND (scantype = 'leakDiscovery'::text))
  • Rows Removed by Filter: 14
90. 0.735 11,800.450 ↓ 1,197.0 1,197 1

Subquery Scan on http (cost=460.77..460.82 rows=1 width=149) (actual time=11,798.537..11,800.450 rows=1,197 loops=1)

91. 0.926 11,799.715 ↓ 1,197.0 1,197 1

Group (cost=460.77..460.79 rows=1 width=140) (actual time=11,798.521..11,799.715 rows=1,197 loops=1)

  • Group Key: htp.zone_id, htp.priority, htp.collector_id, htp.device_id, htp.ip, htp.http, htp.https
92. 8.012 11,798.789 ↓ 2,394.0 2,394 1

Sort (cost=460.77..460.78 rows=1 width=140) (actual time=11,798.518..11,798.789 rows=2,394 loops=1)

  • Sort Key: htp.zone_id, htp.priority, htp.collector_id, htp.device_id, htp.ip, htp.http, htp.https
  • Sort Method: quicksort Memory: 383kB
93. 1.290 11,790.777 ↓ 2,394.0 2,394 1

Subquery Scan on htp (cost=17.71..460.76 rows=1 width=140) (actual time=106.919..11,790.777 rows=2,394 loops=1)

94. 87.703 11,789.487 ↓ 2,394.0 2,394 1

Nested Loop (cost=17.71..460.75 rows=1 width=140) (actual time=106.917..11,789.487 rows=2,394 loops=1)

  • Join Filter: (d_2.id = dv_1.device_id)
95. 45.237 11,687.420 ↓ 2,394.0 2,394 1

Hash Join (cost=17.28..459.73 rows=1 width=174) (actual time=106.144..11,687.420 rows=2,394 loops=1)

  • Hash Cond: (dp.zone_id = ports.zone_id)
  • Join Filter: (dp.open && (ports.http || ports.https))
  • Rows Removed by Join Filter: 28364
96. 11.848 11,641.300 ↓ 43.9 4,394 1

Nested Loop (cost=0.42..441.00 rows=100 width=106) (actual time=39.786..11,641.300 rows=4,394 loops=1)

97. 5.664 5.664 ↓ 86.7 8,668 1

CTE Scan on devs d_2 (cost=0.00..2.00 rows=100 width=68) (actual time=0.001..5.664 rows=8,668 loops=1)

98. 11,623.788 11,623.788 ↑ 1.0 1 8,668

Index Scan using pk_device_ports on device_ports dp (cost=0.42..4.38 rows=1 width=38) (actual time=1.340..1.341 rows=1 loops=8,668)

  • Index Cond: (device_id = d_2.id)
99. 0.008 0.883 ↑ 2.1 7 1

Hash (cost=16.67..16.67 rows=15 width=72) (actual time=0.883..0.883 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
100. 0.004 0.875 ↑ 2.1 7 1

Subquery Scan on ports (cost=16.15..16.67 rows=15 width=72) (actual time=0.749..0.875 rows=7 loops=1)

101. 0.191 0.871 ↑ 2.1 7 1

HashAggregate (cost=16.15..16.52 rows=15 width=86) (actual time=0.747..0.871 rows=7 loops=1)

  • Group Key: ce.zone_id, ce.collector_id, cep.value, ces.value
102. 0.011 0.680 ↑ 2.1 7 1

Hash Left Join (cost=10.92..16.00 rows=15 width=22) (actual time=0.512..0.680 rows=7 loops=1)

  • Hash Cond: (ce.collector_id = ces.collector_id)
103. 0.023 0.454 ↑ 2.1 7 1

Hash Right Join (cost=5.94..10.92 rows=15 width=15) (actual time=0.289..0.454 rows=7 loops=1)

  • Hash Cond: (cep.collector_id = ce.collector_id)
104. 0.224 0.224 ↓ 3.5 7 1

Seq Scan on config cep (cost=0.00..4.96 rows=2 width=11) (actual time=0.073..0.224 rows=7 loops=1)

  • Filter: ((value ~ '^[0-9,]+$'::text) AND (key = 'profileDiscovery.httpPorts'::text))
  • Rows Removed by Filter: 190
105. 0.006 0.207 ↑ 2.1 7 1

Hash (cost=5.75..5.75 rows=15 width=8) (actual time=0.207..0.207 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
106. 0.006 0.201 ↑ 2.1 7 1

Subquery Scan on ce (cost=5.45..5.75 rows=15 width=8) (actual time=0.191..0.201 rows=7 loops=1)

107. 0.078 0.195 ↑ 2.1 7 1

HashAggregate (cost=5.45..5.60 rows=15 width=11) (actual time=0.190..0.195 rows=7 loops=1)

  • Group Key: cfg_3.collector_id, cfg_3.zone_id
  • Filter: (bool_or(((cfg_3.key = 'profileDiscovery.collectHTTP'::text) AND (cfg_3.value = 'true'::text))) AND bool_or(((cfg_3.key = 'enabled'::text) AND (cfg_3.value = 'true'::text))) AND (NOT bool_or(((cfg_3.key = 'archived'::text) AND (cfg_3.value = 'true'::text)))))
  • Rows Removed by Filter: 12
108. 0.117 0.117 ↑ 1.0 27 1

Seq Scan on config cfg_3 (cost=0.00..4.71 rows=27 width=38) (actual time=0.059..0.117 rows=27 loops=1)

  • Filter: (key = ANY ('{profileDiscovery.collectHTTP,enabled,archived}'::text[]))
  • Rows Removed by Filter: 170
109. 0.005 0.215 ↓ 3.5 7 1

Hash (cost=4.96..4.96 rows=2 width=11) (actual time=0.215..0.215 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
110. 0.210 0.210 ↓ 3.5 7 1

Seq Scan on config ces (cost=0.00..4.96 rows=2 width=11) (actual time=0.060..0.210 rows=7 loops=1)

  • Filter: ((value ~ '^[0-9,]+$'::text) AND (key = 'profileDiscovery.httpsPorts'::text))
  • Rows Removed by Filter: 190
111. 14.364 14.364 ↑ 1.0 1 2,394

Index Scan using idx_device_values_devid on device_values dv_1 (cost=0.42..0.51 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=2,394)

  • Index Cond: (device_id = dp.device_id)
  • Filter: (target OR trusted)
112. 0.084 733.727 ↑ 73.7 218 1

Subquery Scan on cifsp (cost=15,764.66..20,141.55 rows=16,062 width=149) (actual time=732.722..733.727 rows=218 loops=1)

113. 1.219 733.643 ↑ 73.7 218 1

HashAggregate (cost=15,764.66..19,940.78 rows=16,062 width=138) (actual time=732.716..733.643 rows=218 loops=1)

  • Group Key: dr_1.collector_id, d_3.priority, cfg_4.zone_id, d_3.id, d_3.ip, dp_1.open
114.          

Initplan (forHashAggregate)

115. 0.020 0.020 ↑ 1.0 1 1

CTE Scan on attype_target (cost=0.00..0.02 rows=1 width=4) (actual time=0.017..0.020 rows=1 loops=1)

116. 0.007 0.007 ↑ 1.0 1 1

CTE Scan on attype_trusted (cost=0.00..0.02 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

117. 0.251 732.397 ↑ 36.8 436 1

Nested Loop Left Join (cost=6.78..15,523.69 rows=16,062 width=106) (actual time=703.793..732.397 rows=436 loops=1)

  • Filter: ((cc.collector_id IS NOT NULL) OR (ct.cidrval IS NOT NULL))
118. 0.553 718.194 ↓ 3.8 436 1

Nested Loop Left Join (cost=6.49..2,128.69 rows=114 width=110) (actual time=703.747..718.194 rows=436 loops=1)

  • Join Filter: (dr_1.collector_id = cc.collector_id)
  • Rows Removed by Join Filter: 436
119. 0.465 704.125 ↓ 31.1 436 1

Nested Loop (cost=6.20..478.75 rows=14 width=106) (actual time=703.656..704.125 rows=436 loops=1)

  • Join Filter: (dr_1.collector_id = cfg_4.collector_id)
  • Rows Removed by Join Filter: 2616
120. 0.046 0.111 ↑ 2.1 7 1

GroupAggregate (cost=5.35..6.31 rows=15 width=11) (actual time=0.076..0.111 rows=7 loops=1)

  • Group Key: cfg_4.collector_id, cfg_4.zone_id
  • Filter: (bool_or(((cfg_4.key = 'profileDiscovery.collectCIFS'::text) AND (cfg_4.value = 'true'::text))) AND bool_or(((cfg_4.key = 'enabled'::text) AND (cfg_4.value = 'true'::text))) AND (NOT bool_or(((cfg_4.key = 'archived'::text) AND (cfg_4.value = 'true'::text)))))
  • Rows Removed by Filter: 12
121. 0.021 0.065 ↑ 1.0 27 1

Sort (cost=5.35..5.42 rows=27 width=38) (actual time=0.060..0.065 rows=27 loops=1)

  • Sort Key: cfg_4.collector_id, cfg_4.zone_id
  • Sort Method: quicksort Memory: 27kB
122. 0.044 0.044 ↑ 1.0 27 1

Seq Scan on config cfg_4 (cost=0.00..4.71 rows=27 width=38) (actual time=0.019..0.044 rows=27 loops=1)

  • Filter: (key = ANY ('{profileDiscovery.collectCIFS,enabled,archived}'::text[]))
  • Rows Removed by Filter: 170
123. 0.538 703.549 ↓ 27.2 436 7

Materialize (cost=0.85..468.73 rows=16 width=102) (actual time=4.144..100.507 rows=436 loops=7)

124. 2.234 703.011 ↓ 27.2 436 1

Nested Loop (cost=0.85..468.65 rows=16 width=102) (actual time=29.002..703.011 rows=436 loops=1)

  • Join Filter: (d_3.id = dp_1.device_id)
125. 1.844 678.047 ↓ 126.3 4,546 1

Nested Loop (cost=0.43..450.22 rows=36 width=76) (actual time=17.674..678.047 rows=4,546 loops=1)

126. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on scantype st (cost=0.00..1.23 rows=1 width=4) (actual time=0.008..0.011 rows=1 loops=1)

  • Filter: (scantype = 'tcpPorts'::text)
  • Rows Removed by Filter: 17
127. 5.613 676.192 ↓ 45.5 4,546 1

Nested Loop (cost=0.43..448.00 rows=100 width=80) (actual time=17.662..676.192 rows=4,546 loops=1)

128. 3.143 3.143 ↓ 86.7 8,668 1

CTE Scan on devs d_3 (cost=0.00..2.00 rows=100 width=68) (actual time=0.001..3.143 rows=8,668 loops=1)

129. 667.436 667.436 ↑ 1.0 1 8,668

Index Scan using idx_device_response_devst on device_response dr_1 (cost=0.43..4.45 rows=1 width=12) (actual time=0.077..0.077 rows=1 loops=8,668)

  • Index Cond: ((scantype_id = st.id) AND (device_id = d_3.id))
130. 22.730 22.730 ↓ 0.0 0 4,546

Index Scan using pk_device_ports on device_ports dp_1 (cost=0.42..0.50 rows=1 width=34) (actual time=0.005..0.005 rows=0 loops=4,546)

  • Index Cond: (device_id = dr_1.device_id)
  • Filter: (open && '{445,139}'::integer[])
  • Rows Removed by Filter: 1
131. 13.516 13.516 ↑ 141.0 1 436

Index Scan using idx_zone_attribute_cidr_ipr on attribute_cidr cc (cost=0.29..116.09 rows=141 width=11) (actual time=0.023..0.031 rows=1 loops=436)

  • Index Cond: (iprange(cidrval) >>= iprange((d_3.ip)::cidr))
  • Filter: (attributetype_id = $7)
  • Rows Removed by Filter: 16
132. 13.952 13.952 ↑ 141.0 1 436

Index Scan using idx_zone_attribute_cidr_ipr on attribute_cidr ct (cost=0.29..116.09 rows=141 width=7) (actual time=0.019..0.032 rows=1 loops=436)

  • Index Cond: (iprange(cidrval) >>= iprange((d_3.ip)::cidr))
  • Filter: (attributetype_id = $8)
  • Rows Removed by Filter: 16
133. 0.063 653.362 ↓ 110.0 110 1

Subquery Scan on *SELECT* 4 (cost=1,312.61..1,312.66 rows=1 width=149) (actual time=653.147..653.362 rows=110 loops=1)

134. 0.122 653.299 ↓ 110.0 110 1

Group (cost=1,312.61..1,312.65 rows=1 width=149) (actual time=653.144..653.299 rows=110 loops=1)

  • Group Key: cfg_5.zone_id, cfg_5.collector_id, d_4.id, (COALESCE((((min(((COALESCE(dl.ip, d_4.ip)))::text)))::cidr)::inet, d_4.ip)), 'snmpDetails'::text, ((((((first_value(sa_1.alias) OVER (?)) || '|'::text) || array_to_string((array_agg(DISTINCT (COALESCE(dl.ip, d_4.ip)) ORDER BY (COALESCE(dl.ip, d_4.ip)))), ','::text)) || '|'::text) || (md5(array_to_string(array_agg(((config.key || '='::text) || config.value) ORDER BY config.key), '|'::text))))), false
135. 0.238 653.177 ↓ 128.0 128 1

Sort (cost=1,312.61..1,312.62 rows=1 width=109) (actual time=653.141..653.177 rows=128 loops=1)

  • Sort Key: cfg_5.zone_id, cfg_5.collector_id, d_4.id, (COALESCE((((min(((COALESCE(dl.ip, d_4.ip)))::text)))::cidr)::inet, d_4.ip)), ((((((first_value(sa_1.alias) OVER (?)) || '|'::text) || array_to_string((array_agg(DISTINCT (COALESCE(dl.ip, d_4.ip)) ORDER BY (COALESCE(dl.ip, d_4.ip)))), ','::text)) || '|'::text) || (md5(array_to_string(array_agg(((config.key || '='::text) || config.value) ORDER BY config.key), '|'::text)))))
  • Sort Method: quicksort Memory: 45kB
136. 0.725 652.939 ↓ 128.0 128 1

Hash Join (cost=1,312.16..1,312.60 rows=1 width=109) (actual time=652.281..652.939 rows=128 loops=1)

  • Hash Cond: ((cfg_5.zone_id = config.zone_id) AND (cfg_5.collector_id = config.collector_id))
  • Join Filter: ((dr_2.collector_id IS NULL) OR (dr_2.collector_id = cfg_5.collector_id))
  • Rows Removed by Join Filter: 128
137. 0.101 0.512 ↑ 7.5 2 1

HashAggregate (cost=5.75..5.90 rows=15 width=11) (actual time=0.504..0.512 rows=2 loops=1)

  • Group Key: cfg_5.collector_id, cfg_5.zone_id
  • Filter: (bool_or(((cfg_5.key ~ 'snmpDiscovery.collect.+'::text) AND (cfg_5.value = 'true'::text))) AND bool_or(((cfg_5.key = 'enabled'::text) AND (cfg_5.value = 'true'::text))) AND (NOT bool_or(((cfg_5.key = 'archived'::text) AND (cfg_5.value = 'true'::text)))))
  • Rows Removed by Filter: 17
138. 0.411 0.411 ↓ 1.0 30 1

Seq Scan on config cfg_5 (cost=0.00..4.96 rows=29 width=38) (actual time=0.063..0.411 rows=30 loops=1)

  • Filter: ((key = ANY ('{enabled,archived}'::text[])) OR (key ~ 'snmpDiscovery.collect.+'::text))
  • Rows Removed by Filter: 167
139. 0.462 651.702 ↓ 512.0 512 1

Hash (cost=1,306.40..1,306.40 rows=1 width=148) (actual time=651.702..651.702 rows=512 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 84kB
140. 0.324 651.240 ↓ 512.0 512 1

Merge Join (cost=1,305.57..1,306.40 rows=1 width=148) (actual time=650.622..651.240 rows=512 loops=1)

  • Merge Cond: (config.zone_id = ids.zone_id)
141. 0.184 0.504 ↑ 3.5 4 1

GroupAggregate (cost=5.04..5.66 rows=14 width=40) (actual time=0.350..0.504 rows=4 loops=1)

  • Group Key: config.zone_id, config.collector_id
142. 0.018 0.320 ↑ 1.0 25 1

Sort (cost=5.04..5.11 rows=25 width=38) (actual time=0.312..0.320 rows=25 loops=1)

  • Sort Key: config.zone_id, config.collector_id
  • Sort Method: quicksort Memory: 27kB
143. 0.302 0.302 ↑ 1.0 25 1

Seq Scan on config (cost=0.00..4.46 rows=25 width=38) (actual time=0.034..0.302 rows=25 loops=1)

  • Filter: ((value IS NOT NULL) AND (key ~ 'snmpDiscovery'::text))
  • Rows Removed by Filter: 172
144. 0.123 650.412 ↓ 509.0 509 1

Materialize (cost=1,300.53..1,300.55 rows=1 width=108) (actual time=650.264..650.412 rows=509 loops=1)

145. 0.515 650.289 ↓ 128.0 128 1

Sort (cost=1,300.53..1,300.53 rows=1 width=131) (actual time=650.260..650.289 rows=128 loops=1)

  • Sort Key: ids.zone_id, dv_2.trusted, dr_2.collector_id, d_4.id, d_4.ip, snac.aliasorder, sa_1.alias
  • Sort Method: quicksort Memory: 45kB
146. 0.365 649.774 ↓ 128.0 128 1

WindowAgg (cost=1,300.49..1,300.52 rows=1 width=131) (actual time=649.389..649.774 rows=128 loops=1)

147. 0.296 649.409 ↓ 128.0 128 1

Sort (cost=1,300.49..1,300.49 rows=1 width=99) (actual time=649.364..649.409 rows=128 loops=1)

  • Sort Key: d_4.ip, dr_2.collector_id, snac.aliasorder
  • Sort Method: quicksort Memory: 45kB
148. 2.646 649.113 ↓ 128.0 128 1

GroupAggregate (cost=1,300.44..1,300.48 rows=1 width=99) (actual time=646.590..649.113 rows=128 loops=1)

  • Group Key: ids.zone_id, dv_2.trusted, dr_2.collector_id, d_4.id, d_4.ip, snac.aliasorder, sa_1.alias
149. 1.431 646.467 ↓ 532.0 532 1

Sort (cost=1,300.44..1,300.44 rows=1 width=67) (actual time=646.329..646.467 rows=532 loops=1)

  • Sort Key: ids.zone_id, dv_2.trusted, dr_2.collector_id, d_4.id, d_4.ip, snac.aliasorder, sa_1.alias
  • Sort Method: quicksort Memory: 89kB
150. 0.724 645.036 ↓ 532.0 532 1

Nested Loop (cost=979.84..1,300.43 rows=1 width=67) (actual time=186.616..645.036 rows=532 loops=1)

  • Join Filter: (dr_2.collector_id = sa_1.collector_id)
  • Rows Removed by Join Filter: 30
151. 1.165 643.188 ↓ 112.4 562 1

Nested Loop Left Join (cost=979.70..1,299.58 rows=5 width=64) (actual time=186.608..643.188 rows=562 loops=1)

  • Join Filter: (snac.collector_id = dr_2.collector_id)
  • Rows Removed by Join Filter: 30
152. 7.893 640.899 ↓ 112.4 562 1

Nested Loop (cost=979.56..1,298.73 rows=5 width=56) (actual time=186.591..640.899 rows=562 loops=1)

153. 0.535 496.814 ↓ 14.0 266 1

Nested Loop (cost=978.88..1,283.26 rows=19 width=64) (actual time=186.352..496.814 rows=266 loops=1)

  • Join Filter: ((COALESCE(dl.id, d_4.id)) = dv_2.device_id)
154. 1.589 437.493 ↓ 2.4 266 1

Nested Loop (cost=978.45..1,212.78 rows=113 width=59) (actual time=186.312..437.493 rows=266 loops=1)

155. 3.692 170.164 ↓ 44.3 4,429 1

Group (cost=978.03..979.03 rows=100 width=62) (actual time=165.189..170.164 rows=4,429 loops=1)

  • Group Key: ids.zone_id, d_4.id, dl.id
156. 4.974 166.472 ↓ 44.3 4,429 1

Sort (cost=978.03..978.28 rows=100 width=26) (actual time=165.184..166.472 rows=4,429 loops=1)

  • Sort Key: ids.zone_id, d_4.id, dl.id
  • Sort Method: quicksort Memory: 412kB
157. 4.284 161.498 ↓ 44.3 4,429 1

Nested Loop Left Join (cost=451.67..974.71 rows=100 width=26) (actual time=26.906..161.498 rows=4,429 loops=1)

158. 3.247 48.222 ↓ 41.9 4,192 1

Nested Loop (cost=451.25..898.07 rows=100 width=15) (actual time=22.411..48.222 rows=4,192 loops=1)

159. 3.490 28.207 ↓ 41.9 4,192 1

Group (cost=450.82..451.57 rows=100 width=8) (actual time=22.404..28.207 rows=4,192 loops=1)

  • Group Key: ids.zone_id, (COALESCE(d_5.device_id, d_5.id))
160. 5.663 24.717 ↓ 86.7 8,668 1

Sort (cost=450.82..451.07 rows=100 width=8) (actual time=22.403..24.717 rows=8,668 loops=1)

  • Sort Key: ids.zone_id, (COALESCE(d_5.device_id, d_5.id))
  • Sort Method: quicksort Memory: 791kB
161. 0.500 19.054 ↓ 86.7 8,668 1

Nested Loop (cost=0.43..447.50 rows=100 width=8) (actual time=0.013..19.054 rows=8,668 loops=1)

162. 1.218 1.218 ↓ 86.7 8,668 1

CTE Scan on devs ids (cost=0.00..2.00 rows=100 width=8) (actual time=0.001..1.218 rows=8,668 loops=1)

163. 17.336 17.336 ↑ 1.0 1 8,668

Index Scan using pk_device on device d_5 (cost=0.43..4.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=8,668)

  • Index Cond: (id = ids.id)
164. 16.768 16.768 ↑ 1.0 1 4,192

Index Scan using pk_device on device d_4 (cost=0.43..4.45 rows=1 width=11) (actual time=0.004..0.004 rows=1 loops=4,192)

  • Index Cond: (id = (COALESCE(d_5.device_id, d_5.id)))
165. 108.992 108.992 ↓ 0.0 0 4,192

Index Scan using idx_zone_device_devid on device dl (cost=0.42..0.66 rows=11 width=15) (actual time=0.022..0.026 rows=0 loops=4,192)

  • Index Cond: (device_id = d_4.id)
166. 265.740 265.740 ↓ 0.0 0 4,429

Index Only Scan using pk_device_alias on device_snmpalias dsa (cost=0.42..2.32 rows=1 width=8) (actual time=0.059..0.060 rows=0 loops=4,429)

  • Index Cond: (device_id = (COALESCE(dl.id, d_4.id)))
  • Heap Fetches: 190
167. 58.786 58.786 ↑ 1.0 1 266

Index Scan using idx_device_values_devid on device_values dv_2 (cost=0.42..0.61 rows=1 width=5) (actual time=0.221..0.221 rows=1 loops=266)

  • Index Cond: (device_id = dsa.device_id)
  • Filter: (target OR trusted)
168. 136.192 136.192 ↓ 2.0 2 266

Index Scan using idx_device_response_devst on device_response dr_2 (cost=0.68..0.80 rows=1 width=8) (actual time=0.366..0.512 rows=2 loops=266)

  • Index Cond: ((scantype_id = system.scantype_ro('snmpDiscovery'::text)) AND (device_id = dv_2.device_id))
169. 1.124 1.124 ↑ 1.0 1 562

Index Scan using pk_snmpalias on snmpalias snac (cost=0.14..0.16 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=562)

  • Index Cond: (id = dsa.snmpalias_id)
170. 1.124 1.124 ↑ 1.0 1 562

Index Scan using pk_snmpalias on snmpalias sa_1 (cost=0.14..0.16 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=562)

  • Index Cond: (id = dsa.snmpalias_id)
171. 0.002 0.191 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=161,356.42..161,364.25 rows=165 width=149) (actual time=0.191..0.191 rows=0 loops=1)

172. 0.002 0.189 ↓ 0.0 0 1

GroupAggregate (cost=161,356.42..161,362.60 rows=165 width=149) (actual time=0.189..0.189 rows=0 loops=1)

  • Group Key: pd.zone_id, pd.collector_id, pd.cidrval, pd.priority, pd.overwrite
173. 0.032 0.187 ↓ 0.0 0 1

Sort (cost=161,356.42..161,356.83 rows=165 width=117) (actual time=0.187..0.187 rows=0 loops=1)

  • Sort Key: pd.zone_id, pd.collector_id, pd.cidrval, pd.priority, pd.overwrite
  • Sort Method: quicksort Memory: 25kB
174. 0.001 0.155 ↓ 0.0 0 1

Subquery Scan on pd (cost=161,347.04..161,350.34 rows=165 width=117) (actual time=0.155..0.155 rows=0 loops=1)

175. 0.003 0.154 ↓ 0.0 0 1

HashAggregate (cost=161,347.04..161,348.69 rows=165 width=117) (actual time=0.154..0.154 rows=0 loops=1)

  • Group Key: "*SELECT* 1_1".zone_id, "*SELECT* 1_1".priority, "*SELECT* 1_1".collector_id, "*SELECT* 1_1".device_id, (NULL::integer), (NULL::integer), "*SELECT* 1_1".cidrval, "*SELECT* 1_1".config, (true)
176. 0.002 0.151 ↓ 0.0 0 1

Append (cost=31.02..161,343.33 rows=165 width=117) (actual time=0.151..0.151 rows=0 loops=1)

177. 0.001 0.066 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=31.02..31.60 rows=11 width=117) (actual time=0.066..0.066 rows=0 loops=1)

178. 0.002 0.065 ↓ 0.0 0 1

GroupAggregate (cost=31.02..31.49 rows=11 width=149) (actual time=0.065..0.065 rows=0 loops=1)

  • Group Key: c_3.zone_id, d_6.priority, c_3.collector_id, (COALESCE(dl_1.device_id, d_6.id)), c_3.hash
  • Filter: (host(((min((COALESCE(dl_1.ip, d_6.ip))::text))::cidr)::inet) !~~ '%.255'::text)
179. 0.019 0.063 ↓ 0.0 0 1

Sort (cost=31.02..31.05 rows=11 width=115) (actual time=0.063..0.063 rows=0 loops=1)

  • Sort Key: c_3.zone_id, d_6.priority, c_3.collector_id, (COALESCE(dl_1.device_id, d_6.id)), c_3.hash
  • Sort Method: quicksort Memory: 25kB
180. 0.000 0.044 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.88..30.83 rows=11 width=115) (actual time=0.044..0.044 rows=0 loops=1)

181. 0.002 0.044 ↓ 0.0 0 1

Nested Loop (cost=0.46..6.85 rows=1 width=112) (actual time=0.044..0.044 rows=0 loops=1)

182. 0.030 0.042 ↓ 0.0 0 1

Hash Join (cost=0.04..2.42 rows=1 width=112) (actual time=0.042..0.042 rows=0 loops=1)

  • Hash Cond: (d_6.zone_id = c_3.zone_id)
183. 0.002 0.002 ↑ 100.0 1 1

CTE Scan on devs d_6 (cost=0.00..2.00 rows=100 width=76) (actual time=0.002..0.002 rows=1 loops=1)

184. 0.001 0.010 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=40) (actual time=0.010..0.010 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
185. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on protocolcfg c_3 (cost=0.00..0.02 rows=1 width=40) (actual time=0.009..0.009 rows=0 loops=1)

  • Filter: (tracehosts AND pathenabled AND (collector_id IS NOT NULL) AND (scantype = 'pathDiscovery'::text))
  • Rows Removed by Filter: 14
186. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_device_values_devid on device_values dv_3 (cost=0.42..4.42 rows=1 width=4) (never executed)

  • Index Cond: (device_id = d_6.id)
  • Filter: trusted
187. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_zone_device_devid on device dl_1 (cost=0.42..23.87 rows=11 width=11) (never executed)

  • Index Cond: (device_id = d_6.parent_id)
188. 0.002 0.051 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=5,558.75..5,566.78 rows=153 width=117) (actual time=0.051..0.051 rows=0 loops=1)

189. 0.001 0.049 ↓ 0.0 0 1

GroupAggregate (cost=5,558.75..5,565.25 rows=153 width=149) (actual time=0.049..0.049 rows=0 loops=1)

  • Group Key: c_4.zone_id, d_7.priority, c_4.collector_id, (COALESCE(dl_2.device_id, d_7.id)), c_4.hash
  • Filter: (host(((min((COALESCE(dl_2.ip, d_7.ip))::text))::cidr)::inet) !~~ '%.255'::text)
190. 0.023 0.048 ↓ 0.0 0 1

Sort (cost=5,558.75..5,559.13 rows=153 width=115) (actual time=0.048..0.048 rows=0 loops=1)

  • Sort Key: c_4.zone_id, d_7.priority, c_4.collector_id, (COALESCE(dl_2.device_id, d_7.id)), c_4.hash
  • Sort Method: quicksort Memory: 25kB
191. 0.001 0.025 ↓ 0.0 0 1

Nested Loop (cost=89.83..5,553.20 rows=153 width=115) (actual time=0.025..0.025 rows=0 loops=1)

  • Join Filter: (c_4.collector_id = cc_1.collector_id)
192. 0.001 0.024 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.88..30.83 rows=11 width=119) (actual time=0.024..0.024 rows=0 loops=1)

193. 0.001 0.023 ↓ 0.0 0 1

Nested Loop (cost=0.46..6.85 rows=1 width=112) (actual time=0.023..0.023 rows=0 loops=1)

194. 0.018 0.022 ↓ 0.0 0 1

Hash Join (cost=0.04..2.42 rows=1 width=112) (actual time=0.022..0.022 rows=0 loops=1)

  • Hash Cond: (d_7.zone_id = c_4.zone_id)
195. 0.000 0.000 ↑ 100.0 1 1

CTE Scan on devs d_7 (cost=0.00..2.00 rows=100 width=76) (actual time=0.000..0.000 rows=1 loops=1)

196. 0.000 0.004 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=40) (actual time=0.004..0.004 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
197. 0.004 0.004 ↓ 0.0 0 1

CTE Scan on protocolcfg c_4 (cost=0.00..0.02 rows=1 width=40) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (tracehosts AND pathenabled AND (collector_id IS NOT NULL) AND (scantype = 'pathDiscovery'::text))
  • Rows Removed by Filter: 14
198. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_device_values_devid on device_values dv_4 (cost=0.42..4.42 rows=1 width=4) (never executed)

  • Index Cond: (device_id = d_7.id)
  • Filter: target
199. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_zone_device_devid on device dl_2 (cost=0.42..23.87 rows=11 width=11) (never executed)

  • Index Cond: (device_id = d_7.parent_id)
200. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on attribute_cidr cc_1 (cost=88.95..498.51 rows=282 width=11) (never executed)

  • Recheck Cond: ((iprange(cidrval) >>= iprange((d_7.ip)::cidr)) OR (iprange(cidrval) >>= iprange((dl_2.ip)::cidr)))
  • Filter: (attributetype_id = system.attributetype('target'::text))
201. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=88.95..88.95 rows=1,127 width=0) (never executed)

202. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_zone_attribute_cidr_ipr (cost=0.00..13.98 rows=564 width=0) (never executed)

  • Index Cond: (iprange(cidrval) >>= iprange((d_7.ip)::cidr))
203. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_zone_attribute_cidr_ipr (cost=0.00..4.53 rows=564 width=0) (never executed)

  • Index Cond: (iprange(cidrval) >>= iprange((dl_2.ip)::cidr))
204. 0.002 0.032 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=155,744.14..155,744.95 rows=1 width=117) (actual time=0.032..0.032 rows=0 loops=1)

205. 0.001 0.030 ↓ 0.0 0 1

Group (cost=155,744.14..155,744.94 rows=1 width=149) (actual time=0.030..0.030 rows=0 loops=1)

  • Group Key: c_5.zone_id, c_5.collector_id, rt.id, rt.route, c_5.hash
206. 0.024 0.029 ↓ 0.0 0 1

Sort (cost=155,744.14..155,744.27 rows=53 width=76) (actual time=0.029..0.029 rows=0 loops=1)

  • Sort Key: c_5.zone_id, c_5.collector_id, rt.id, rt.route, c_5.hash
  • Sort Method: quicksort Memory: 25kB
207. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=153,748.14..155,742.62 rows=53 width=76) (actual time=0.005..0.005 rows=0 loops=1)

  • Join Filter: ((iprange(cc_2.cidrval)) >>= iprange(rt.route))
208. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.08 rows=1 width=76) (actual time=0.004..0.004 rows=0 loops=1)

  • Join Filter: (rt.zone_id = c_5.zone_id)
209. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on rts rt (cost=0.00..0.05 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((NOT ('169.254.0.0/16'::inet >>= (route)::inet)) AND (NOT ('127.0.0.0/8'::inet >>= (route)::inet)) AND (NOT ('224.0.0.0/3'::inet >>= (route)::inet)) AND (NOT ('f000::/4'::inet >>= (route)::inet)) AND (5 <= masklen((route)::inet)) AND (host((route)::inet) <> '0.0.0.0'::text) AND (host((route)::inet) !~~ '%.255'::text))
210. 0.000 0.000 ↓ 0.0 0

CTE Scan on protocolcfg c_5 (cost=0.00..0.02 rows=1 width=40) (never executed)

  • Filter: (traceroutes AND pathenabled AND (collector_id IS NOT NULL) AND (scantype = 'pathDiscovery'::text))
211. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=153,748.14..154,412.94 rows=53,184 width=47) (never executed)

  • Group Key: cc_2.zone_id, cc_2.cidrval, cc_2.collector_id
212. 0.000 0.000 ↓ 0.0 0

Seq Scan on attribute_cidr cc_2 (cost=0.00..152,691.44 rows=140,894 width=15) (never executed)

  • Filter: (attributetype_id = system.attributetype('trusted'::text))
213. 4.011 7,360.726 ↓ 13,002.0 26,004 1

Subquery Scan on *SELECT* 6 (cost=156,011.89..156,011.98 rows=2 width=149) (actual time=7,314.193..7,360.726 rows=26,004 loops=1)

214. 39.484 7,356.715 ↓ 13,002.0 26,004 1

GroupAggregate (cost=156,011.89..156,011.96 rows=2 width=149) (actual time=7,314.191..7,356.715 rows=26,004 loops=1)

  • Group Key: hd.zone_id, hd.collector_id, hd.cidrval, hd.priority
215. 43.832 7,317.231 ↓ 13,002.0 26,004 1

Sort (cost=156,011.89..156,011.89 rows=2 width=116) (actual time=7,314.173..7,317.231 rows=26,004 loops=1)

  • Sort Key: hd.zone_id, hd.collector_id, hd.cidrval, hd.priority
  • Sort Method: quicksort Memory: 4425kB
216. 3.721 7,273.399 ↓ 13,002.0 26,004 1

Subquery Scan on hd (cost=156,011.81..156,011.88 rows=2 width=116) (actual time=7,260.709..7,273.399 rows=26,004 loops=1)

217. 6.777 7,269.678 ↓ 13,002.0 26,004 1

Unique (cost=156,011.81..156,011.86 rows=2 width=116) (actual time=7,260.707..7,269.678 rows=26,004 loops=1)

218. 9.410 7,262.901 ↓ 13,002.0 26,004 1

Sort (cost=156,011.81..156,011.82 rows=2 width=116) (actual time=7,260.705..7,262.901 rows=26,004 loops=1)

  • Sort Key: "*SELECT* 1_2".zone_id, "*SELECT* 1_2".priority, "*SELECT* 1_2".collector_id, "*SELECT* 1_2".device_id, (NULL::integer), (NULL::integer), "*SELECT* 1_2".cidrval, "*SELECT* 1_2".config
  • Sort Method: quicksort Memory: 4425kB
219. 2.180 7,253.491 ↓ 13,002.0 26,004 1

Append (cost=266.82..156,011.80 rows=2 width=116) (actual time=7,225.861..7,253.491 rows=26,004 loops=1)

220. 3.220 7,251.295 ↓ 26,004.0 26,004 1

Subquery Scan on *SELECT* 1_2 (cost=266.82..266.85 rows=1 width=116) (actual time=7,225.861..7,251.295 rows=26,004 loops=1)

221. 16.914 7,248.075 ↓ 26,004.0 26,004 1

Group (cost=266.82..266.84 rows=1 width=180) (actual time=7,225.859..7,248.075 rows=26,004 loops=1)

  • Group Key: c_6.zone_id, d_8.priority, c_6.collector_id, d_8.id, d_8.ip, c_6.hash
222. 85.847 7,231.161 ↓ 52,008.0 52,008 1

Sort (cost=266.82..266.82 rows=1 width=108) (actual time=7,225.848..7,231.161 rows=52,008 loops=1)

  • Sort Key: c_6.zone_id, d_8.priority, c_6.collector_id, d_8.id, d_8.ip, c_6.hash
  • Sort Method: quicksort Memory: 8850kB
223. 16.475 7,145.314 ↓ 52,008.0 52,008 1

Nested Loop Left Join (cost=0.75..266.81 rows=1 width=108) (actual time=1.260..7,145.314 rows=52,008 loops=1)

  • Join Filter: ((c_6.collector_id = cc_3.collector_id) AND (cc_3.zone_id = c_6.zone_id))
  • Rows Removed by Join Filter: 52008
  • Filter: (cc_3.id IS NULL)
  • Rows Removed by Filter: 8668
224. 5.103 151.099 ↓ 60,676.0 60,676 1

Nested Loop (cost=0.46..7.60 rows=1 width=108) (actual time=0.099..151.099 rows=60,676 loops=1)

225. 13.985 24.644 ↓ 60,676.0 60,676 1

Hash Join (cost=0.04..3.17 rows=1 width=108) (actual time=0.075..24.644 rows=60,676 loops=1)

  • Hash Cond: (d_8.zone_id = c_6.zone_id)
226. 10.629 10.629 ↓ 86.7 8,668 1

CTE Scan on devs d_8 (cost=0.00..2.75 rows=100 width=72) (actual time=0.021..10.629 rows=8,668 loops=1)

  • Filter: (host(((ip)::cidr)::inet) !~~ '%.255'::text)
227. 0.012 0.030 ↓ 7.0 7 1

Hash (cost=0.02..0.02 rows=1 width=40) (actual time=0.030..0.030 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
228. 0.018 0.018 ↓ 7.0 7 1

CTE Scan on protocolcfg c_6 (cost=0.00..0.02 rows=1 width=40) (actual time=0.009..0.018 rows=7 loops=1)

  • Filter: (hostenabled AND (collector_id IS NOT NULL) AND (scantype = 'hostDiscovery'::text))
  • Rows Removed by Filter: 7
229. 121.352 121.352 ↑ 1.0 1 60,676

Index Scan using idx_device_values_devid on device_values dv_5 (cost=0.42..4.42 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=60,676)

  • Index Cond: (device_id = d_8.id)
  • Filter: trusted
230. 6,977.740 6,977.740 ↑ 141.0 1 60,676

Index Scan using idx_zone_attribute_cidr_ipr on attribute_cidr cc_3 (cost=0.29..257.09 rows=141 width=19) (actual time=0.072..0.115 rows=1 loops=60,676)

  • Index Cond: (iprange(cidrval) >>= iprange((d_8.ip)::cidr))
  • Filter: (attributetype_id = system.attributetype('target'::text))
  • Rows Removed by Filter: 16
231. 0.001 0.016 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=155,744.14..155,744.95 rows=1 width=116) (actual time=0.016..0.016 rows=0 loops=1)

232. 0.000 0.015 ↓ 0.0 0 1

Group (cost=155,744.14..155,744.94 rows=1 width=148) (actual time=0.015..0.015 rows=0 loops=1)

  • Group Key: c_7.zone_id, c_7.collector_id, r.id, r.route, c_7.hash
233. 0.012 0.015 ↓ 0.0 0 1

Sort (cost=155,744.14..155,744.28 rows=53 width=76) (actual time=0.015..0.015 rows=0 loops=1)

  • Sort Key: c_7.zone_id, c_7.collector_id, r.id, r.route, c_7.hash
  • Sort Method: quicksort Memory: 25kB
234. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=153,748.14..155,742.62 rows=53 width=76) (actual time=0.003..0.003 rows=0 loops=1)

  • Join Filter: ((iprange(cc_4.cidrval)) >>= iprange(r.route))
235. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.08 rows=1 width=76) (actual time=0.002..0.002 rows=0 loops=1)

  • Join Filter: (r.zone_id = c_7.zone_id)
236. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on rts r (cost=0.00..0.05 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((host((route)::inet) !~~ '%.255'::text) AND (((family((route)::inet) = 4) AND (masklen((route)::inet) >= 16)) OR ((family((route)::inet) = 6) AND (masklen((route)::inet) >= 112) AND (NOT ('f000::/4'::inet >>= (route)::inet)))))
237. 0.000 0.000 ↓ 0.0 0

CTE Scan on protocolcfg c_7 (cost=0.00..0.02 rows=1 width=40) (never executed)

  • Filter: (targetroutes AND hostenabled AND (collector_id IS NOT NULL) AND (scantype = 'hostDiscovery'::text))
238. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=153,748.14..154,412.94 rows=53,184 width=47) (never executed)

  • Group Key: cc_4.zone_id, cc_4.cidrval, cc_4.collector_id
239. 0.000 0.000 ↓ 0.0 0

Seq Scan on attribute_cidr cc_4 (cost=0.00..152,691.44 rows=140,894 width=15) (never executed)

  • Filter: (attributetype_id = system.attributetype('trusted'::text))
240. 4.102 25,075.259 ↓ 6,841.0 13,682 1

Unique (cost=25,775.52..25,775.57 rows=2 width=121) (actual time=25,070.112..25,075.259 rows=13,682 loops=1)

241. 5.133 25,071.157 ↓ 6,841.0 13,682 1

Sort (cost=25,775.52..25,775.52 rows=2 width=121) (actual time=25,070.110..25,071.157 rows=13,682 loops=1)

  • Sort Key: targets.zone_id, targets.priority, targets.collector_id, targets.device_id, targets.route_id, targets.cidr_id, targets.cidrval, st_1.id, targets.config, targets.overwrite
  • Sort Method: quicksort Memory: 2153kB
242. 1.533 25,066.024 ↓ 6,841.0 13,682 1

Append (cost=23,382.74..25,775.51 rows=2 width=121) (actual time=25,049.853..25,066.024 rows=13,682 loops=1)

243. 4.672 25,055.893 ↓ 13,682.0 13,682 1

Group (cost=23,382.74..23,382.77 rows=1 width=121) (actual time=25,049.852..25,055.893 rows=13,682 loops=1)

  • Group Key: targets.zone_id, targets.priority, targets.collector_id, targets.device_id, targets.route_id, targets.cidr_id, targets.cidrval, st_1.id, targets.config, targets.overwrite
244.          

Initplan (forGroup)

245. 0.009 0.009 ↑ 1.0 1 1

CTE Scan on attype attype_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=1)

246. 19.008 25,051.212 ↓ 13,682.0 13,682 1

Sort (cost=23,382.72..23,382.72 rows=1 width=121) (actual time=25,049.850..25,051.212 rows=13,682 loops=1)

  • Sort Key: targets.zone_id, targets.priority, targets.collector_id, targets.device_id, targets.route_id, targets.cidr_id, targets.cidrval, st_1.id, targets.config, targets.overwrite
  • Sort Method: quicksort Memory: 2153kB
247. 36.821 25,032.204 ↓ 13,682.0 13,682 1

Nested Loop Left Join (cost=1.70..23,382.71 rows=1 width=121) (actual time=21,789.823..25,032.204 rows=13,682 loops=1)

  • Filter: (cc_5.id IS NULL)
  • Rows Removed by Filter: 83191
248. 31.726 21,895.447 ↓ 66.3 96,873 1

Hash Join (cost=1.41..401.52 rows=1,461 width=121) (actual time=21,789.182..21,895.447 rows=96,873 loops=1)

  • Hash Cond: (targets.scantype = st_1.scantype)
249. 21,863.705 21,863.705 ↓ 6.0 96,873 1

CTE Scan on targets (cost=0.00..324.64 rows=16,232 width=149) (actual time=21,789.154..21,863.705 rows=96,873 loops=1)

250. 0.005 0.016 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=15) (actual time=0.016..0.016 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
251. 0.011 0.011 ↑ 1.0 18 1

Seq Scan on scantype st_1 (cost=0.00..1.18 rows=18 width=15) (actual time=0.010..0.011 rows=18 loops=1)

252. 3,099.936 3,099.936 ↑ 22.0 1 96,873

Index Scan using idx_zone_attribute_cidr_ipr on attribute_cidr cc_5 (cost=0.29..15.51 rows=22 width=19) (actual time=0.025..0.032 rows=1 loops=96,873)

  • Index Cond: (iprange(cidrval) >>= iprange((targets.cidrval)::cidr))
  • Filter: ((attributetype_id = $34) AND ((collector_id = targets.collector_id) OR (collector_id IS NULL)) AND (zone_id = targets.zone_id))
  • Rows Removed by Filter: 17
253. 0.001 8.598 ↓ 0.0 0 1

Group (cost=2,392.69..2,392.72 rows=1 width=121) (actual time=8.598..8.598 rows=0 loops=1)

  • Group Key: targets_1.zone_id, targets_1.priority, targets_1.collector_id, targets_1.device_id, targets_1.route_id, targets_1.cidr_id, targets_1.cidrval, st_2.id, targets_1.config, targets_1.overwrite
254.          

Initplan (forGroup)

255. 0.000 0.000 ↓ 0.0 0

CTE Scan on attype (cost=0.00..0.02 rows=1 width=4) (never executed)

256. 0.016 8.597 ↓ 0.0 0 1

Sort (cost=2,392.67..2,392.68 rows=1 width=121) (actual time=8.597..8.597 rows=0 loops=1)

  • Sort Key: targets_1.zone_id, targets_1.priority, targets_1.collector_id, targets_1.device_id, targets_1.route_id, targets_1.cidr_id, targets_1.cidrval, st_2.id, targets_1.config, targets_1.overwrite
  • Sort Method: quicksort Memory: 25kB
257. 0.002 8.581 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.70..2,392.66 rows=1 width=121) (actual time=8.581..8.581 rows=0 loops=1)

  • Filter: (cc_6.id IS NULL)
258. 0.001 8.579 ↓ 0.0 0 1

Hash Join (cost=1.41..363.78 rows=730 width=121) (actual time=8.579..8.579 rows=0 loops=1)

  • Hash Cond: (targets_1.scantype = st_2.scantype)
259. 8.578 8.578 ↓ 0.0 0 1

CTE Scan on targets targets_1 (cost=0.00..324.64 rows=8,116 width=149) (actual time=8.578..8.578 rows=0 loops=1)

  • Filter: overwrite
  • Rows Removed by Filter: 96873
260. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.18..1.18 rows=18 width=15) (never executed)

261. 0.000 0.000 ↓ 0.0 0

Seq Scan on scantype st_2 (cost=0.00..1.18 rows=18 width=15) (never executed)

262. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_zone_attribute_cidr_ipr on attribute_cidr cc_6 (cost=0.29..2.77 rows=1 width=19) (never executed)

  • Index Cond: (iprange(cidrval) = iprange((targets_1.cidrval)::cidr))
  • Filter: ((attributetype_id = $33) AND ((collector_id = targets_1.collector_id) OR (collector_id IS NULL)) AND (zone_id = targets_1.zone_id))
263.          

CTE updates

264. 0.002 212.174 ↓ 0.0 0 1

Update on target zt_1 (cost=0.43..8.97 rows=1 width=217) (actual time=212.174..212.174 rows=0 loops=1)

265. 1.460 212.172 ↓ 0.0 0 1

Nested Loop (cost=0.43..8.97 rows=1 width=217) (actual time=212.172..212.172 rows=0 loops=1)

266. 5.482 5.482 ↓ 6,841.0 13,682 1

CTE Scan on targets st_3 (cost=0.00..0.04 rows=2 width=168) (actual time=0.065..5.482 rows=13,682 loops=1)

267. 205.230 205.230 ↓ 0.0 0 13,682

Index Scan using uk_target on target zt_1 (cost=0.43..4.46 rows=1 width=114) (actual time=0.015..0.015 rows=0 loops=13,682)

  • Index Cond: (((cidrval)::inet = st_3.cidrval) AND (collector_id = st_3.collector_id) AND (scantype_id = st_3.scantype_id))
  • Filter: (st_3.config <> details)
  • Rows Removed by Filter: 1
268.          

CTE inserts

269. 0.003 39,018.271 ↓ 0.0 0 1

Insert on target (cost=9.65..9.75 rows=3 width=140) (actual time=39,018.271..39,018.271 rows=0 loops=1)

  • Conflict Resolution: NOTHING
  • Conflict Arbiter Indexes: uk_target
  • Tuples Inserted: 0
  • Conflicting Tuples: 0
270. 0.001 39,018.268 ↓ 0.0 0 1

Subquery Scan on *SELECT* (cost=9.65..9.75 rows=3 width=140) (actual time=39,018.268..39,018.268 rows=0 loops=1)

271. 0.004 39,018.267 ↓ 0.0 0 1

HashAggregate (cost=9.65..9.68 rows=3 width=88) (actual time=39,018.267..39,018.267 rows=0 loops=1)

  • Group Key: t_1.collector_id, t_1.zone_id, t_1.cidrval, t_1.device_id, t_1.cidr_id, t_1.route_id, t_1.scantype_id, t_1.config
272. 0.002 39,018.263 ↓ 0.0 0 1

Append (cost=0.42..9.59 rows=3 width=88) (actual time=39,018.263..39,018.263 rows=0 loops=1)

273. 76.672 39,014.386 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.42..5.06 rows=2 width=88) (actual time=39,014.386..39,014.386 rows=0 loops=1)

274. 33.419 53.470 ↓ 6,841.0 13,682 1

Nested Loop Left Join (cost=0.00..0.06 rows=2 width=88) (actual time=0.002..53.470 rows=13,682 loops=1)

  • Join Filter: false
275. 20.051 20.051 ↓ 6,841.0 13,682 1

CTE Scan on targets t_1 (cost=0.00..0.04 rows=2 width=88) (actual time=0.000..20.051 rows=13,682 loops=1)

276. 0.000 0.000 ↓ 0.0 0 13,682

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=13,682)

  • One-Time Filter: false
277. 38,884.244 38,884.244 ↑ 36.0 1 13,682

Index Scan using idx_target_colscancidr_gist on target zt_2 (cost=0.42..75.23 rows=36 width=15) (actual time=2.842..2.842 rows=1 loops=13,682)

  • Index Cond: ((collector_id = t_1.collector_id) AND (scantype_id = t_1.scantype_id) AND (iprange(cidrval) >>= iprange((t_1.cidrval)::cidr)))
278. 0.001 3.875 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.42..4.50 rows=1 width=88) (actual time=3.875..3.875 rows=0 loops=1)

279. 0.003 3.874 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.05 rows=1 width=88) (actual time=3.874..3.874 rows=0 loops=1)

  • Join Filter: false
280. 3.871 3.871 ↓ 0.0 0 1

CTE Scan on targets t_2 (cost=0.00..0.04 rows=1 width=88) (actual time=3.871..3.871 rows=0 loops=1)

  • Filter: overwrite
  • Rows Removed by Filter: 13682
281. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=0) (never executed)

  • One-Time Filter: false
282. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_target_colscancidr_gist on target zt_3 (cost=0.42..4.44 rows=1 width=15) (never executed)

  • Index Cond: ((collector_id = t_2.collector_id) AND (scantype_id = t_2.scantype_id) AND (iprange(cidrval) = iprange((t_2.cidrval)::cidr)))
283. 0.001 25,108.138 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.14..9.63 rows=2 width=144) (actual time=25,108.138..25,108.138 rows=0 loops=1)

  • Join Filter: ((tparent.collector_id = t.collector_id) AND (tparent.scantype_id = t.scantype_id) AND ((tparent.cidrval)::inet = t.cidrval))
284. 0.000 25,108.137 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.14..9.41 rows=2 width=88) (actual time=25,108.137..25,108.137 rows=0 loops=1)

285. 25,108.137 25,108.137 ↓ 0.0 0 1

CTE Scan on targets t (cost=0.00..0.04 rows=2 width=88) (actual time=25,108.137..25,108.137 rows=0 loops=1)

  • Filter: (priority IS NOT NULL)
  • Rows Removed by Filter: 13682
286. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_target_hp_colscancidr_gist on target_highpriority zt (cost=0.14..3.17 rows=1 width=40) (never executed)

  • Index Cond: ((collector_id = t.collector_id) AND (scantype_id = t.scantype_id))
  • Filter: ((cidrval)::inet = t.cidrval)
287. 0.000 0.000 ↓ 0.0 0

CTE Scan on inserts tparent (cost=0.00..0.06 rows=3 width=44) (never executed)