explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J17U

Settings
# exclusive inclusive rows x rows loops node
1. 3,008.863 3,026.199 ↓ 0.0 0 1

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

  • loops=1)
  • Planning time: 19.350 ms
  • Execution time: 4715.110 ms
2.          

CTE attype

3. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on attributetype (cost=0.00..1.27 rows=1 width=4) (actual time=0.004..0.005 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.013 0.013 ↑ 1.0 1 1

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

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

CTE targets

9. 3,024.570 3,024.570 ↓ 6,841.0 13,682 1

Sort (cost=368,889.31..368,889.31 rows=2 width=121) (actual time=3,023.313..3,024.570 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.016 2.324 ↓ 42.0 42 1

Hash Join (cost=414.69..417.54 rows=1 width=83) (actual time=2.213..2.324 rows=42 loops=1)

  • Hash Cond: ((c.zone_id = cfg.zone_id) AND (c.collector_id = cfg.collector_id))
12. 0.113 2.172 ↓ 1.4 42 1

GroupAggregate (cost=403.21..405.53 rows=30 width=72) (actual time=2.070..2.172 rows=42 loops=1)

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

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

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

Nested Loop (cost=0.00..399.97 rows=98 width=70) (actual time=0.272..2.015 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.022 0.022 ↑ 1.0 197 1

Seq Scan on config c (cost=0.00..3.97 rows=197 width=38) (actual time=0.002..0.022 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.005 0.136 ↑ 1.0 17 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.090 0.131 ↑ 1.0 17 1

HashAggregate (cost=10.88..11.05 rows=17 width=19) (actual time=0.126..0.131 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.041 0.041 ↑ 1.2 34 1

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

  • Filter: (key = ANY ('{pathDiscovery.traceDiscoveredRoutes,pathDiscovery.traceToHosts,hostDiscovery.targetDiscoveredRoutes,leakDiscovery.doInbound,leakDiscovery.doOut
  • Rows Removed by Filter: 163
21.          

CTE devs

22. 0.485 54.624 ↓ 86.7 8,668 1

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

23. 10.789 10.789 ↓ 86.7 8,670 1

Function Scan on json_to_recordset (cost=0.00..1.00 rows=100 width=36) (actual time=9.388..10.789 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.000 0.000 ↓ 0.0 0 1

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

  • One-Time Filter: false
27.          

CTE targets

28. 141.075 695.802 ↓ 4.4 70,869 1

HashAggregate (cost=342,087.35..342,249.67 rows=16,232 width=149) (actual time=670.023..695.802 rows=70,869 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. 20.006 554.727 ↓ 14.2 230,053 1

Append (cost=2,216.40..341,681.55 rows=16,232 width=149) (actual time=299.934..554.727 rows=230,053 loops=1)

30. 37.846 341.723 ↓ 228,528.0 228,528 1

Hash Join (cost=2,216.40..2,229.66 rows=1 width=149) (actual time=299.933..341.723 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) = "*SELEC
31. 26.486 300.454 ↓ 27.7 14,283 1

HashAggregate (cost=1,789.37..1,794.52 rows=515 width=81) (actual time=296.496..300.454 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. 10.007 273.968 ↓ 82.3 42,360 1

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

33. 5.799 133.941 ↓ 109.7 8,668 1

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

34. 0.851 76.134 ↓ 94.2 8,668 1

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

35. 57.947 57.947 ↓ 86.7 8,668 1

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

36. 17.336 17.336 ↑ 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.002..0.002 rows=1 loops=8,668)

  • Index Cond: (id = d_1.id)
  • Filter: ((NOT meta) AND (ip IS NOT NULL))
37. 52.008 52.008 ↑ 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.006..0.006 rows=1 loops=8,668)

  • Index Cond: (device_id = zd.id)
  • Filter: (target OR trusted)
38. 130.020 130.020 ↑ 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.005..0.015 rows=5 loops=8,668)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
40. 0.003 3.416 ↑ 2.0 16 1

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

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

Sort (cost=425.90..425.98 rows=32 width=72) (actual time=3.412..3.413 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.014 3.396 ↑ 2.0 16 1

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

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

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

44. 0.000 0.361 ↑ 7.0 2 1

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

45. 0.027 0.361 ↑ 7.0 2 1

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

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

Sort (cost=43.47..43.51 rows=14 width=40) (actual time=0.334..0.334 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.005 0.317 ↑ 7.0 2 1

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

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

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

  • Group Key: sa.zone_id, sa.collector_id
49. 0.029 0.150 ↑ 4.3 60 1

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

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

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

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

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

  • -> HashAggregate (cost=2.05..2.61 rows=56 width=15) (actual time=0.029..0.031 rows=60 loops
  • Group Key: sa.zone_id, sa.collector_id, sa.alias
  • -> Seq Scan on snmpalias sa (cost=0.00..1.60 rows=60 width=15) (actual time=0.002..0.
  • -> Nested Loop (cost=0.00..6.98 rows=200 width=40) (actual time=0.026..0.040 rows=52 loops=
  • -> Seq Scan on snmpaliasgroup sg (cost=0.00..1.01 rows=1 width=604) (actual time=0.01
  • Filter: (name = 'common'::text)
  • -> Seq Scan on config c_1 (cost=0.00..4.96 rows=2 width=8) (actual time=0.005..0.014
  • Filter: ((key = 'snmpDiscovery.useCommonCredentials'::text) AND (value = 'true'::
  • Rows Removed by Filter: 196
52. 0.004 0.081 ↑ 7.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
53. 0.001 0.077 ↑ 7.0 2 1

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

54. 0.076 0.076 ↑ 7.0 2 1

HashAggregate (cost=5.37..5.51 rows=14 width=11) (actual time=0.074..0.076 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))) AN
  • Rows Removed by Filter: 17
  • -> Seq Scan on config cfg_1 (cost=0.00..4.71 rows=24 width=38) (actual time=0.011..0.045 rows=24
  • Filter: (key = ANY ('{snmpDiscovery.enabled,enabled,archived}'::text[]))
  • Rows Removed by Filter: 173
55. 0.017 0.654 ↑ 2.1 7 1

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

  • Join Filter: (cvp.collector_id = c_2.id)
  • Rows Removed by Join Filter: 42
56. 0.005 0.609 ↑ 2.1 7 1

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

  • Join Filter: (cip.collector_id = c_2.id)
  • Rows Removed by Join Filter: 42
57. 0.003 0.583 ↑ 2.1 7 1

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

  • Join Filter: (cst.collector_id = c_2.id)
58. 0.005 0.559 ↑ 2.1 7 1

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

  • Hash Cond: (cpe.collector_id = c_2.id)
59. 0.072 0.457 ↑ 2.7 7 1

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

  • Group Key: cpe.zone_id, cpe.collector_id
60. 0.040 0.385 ↑ 31.7 126 1

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

  • Sort Key: cpe.zone_id, cpe.collector_id
  • Sort Method: quicksort Memory: 30kB
61. 0.202 0.345 ↑ 31.7 126 1

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

  • Hash Cond: ((cpe.key = (regexp_split_to_array(unnest('{profileDiscovery.collectHTTP:profileDiscover
  • -> Seq Scan on config cpe (cost=0.00..4.46 rows=104 width=31) (actual time=0.003..0.030 rows=104
  • Filter: (value = 'true'::text)
  • Rows Removed by Filter: 93
62. 0.022 0.143 ↑ 3.1 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
63. 0.065 0.121 ↑ 3.1 21 1

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

  • Hash Cond: (cpp.key = (regexp_split_to_array(unnest('{profileDiscovery.collectHTTP:prof
  • -> Seq Scan on config cpp (cost=0.00..4.96 rows=66 width=34) (actual time=0.003..0.03
  • Filter: (length(value) > 0)
  • Rows Removed by Filter: 14
64. 0.056 0.056 ↑ 33.3 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.043..0.053 rows=3
65. 0.002 0.097 ↑ 2.1 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
66. 0.019 0.095 ↑ 2.1 7 1

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

  • Hash Cond: (c_2.id = phases_1.collector_id)
  • -> Seq Scan on collector c_2 (cost=0.00..1.39 rows=39 width=8) (actual time=0.003..0.005 rows=39 loops=
67. 0.076 0.076 ↑ 2.1 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Subquery Scan on phases_1 (cost=5.45..5.75 rows=15 width=4) (actual time=0.068..0.071 rows=7 l
  • -> HashAggregate (cost=5.45..5.60 rows=15 width=11) (actual time=0.068..0.070 rows=7 loops=
  • Group Key: cfg_2.collector_id, cfg_2.zone_id
  • Filter: (bool_or(((cfg_2.key = 'portDiscovery.enabled'::text) AND (cfg_2.value = 'true'
  • Rows Removed by Filter: 12
  • -> Seq Scan on config cfg_2 (cost=0.00..4.71 rows=27 width=38) (actual time=0.004..0.
  • Filter: (key = ANY ('{portDiscovery.enabled,enabled,archived}'::text[]))
  • Rows Removed by Filter: 170
68. 0.007 0.021 ↓ 0.0 0 7

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

69. 0.014 0.014 ↓ 0.0 0 1

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

  • Filter: ((key = 'profileDiscovery.scanStandardTCPPorts'::text) AND (value = 'true'::text))
  • Rows Removed by Filter: 197
70. 0.005 0.021 ↓ 1.8 7 7

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

71. 0.016 0.016 ↓ 1.8 7 1

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

  • Filter: ((key = 'portDiscovery.useInfectionPorts'::text) AND (value = 'true'::text))
  • Rows Removed by Filter: 190
72. 0.011 0.028 ↓ 1.8 7 7

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

73. 0.017 0.017 ↓ 1.8 7 1

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

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

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

  • Filter: dnsenabled
  • Rows Removed by Filter: 35
75. 0.004 0.004 ↓ 0.0 0 1

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

  • Filter: (inboundleak AND leakenabled AND (scantype = 'leakDiscovery'::text))
  • Rows Removed by Filter: 42
76. 0.004 0.004 ↓ 0.0 0 1

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

  • Filter: (outboundleak AND leakenabled AND (scantype = 'leakDiscovery'::text))
  • Rows Removed by Filter: 42
77. 0.655 81.518 ↓ 1,197.0 1,197 1

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

78. 0.817 80.863 ↓ 1,197.0 1,197 1

Group (cost=460.77..460.79 rows=1 width=140) (actual time=79.794..80.863 rows=1,197 loops=1)

  • Group Key: htp.zone_id, htp.priority, htp.collector_id, htp.device_id, htp.ip, htp.http, htp.https
79. 3.152 80.046 ↓ 2,394.0 2,394 1

Sort (cost=460.77..460.78 rows=1 width=140) (actual time=79.793..80.046 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
80. 0.400 76.894 ↓ 2,394.0 2,394 1

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

81. 24.324 76.494 ↓ 2,394.0 2,394 1

Nested Loop (cost=17.71..460.75 rows=1 width=140) (actual time=0.828..76.494 rows=2,394 loops=1)

  • Join Filter: (d_2.id = dv_1.device_id)
82. 10.079 47.382 ↓ 2,394.0 2,394 1

Hash Join (cost=17.28..459.73 rows=1 width=174) (actual time=0.486..47.382 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
83. 0.948 36.973 ↓ 43.9 4,394 1

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

84. 1.353 1.353 ↓ 86.7 8,668 1

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

85. 34.672 34.672 ↑ 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=0.004..0.004 rows=1 loops=8,668)

  • Index Cond: (device_id = d_2.id)
86. 0.005 0.330 ↑ 2.1 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
87. 0.004 0.325 ↑ 2.1 7 1

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

88. 0.075 0.321 ↑ 2.1 7 1

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

  • Group Key: ce.zone_id, ce.collector_id, cep.value, ces.value
89. 0.017 0.246 ↑ 2.1 7 1

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

  • Hash Cond: (ce.collector_id = ces.collector_id)
90. 0.012 0.161 ↑ 2.1 7 1

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

  • Hash Cond: (cep.collector_id = ce.collector_id)
91. 0.072 0.072 ↓ 3.5 7 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
93. 0.002 0.075 ↑ 2.1 7 1

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

94. 0.073 0.073 ↑ 2.1 7 1

HashAggregate (cost=5.45..5.60 rows=15 width=11) (actual time=0.070..0.073 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
  • Rows Removed by Filter: 12
  • -> Seq Scan on config cfg_3 (cost=0.00..4.71 rows=27 width=38) (actual time=0.016..0.030 ro
  • Filter: (key = ANY ('{profileDiscovery.collectHTTP,enabled,archived}'::text[]))
  • Rows Removed by Filter: 170
95. 0.004 0.068 ↓ 3.5 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
96. 0.064 0.064 ↓ 3.5 7 1

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

  • Filter: ((value ~ '^[0-9,]+$'::text) AND (key = 'profileDiscovery.httpsPorts'::text))
  • Rows Removed by Filter: 190
97. 4.788 4.788 ↑ 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.002..0.002 rows=1 loops=2,394)

  • Index Cond: (device_id = dp.device_id)
  • Filter: (target OR trusted)
98. 0.081 58.813 ↑ 73.7 218 1

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

99. 1.213 58.732 ↑ 73.7 218 1

HashAggregate (cost=15,764.66..19,940.78 rows=16,062 width=138) (actual time=57.779..58.732 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
100.          

Initplan (forHashAggregate)

101. 0.016 0.016 ↑ 1.0 1 1

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

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

103. 0.111 57.496 ↑ 36.8 436 1

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

  • Filter: ((cc.collector_id IS NOT NULL) OR (ct.cidrval IS NOT NULL))
104. 0.568 43.869 ↓ 3.8 436 1

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

  • Join Filter: (dr_1.collector_id = cc.collector_id)
  • Rows Removed by Join Filter: 436
105. 0.353 29.785 ↓ 31.1 436 1

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

  • Join Filter: (dr_1.collector_id = cfg_4.collector_id)
  • Rows Removed by Join Filter: 2616
106. 0.037 0.088 ↑ 2.1 7 1

GroupAggregate (cost=5.35..6.31 rows=15 width=11) (actual time=0.058..0.088 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'::
  • Rows Removed by Filter: 12
107. 0.016 0.051 ↑ 1.0 27 1

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

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

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

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

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

110. 8.237 29.084 ↓ 27.2 436 1

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

  • Join Filter: (d_3.id = dp_1.device_id)
  • -> Index Scan using pk_device_ports on device_ports dp_1 (cost=0.42..0.50 rows=1 width=34) (actual time=0.002..0.002 rows=0 loops=454
111. 0.599 20.847 ↓ 126.3 4,546 1

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

  • Index Cond: (device_id = dr_1.device_id)
  • Filter: (open && '{445,139}'::integer[])
  • Rows Removed by Filter: 1
112. 0.006 0.006 ↑ 1.0 1 1

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

  • Filter: (scantype = 'tcpPorts'::text)
  • Rows Removed by Filter: 17
113. 19.039 20.242 ↓ 45.5 4,546 1

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

  • -> Index Scan using idx_device_response_devst on device_response dr_1 (cost=0.43..4.45 rows=1 width=12) (actual time=0.00
114. 1.203 1.203 ↓ 86.7 8,668 1

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

  • Index Cond: ((scantype_id = st.id) AND (device_id = d_3.id))
115. 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
116. 13.516 13.516 ↑ 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.018..0.031 rows=1 loops=436)

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

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

118. 0.035 52.646 ↓ 110.0 110 1

Group (cost=1,312.61..1,312.65 rows=1 width=149) (actual time=52.602..52.646 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_
119. 0.077 52.611 ↓ 128.0 128 1

Sort (cost=1,312.61..1,312.62 rows=1 width=109) (actual time=52.600..52.611 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
  • Sort Method: quicksort Memory: 45kB
120. 0.220 52.534 ↓ 128.0 128 1

Hash Join (cost=1,312.16..1,312.60 rows=1 width=109) (actual time=52.339..52.534 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
121. 0.097 0.515 ↑ 7.5 2 1

HashAggregate (cost=5.75..5.90 rows=15 width=11) (actual time=0.512..0.515 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 (
  • Rows Removed by Filter: 17
122. 0.418 0.418 ↓ 1.0 30 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 84kB
124. 0.099 51.647 ↓ 512.0 512 1

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

  • Merge Cond: (config.zone_id = ids.zone_id)
125. 0.084 0.403 ↑ 3.5 4 1

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

  • Group Key: config.zone_id, config.collector_id
126. 0.017 0.319 ↑ 1.0 25 1

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

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

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

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

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

129. 0.177 51.112 ↓ 128.0 128 1

Sort (cost=1,300.53..1,300.53 rows=1 width=131) (actual time=51.085..51.112 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
130. 0.120 50.935 ↓ 128.0 128 1

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

131. 0.080 50.815 ↓ 128.0 128 1

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

  • Sort Key: d_4.ip, dr_2.collector_id, snac.aliasorder
  • Sort Method: quicksort Memory: 45kB
132. 0.785 50.735 ↓ 128.0 128 1

GroupAggregate (cost=1,300.44..1,300.48 rows=1 width=99) (actual time=49.981..50.735 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
133. 0.381 49.950 ↓ 532.0 532 1

Sort (cost=1,300.44..1,300.44 rows=1 width=67) (actual time=49.908..49.950 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
134. 49.569 49.569 ↓ 532.0 532 1

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

  • Join Filter: (dr_2.collector_id = sa_1.collector_id)
  • Rows Removed by Join Filter: 30
  • -> Nested Loop Left Join (cost=979.70..1299.58 rows=5 width=64) (actual time=37.880..49.026 rows=
  • Join Filter: (snac.collector_id = dr_2.collector_id)
  • Rows Removed by Join Filter: 30
  • -> Nested Loop (cost=979.56..1298.73 rows=5 width=56) (actual time=37.874..48.415 rows=562
  • -> Nested Loop (cost=978.88..1283.26 rows=19 width=64) (actual time=37.776..45.250 ro
  • Join Filter: ((COALESCE(dl.id, d_4.id)) = dv_2.device_id)
  • -> Nested Loop (cost=978.45..1212.78 rows=113 width=59) (actual time=37.768..44
  • -> Group (cost=978.03..979.03 rows=100 width=62) (actual time=37.700..39.
  • Group Key: ids.zone_id, d_4.id, dl.id
  • -> Sort (cost=978.03..978.28 rows=100 width=26) (actual time=37.699
  • Sort Key: ids.zone_id, d_4.id, dl.id
  • Sort Method: quicksort Memory: 412kB
  • -> Nested Loop Left Join (cost=451.67..974.71 rows=100 width=
135. 0.000 0.000 ↓ 0.0

Nested Loop (cost=451.25..898.07 rows=100 width=15) (actual rows= loops=)

  • -> Group (cost=450.82..451.57 rows=100 width=8) (
  • Group Key: ids.zone_id, (COALESCE(d_5.device_
  • -> Sort (cost=450.82..451.07 rows=100 width
  • Sort Key: ids.zone_id, (COALESCE(d_5.de
  • Sort Method: quicksort Memory: 791kB
  • -> Nested Loop (cost=0.43..447.50 row
  • -> CTE Scan on devs ids (cost=0
  • -> Index Scan using pk_device on
136. 0.000 17.336 ↑ 1.0 1 8,668

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)
  • -> Index Scan using pk_device on device d_4 (cost
  • Index Cond: (id = (COALESCE(d_5.device_id, d_
  • -> Index Scan using idx_zone_device_devid on device dl
  • Index Cond: (device_id = d_4.id)
  • -> Index Only Scan using pk_device_alias on device_snmpalias dsa (cost=0.
  • Index Cond: (device_id = (COALESCE(dl.id, d_4.id)))
  • Heap Fetches: 190
  • -> Index Scan using idx_device_values_devid on device_values dv_2 (cost=0.42..0
  • Index Cond: (device_id = dsa.device_id)
  • Filter: (target OR trusted)
  • -> Index Scan using idx_device_response_devst on device_response dr_2 (cost=0.68..0.8
  • Index Cond: ((scantype_id = system.scantype_ro('snmpDiscovery'::text)) AND (devic
  • -> Index Scan using pk_snmpalias on snmpalias snac (cost=0.14..0.16 rows=1 width=16) (actua
  • Index Cond: (id = dsa.snmpalias_id)
  • -> Index Scan using pk_snmpalias on snmpalias sa_1 (cost=0.14..0.16 rows=1 width=15) (actual time
  • Index Cond: (id = dsa.snmpalias_id)
137.          

CTE updates

138. 0.002 43.258 ↓ 0.0 0 1

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

139. 0.000 43.256 ↓ 0.0 0 1

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

140. 3.282 3.282 ↓ 6,841.0 13,682 1

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

141. 41.046 41.046 ↓ 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.003..0.003 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
142.          

CTE inserts

143. 0.003 1,640.106 ↓ 0.0 0 1

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

  • Conflict Resolution: NOTHING
  • Conflict Arbiter Indexes: uk_target
  • Tuples Inserted: 0
  • Conflicting Tuples: 0
144. 0.002 1,640.103 ↓ 0.0 0 1

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

145. 0.002 1,640.101 ↓ 0.0 0 1

HashAggregate (cost=9.65..9.68 rows=3 width=88) (actual time=1,640.101..1,640.101 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
146. 0.000 1,640.099 ↓ 0.0 0 1

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

147. 4.735 1,639.015 ↓ 0.0 0 1

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

148. 3.973 6.122 ↓ 6,841.0 13,682 1

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

  • Join Filter: false
149. 2.149 2.149 ↓ 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..2.149 rows=13,682 loops=1)

150. 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
151. 1,628.158 1,628.158 ↑ 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=0.119..0.119 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)))
152. 0.000 1.084 ↓ 0.0 0 1

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

153. 0.001 1.084 ↓ 0.0 0 1

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

  • Join Filter: false
154. 1.083 1.083 ↓ 0.0 0 1

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

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

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

  • One-Time Filter: false
156. 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)))
157. 0.000 0.088 ↓ 0.0 0 1

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

158. 0.001 0.088 ↓ 0.0 0 1

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

  • Group Key: pd.zone_id, pd.collector_id, pd.cidrval, pd.priority, pd.overwrite
159. 0.015 0.087 ↓ 0.0 0 1

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

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

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

161. 0.002 0.071 ↓ 0.0 0 1

HashAggregate (cost=161,347.04..161,348.69 rows=165 width=117) (actual time=0.071..0.071 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)
162. 0.001 0.069 ↓ 0.0 0 1

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

163. 0.002 0.028 ↓ 0.0 0 1

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

164. 0.001 0.026 ↓ 0.0 0 1

GroupAggregate (cost=31.02..31.49 rows=11 width=149) (actual time=0.026..0.026 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)
165. 0.007 0.025 ↓ 0.0 0 1

Sort (cost=31.02..31.05 rows=11 width=115) (actual time=0.025..0.025 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
166. 0.001 0.018 ↓ 0.0 0 1

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

167. 0.000 0.017 ↓ 0.0 0 1

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

  • -> Index Scan using idx_device_values_devid on device_values dv_3 (cost=0.42..4.42 rows=1 width=4) (never exe
168. 0.011 0.017 ↓ 0.0 0 1

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

  • Hash Cond: (d_6.zone_id = c_3.zone_id)
  • Index Cond: (device_id = d_6.id)
  • Filter: trusted
169. 0.000 0.000 ↑ 100.0 1 1

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

170. 0.006 0.006 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • -> CTE Scan on protocolcfg c_3 (cost=0.00..0.02 rows=1 width=40) (actual time=0.006..0.006 rows=0
  • Filter: (tracehosts AND pathenabled AND (collector_id IS NOT NULL) AND (scantype = 'pathDisco
  • Rows Removed by Filter: 42
171. 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)
172. 0.000 0.032 ↓ 0.0 0 1

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

173. 0.001 0.032 ↓ 0.0 0 1

GroupAggregate (cost=5,558.75..5,565.25 rows=153 width=149) (actual time=0.032..0.032 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)
174. 0.007 0.031 ↓ 0.0 0 1

Sort (cost=5,558.75..5,559.13 rows=153 width=115) (actual time=0.031..0.031 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
175. 0.001 0.024 ↓ 0.0 0 1

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

  • Join Filter: (c_4.collector_id = cc_1.collector_id)
176. 0.000 0.023 ↓ 0.0 0 1

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

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

  • -> Index Scan using idx_device_values_devid on device_values dv_4 (cost=0.42..4.42 rows=1 width=4) (nev
178. 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)
  • -> CTE Scan on devs d_7 (cost=0.00..2.00 rows=100 width=76) (actual time=0.000..0.000 rows=1 loop
  • Index Cond: (device_id = d_7.id)
  • Filter: target
179. 0.004 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
  • -> CTE Scan on protocolcfg c_4 (cost=0.00..0.02 rows=1 width=40) (actual time=0.004..0.004
  • Filter: (tracehosts AND pathenabled AND (collector_id IS NOT NULL) AND (scantype = 'pat
  • Rows Removed by Filter: 42
180. 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)
181. 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))
182. 0.000 0.000 ↓ 0.0 0

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

  • -> 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))
183. 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))
184. 0.000 0.008 ↓ 0.0 0 1

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

185. 0.000 0.008 ↓ 0.0 0 1

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

  • Group Key: c_5.zone_id, c_5.collector_id, rt.id, rt.route, c_5.hash
186. 0.005 0.008 ↓ 0.0 0 1

Sort (cost=155,744.14..155,744.27 rows=53 width=76) (actual time=0.008..0.008 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
187. 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_2.cidrval)) >>= iprange(rt.route))
188. 0.000 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: (rt.zone_id = c_5.zone_id)
189. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: ((NOT ('169.254.0.0/16'::inet >>= (route)::inet)) AND (NOT ('127.0.0.0/8'::inet >>= (route)::inet
190. 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
191. 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
192. 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))
193. 0.001 0.061 ↓ 0.0 0 1

Subquery Scan on *SELECT* 6 (cost=156,011.89..156,011.98 rows=2 width=149) (actual time=0.061..0.061 rows=0 loops=1)

194. 0.000 0.060 ↓ 0.0 0 1

GroupAggregate (cost=156,011.89..156,011.96 rows=2 width=149) (actual time=0.060..0.060 rows=0 loops=1)

  • Group Key: hd.zone_id, hd.collector_id, hd.cidrval, hd.priority
195. 0.006 0.060 ↓ 0.0 0 1

Sort (cost=156,011.89..156,011.89 rows=2 width=116) (actual time=0.060..0.060 rows=0 loops=1)

  • Sort Key: hd.zone_id, hd.collector_id, hd.cidrval, hd.priority
  • Sort Method: quicksort Memory: 25kB
196. 0.000 0.054 ↓ 0.0 0 1

Subquery Scan on hd (cost=156,011.81..156,011.88 rows=2 width=116) (actual time=0.054..0.054 rows=0 loops=1)

197. 0.001 0.054 ↓ 0.0 0 1

Unique (cost=156,011.81..156,011.86 rows=2 width=116) (actual time=0.054..0.054 rows=0 loops=1)

198. 0.007 0.053 ↓ 0.0 0 1

Sort (cost=156,011.81..156,011.82 rows=2 width=116) (actual time=0.053..0.053 rows=0 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::int
  • Sort Method: quicksort Memory: 25kB
199. 0.000 0.046 ↓ 0.0 0 1

Append (cost=266.82..156,011.80 rows=2 width=116) (actual time=0.046..0.046 rows=0 loops=1)

200. 0.001 0.037 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_2 (cost=266.82..266.85 rows=1 width=116) (actual time=0.037..0.037 rows=0 loops=1)

201. 0.001 0.036 ↓ 0.0 0 1

Group (cost=266.82..266.84 rows=1 width=180) (actual time=0.036..0.036 rows=0 loops=1)

  • Group Key: c_6.zone_id, d_8.priority, c_6.collector_id, d_8.id, d_8.ip, c_6.hash
202. 0.006 0.035 ↓ 0.0 0 1

Sort (cost=266.82..266.82 rows=1 width=108) (actual time=0.035..0.035 rows=0 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: 25kB
203. 0.000 0.029 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.75..266.81 rows=1 width=108) (actual time=0.029..0.029 rows=0 loops=1)

  • Join Filter: ((c_6.collector_id = cc_3.collector_id) AND (cc_3.zone_id = c_6.zone_id))
  • Filter: (cc_3.id IS NULL)
204. 0.001 0.029 ↓ 0.0 0 1

Nested Loop (cost=0.46..7.60 rows=1 width=108) (actual time=0.029..0.029 rows=0 loops=1)

  • -> Index Scan using idx_device_values_devid on device_values dv_5 (cost=0.42..4.42 rows=1 width=4) (nev
205. 0.020 0.028 ↓ 0.0 0 1

Hash Join (cost=0.04..3.17 rows=1 width=108) (actual time=0.028..0.028 rows=0 loops=1)

  • Hash Cond: (d_8.zone_id = c_6.zone_id)
  • -> CTE Scan on devs d_8 (cost=0.00..2.75 rows=100 width=72) (actual time=0.008..0.008 rows=1 loop
  • Filter: (host(((ip)::cidr)::inet) !~~ '%.255'::text)
  • Index Cond: (device_id = d_8.id)
  • Filter: trusted
206. 0.008 0.008 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • -> CTE Scan on protocolcfg c_6 (cost=0.00..0.02 rows=1 width=40) (actual time=0.007..0.007
  • Filter: (hostenabled AND (collector_id IS NOT NULL) AND (scantype = 'hostDiscovery'::te
  • Rows Removed by Filter: 42
207. 0.000 0.000 ↓ 0.0

Index Scan using idx_zone_attribute_cidr_ipr on attribute_cidr cc_3 (cost=0.29..257.09 rows=141 width=19) (actual rows= loops=)

  • Index Cond: (iprange(cidrval) >>= iprange((d_8.ip)::cidr))
  • Filter: (attributetype_id = system.attributetype('target'::text))
208. 0.000 0.009 ↓ 0.0 0 1

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

209. 0.001 0.009 ↓ 0.0 0 1

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

  • Group Key: c_7.zone_id, c_7.collector_id, r.id, r.route, c_7.hash
210. 0.006 0.008 ↓ 0.0 0 1

Sort (cost=155,744.14..155,744.28 rows=53 width=76) (actual time=0.008..0.008 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
211. 0.001 0.002 ↓ 0.0 0 1

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

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

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

  • Join Filter: (r.zone_id = c_7.zone_id)
213. 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((ro
214. 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
215. 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
216. 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))
217. 3.983 2,993.613 ↓ 6,841.0 13,682 1

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

218. 5.029 2,989.630 ↓ 6,841.0 13,682 1

Sort (cost=25,775.52..25,775.52 rows=2 width=121) (actual time=2,988.611..2,989.630 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.overwrit
  • Sort Method: quicksort Memory: 2153kB
219. 1.256 2,984.601 ↓ 6,841.0 13,682 1

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

220. 4.419 2,977.201 ↓ 13,682.0 13,682 1

Group (cost=23,382.74..23,382.77 rows=1 width=121) (actual time=2,971.441..2,977.201 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, tar
221.          

Initplan (forGroup)

222. 0.008 0.008 ↑ 1.0 1 1

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

223. 19.500 2,972.774 ↓ 13,682.0 13,682 1

Sort (cost=23,382.72..23,382.72 rows=1 width=121) (actual time=2,971.439..2,972.774 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
  • Sort Method: quicksort Memory: 2153kB
224. 7.327 2,953.274 ↓ 13,682.0 13,682 1

Nested Loop Left Join (cost=1.70..23,382.71 rows=1 width=121) (actual time=670.614..2,953.274 rows=13,682 loops=1)

  • Filter: (cc_5.id IS NULL)
  • Rows Removed by Filter: 57187
225. 24.282 749.008 ↓ 48.5 70,869 1

Hash Join (cost=1.41..401.52 rows=1,461 width=121) (actual time=670.047..749.008 rows=70,869 loops=1)

  • Hash Cond: (targets.scantype = st_1.scantype)
226. 724.714 724.714 ↓ 4.4 70,869 1

CTE Scan on targets (cost=0.00..324.64 rows=16,232 width=149) (actual time=670.024..724.714 rows=70,869 loops=1)

227. 0.007 0.012 ↑ 1.0 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
228. 0.005 0.005 ↑ 1.0 18 1

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

229. 2,196.939 2,196.939 ↑ 22.0 1 70,869

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.023..0.031 rows=1 loops=70,869)

  • 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
230. 0.002 6.144 ↓ 0.0 0 1

Group (cost=2,392.69..2,392.72 rows=1 width=121) (actual time=6.144..6.144 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, targe
231.          

Initplan (forGroup)

232. 0.000 0.000 ↓ 0.0 0

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

233. 0.014 6.142 ↓ 0.0 0 1

Sort (cost=2,392.67..2,392.68 rows=1 width=121) (actual time=6.142..6.142 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,
  • Sort Method: quicksort Memory: 25kB
234. 0.001 6.128 ↓ 0.0 0 1

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

  • Filter: (cc_6.id IS NULL)
235. 0.001 6.127 ↓ 0.0 0 1

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

  • Hash Cond: (targets_1.scantype = st_2.scantype)
236. 6.126 6.126 ↓ 0.0 0 1

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

  • Filter: overwrite
  • Rows Removed by Filter: 70869
237. 0.000 0.000 ↓ 0.0 0

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

238. 0.000 0.000 ↓ 0.0 0

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

239. 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))
240. 0.000 3,026.195 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.14..9.63 rows=2 width=144) (actual time=3,026.195..3,026.195 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))
241. 0.001 3,026.195 ↓ 0.0 0 1

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

242. 3,026.194 3,026.194 ↓ 0.0 0 1

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

  • Filter: (priority IS NOT NULL)
  • Rows Removed by Filter: 13682
243. 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)
244. 0.000 0.000 ↓ 0.0 0

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