explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PxuQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2,697,353.083 ↓ 0.0 0 1

Insert on target_highpriority (cost=572,401.98..572,411.47 rows=2 width=144) (actual time=2,697,353.083..2,697,353.083 rows=0 loops=1)

  • Planning time: 20.886 ms
  • Execution time: 2697923.362 ms
2.          

CTE targets

3. 10.900 2,697,352.320 ↓ 1,967.0 3,934 1

Sort (cost=572,383.11..572,383.11 rows=2 width=121) (actual time=2,697,351.733..2,697,352.320 rows=3,934 loops=1)

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

CTE protocolcfg

5. 0.017 3.595 ↓ 14.0 14 1

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

  • Hash Cond: ((c.zone_id = cfg.zone_id) AND (c.collector_id = cfg.collector_id))
6. 0.106 3.447 ↑ 2.1 14 1

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

  • Group Key: c.zone_id, c.collector_id, (regexp_replace(c.key, '^(.+Discovery)\..+'::text, '\1'::text))
7. 0.032 3.341 ↑ 2.3 42 1

Sort (cost=403.21..403.46 rows=98 width=70) (actual time=3.336..3.341 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
8. 3.084 3.309 ↑ 2.3 42 1

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

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

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

10. 0.182 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)

11. 0.015 0.015 ↑ 16.7 6 1

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

12. 0.007 0.131 ↓ 1.1 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.066 0.124 ↓ 1.1 18 1

HashAggregate (cost=10.88..11.05 rows=17 width=19) (actual time=0.117..0.124 rows=18 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: 1
14. 0.058 0.058 ↑ 1.2 34 1

Seq Scan on config cfg (cost=0.00..6.68 rows=41 width=38) (actual time=0.005..0.058 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
15.          

CTE devs

16. 0.496 7.797 ↓ 7.7 765 1

Nested Loop (cost=0.43..446.25 rows=100 width=51) (actual time=1.087..7.797 rows=765 loops=1)

17. 1.181 1.181 ↓ 7.7 765 1

Function Scan on json_to_recordset (cost=0.00..1.00 rows=100 width=36) (actual time=1.049..1.181 rows=765 loops=1)

18. 6.120 6.120 ↑ 1.0 1 765

Index Scan using pk_device on device d (cost=0.42..4.44 rows=1 width=15) (actual time=0.008..0.008 rows=1 loops=765)

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

CTE rts

20. 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
21.          

CTE targets

22. 50.752 1,375.614 ↓ 1.1 20,460 1

HashAggregate (cost=367,223.83..367,418.40 rows=19,457 width=149) (actual time=1,369.605..1,375.614 rows=20,460 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)
23. 7.415 1,324.862 ↓ 3.6 69,675 1

Append (cost=2,481.45..366,737.40 rows=19,457 width=149) (actual time=47.778..1,324.862 rows=69,675 loops=1)

24. 14.005 62.662 ↓ 62,220.0 62,220 1

Hash Join (cost=2,481.45..2,498.60 rows=1 width=149) (actual time=47.778..62.662 rows=62,220 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))
25. 4.336 43.683 ↓ 5.5 3,660 1

HashAggregate (cost=2,054.41..2,061.07 rows=666 width=81) (actual time=42.791..43.683 rows=3,660 loops=1)

  • Group Key: d_1.zone_id, d_1.priority, dv.trusted, dr.collector_id, d_1.id, d_1.ip
26. 1.665 39.347 ↓ 9.6 6,384 1

Nested Loop Left Join (cost=1.28..2,044.42 rows=666 width=77) (actual time=1.131..39.347 rows=6,384 loops=1)

27. 0.815 16.262 ↓ 8.1 765 1

Nested Loop (cost=0.85..492.96 rows=94 width=73) (actual time=1.108..16.262 rows=765 loops=1)

28. 0.384 10.092 ↓ 8.1 765 1

Nested Loop (cost=0.42..447.25 rows=94 width=76) (actual time=1.092..10.092 rows=765 loops=1)

29. 8.178 8.178 ↓ 7.7 765 1

CTE Scan on devs d_1 (cost=0.00..2.00 rows=100 width=72) (actual time=1.088..8.178 rows=765 loops=1)

30. 1.530 1.530 ↑ 1.0 1 765

Index Scan using pk_device on device zd (cost=0.42..4.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=765)

  • Index Cond: (id = d_1.id)
  • Filter: ((NOT meta) AND (ip IS NOT NULL))
31. 5.355 5.355 ↑ 1.0 1 765

Index Scan using idx_device_values_devid on device_values dv (cost=0.42..0.48 rows=1 width=5) (actual time=0.007..0.007 rows=1 loops=765)

  • Index Cond: (device_id = zd.id)
  • Filter: (target OR trusted)
32. 21.420 21.420 ↓ 1.1 8 765

Index Scan using idx_device_response_devid on device_response dr (cost=0.43..16.43 rows=7 width=8) (actual time=0.008..0.028 rows=8 loops=765)

  • Index Cond: (device_id = d_1.id)
33. 0.006 4.974 ↑ 1.9 17 1

Hash (cost=426.64..426.64 rows=32 width=72) (actual time=4.974..4.974 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
34. 0.006 4.968 ↑ 1.9 17 1

Group (cost=425.92..426.32 rows=32 width=72) (actual time=4.959..4.968 rows=17 loops=1)

  • Group Key: "*SELECT* 1".zone_id, "*SELECT* 1".collector_id, ('snmpDiscovery'::text), "*SELECT* 1".config
35. 0.019 4.962 ↑ 1.9 17 1

Sort (cost=425.92..426.00 rows=32 width=72) (actual time=4.959..4.962 rows=17 loops=1)

  • Sort Key: "*SELECT* 1".zone_id, "*SELECT* 1".collector_id, ('snmpDiscovery'::text), "*SELECT* 1".config
  • Sort Method: quicksort Memory: 26kB
36. 0.018 4.943 ↑ 1.9 17 1

HashAggregate (cost=424.48..424.80 rows=32 width=72) (actual time=4.940..4.943 rows=17 loops=1)

  • Group Key: "*SELECT* 1".zone_id, "*SELECT* 1".collector_id, ('snmpDiscovery'::text), "*SELECT* 1".config
37. 0.003 4.925 ↑ 1.9 17 1

Append (cost=43.47..424.16 rows=32 width=72) (actual time=0.423..4.925 rows=17 loops=1)

38. 0.001 0.442 ↑ 4.7 3 1

Subquery Scan on *SELECT* 1 (cost=43.47..44.03 rows=14 width=72) (actual time=0.423..0.442 rows=3 loops=1)

39. 0.035 0.441 ↑ 4.7 3 1

Group (cost=43.47..43.89 rows=14 width=104) (actual time=0.423..0.441 rows=3 loops=1)

  • Group Key: sa.zone_id, sa.collector_id, (array_agg(DISTINCT sa.alias ORDER BY sa.alias))
40. 0.032 0.406 ↑ 4.7 3 1

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

  • Sort Key: sa.zone_id, sa.collector_id, (array_agg(DISTINCT sa.alias ORDER BY sa.alias))
  • Sort Method: quicksort Memory: 26kB
41. 0.010 0.374 ↑ 4.7 3 1

Hash Join (cost=35.26..43.21 rows=14 width=40) (actual time=0.298..0.374 rows=3 loops=1)

  • Hash Cond: (sa.collector_id = phases.collector_id)
42. 0.090 0.279 ↑ 66.7 3 1

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

  • Group Key: sa.zone_id, sa.collector_id
43. 0.038 0.189 ↑ 4.3 60 1

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

  • Sort Key: sa.zone_id, sa.collector_id
  • Sort Method: quicksort Memory: 28kB
44. 0.050 0.151 ↑ 4.3 60 1

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

  • Group Key: sa.zone_id, sa.collector_id, sa.alias
45. 0.009 0.101 ↑ 2.3 112 1

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

46. 0.031 0.048 ↓ 1.1 60 1

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

  • Group Key: sa.zone_id, sa.collector_id, sa.alias
47. 0.017 0.017 ↑ 1.0 60 1

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

48. 0.016 0.044 ↑ 3.8 52 1

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

49. 0.006 0.006 ↑ 1.0 1 1

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

  • Filter: (name = 'common'::text)
50. 0.022 0.022 ↑ 2.0 1 1

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

  • Filter: ((key = 'snmpDiscovery.useCommonCredentials'::text) AND (value = 'true'::text))
  • Rows Removed by Filter: 196
51. 0.003 0.085 ↑ 4.7 3 1

Hash (cost=5.65..5.65 rows=14 width=4) (actual time=0.085..0.085 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.000 0.082 ↑ 4.7 3 1

Subquery Scan on phases (cost=5.37..5.65 rows=14 width=4) (actual time=0.079..0.082 rows=3 loops=1)

53. 0.031 0.082 ↑ 4.7 3 1

HashAggregate (cost=5.37..5.51 rows=14 width=11) (actual time=0.079..0.082 rows=3 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: 16
54. 0.051 0.051 ↑ 1.0 24 1

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

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

Nested Loop Left Join (cost=322.05..379.88 rows=15 width=72) (actual time=0.707..0.861 rows=7 loops=1)

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

Nested Loop Left Join (cost=322.05..373.71 rows=15 width=86) (actual time=0.690..0.806 rows=7 loops=1)

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

Nested Loop Left Join (cost=322.05..367.85 rows=15 width=63) (actual time=0.680..0.765 rows=7 loops=1)

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

Hash Right Join (cost=322.05..362.66 rows=15 width=40) (actual time=0.652..0.735 rows=7 loops=1)

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

GroupAggregate (cost=314.24..354.52 rows=19 width=40) (actual time=0.553..0.630 rows=7 loops=1)

  • Group Key: cpe.zone_id, cpe.collector_id
60. 0.072 0.530 ↑ 31.7 126 1

Sort (cost=314.24..324.24 rows=4,000 width=12) (actual time=0.523..0.530 rows=126 loops=1)

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

Hash Join (cost=9.61..34.92 rows=4,000 width=12) (actual time=0.282..0.458 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))
62. 0.041 0.041 ↑ 1.0 106 1

Seq Scan on config cpe (cost=0.00..4.46 rows=106 width=31) (actual time=0.005..0.041 rows=106 loops=1)

  • Filter: (value = 'true'::text)
  • Rows Removed by Filter: 91
63. 0.014 0.232 ↑ 3.1 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
64. 0.038 0.218 ↑ 3.1 21 1

Hash Join (cost=2.76..8.62 rows=66 width=43) (actual time=0.147..0.218 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])
65. 0.049 0.049 ↓ 2.8 187 1

Seq Scan on config cpp (cost=0.00..4.96 rows=66 width=34) (actual time=0.004..0.049 rows=187 loops=1)

  • Filter: (length(value) > 0)
  • Rows Removed by Filter: 10
66. 0.007 0.131 ↑ 33.3 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 0.124 0.124 ↑ 33.3 3 1

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

68. 0.003 0.093 ↑ 2.1 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
69. 0.011 0.090 ↑ 2.1 7 1

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

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

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

71. 0.006 0.067 ↑ 2.1 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
72. 0.001 0.061 ↑ 2.1 7 1

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

73. 0.032 0.060 ↑ 2.1 7 1

HashAggregate (cost=5.45..5.60 rows=15 width=11) (actual time=0.057..0.060 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
74. 0.028 0.028 ↑ 1.0 27 1

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

  • Filter: (key = ANY ('{portDiscovery.enabled,enabled,archived}'::text[]))
  • Rows Removed by Filter: 170
75. 0.004 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)

76. 0.024 0.024 ↓ 0.0 0 1

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

  • Filter: ((key = 'profileDiscovery.scanStandardTCPPorts'::text) AND (value = 'true'::text))
  • Rows Removed by Filter: 197
77. 0.009 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)

78. 0.026 0.026 ↓ 1.8 7 1

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

  • Filter: ((key = 'portDiscovery.useInfectionPorts'::text) AND (value = 'true'::text))
  • Rows Removed by Filter: 190
79. 0.005 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)

80. 0.023 0.023 ↓ 1.8 7 1

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

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

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

  • Filter: dnsenabled
  • Rows Removed by Filter: 7
82. 0.002 0.002 ↓ 0.0 0 1

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

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

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

  • Filter: (outboundleak AND leakenabled AND (scantype = 'leakDiscovery'::text))
  • Rows Removed by Filter: 14
84. 1.424 51.395 ↓ 2,184.0 2,184 1

Subquery Scan on http (cost=460.77..460.82 rows=1 width=149) (actual time=48.847..51.395 rows=2,184 loops=1)

85. 0.893 49.971 ↓ 2,184.0 2,184 1

Group (cost=460.77..460.79 rows=1 width=140) (actual time=48.836..49.971 rows=2,184 loops=1)

  • Group Key: htp.zone_id, htp.priority, htp.collector_id, htp.device_id, htp.ip, htp.http, htp.https
86. 3.933 49.078 ↓ 2,184.0 2,184 1

Sort (cost=460.77..460.77 rows=1 width=140) (actual time=48.835..49.078 rows=2,184 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: 315kB
87. 0.438 45.145 ↓ 2,184.0 2,184 1

Subquery Scan on htp (cost=17.71..460.76 rows=1 width=140) (actual time=0.909..45.145 rows=2,184 loops=1)

88. 30.146 44.707 ↓ 2,184.0 2,184 1

Nested Loop (cost=17.71..460.75 rows=1 width=140) (actual time=0.909..44.707 rows=2,184 loops=1)

  • Join Filter: (d_2.id = dv_1.device_id)
89. 4.237 10.193 ↓ 2,184.0 2,184 1

Hash Join (cost=17.28..459.73 rows=1 width=175) (actual time=0.493..10.193 rows=2,184 loops=1)

  • Hash Cond: (dp.zone_id = ports.zone_id)
  • Join Filter: (dp.open && (ports.http || ports.https))
  • Rows Removed by Join Filter: 3171
90. 0.834 5.552 ↓ 7.7 765 1

Nested Loop (cost=0.42..441.00 rows=100 width=107) (actual time=0.020..5.552 rows=765 loops=1)

91. 0.128 0.128 ↓ 7.7 765 1

CTE Scan on devs d_2 (cost=0.00..2.00 rows=100 width=68) (actual time=0.000..0.128 rows=765 loops=1)

92. 4.590 4.590 ↑ 1.0 1 765

Index Scan using pk_device_ports on device_ports dp (cost=0.42..4.38 rows=1 width=39) (actual time=0.006..0.006 rows=1 loops=765)

  • Index Cond: (device_id = d_2.id)
93. 0.006 0.404 ↑ 2.1 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
94. 0.001 0.398 ↑ 2.1 7 1

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

95. 0.090 0.397 ↑ 2.1 7 1

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

  • Group Key: ce.zone_id, ce.collector_id, cep.value, ces.value
96. 0.013 0.307 ↑ 2.1 7 1

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

  • Hash Cond: (ce.collector_id = ces.collector_id)
97. 0.019 0.197 ↑ 2.1 7 1

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

  • Hash Cond: (cep.collector_id = ce.collector_id)
98. 0.098 0.098 ↓ 3.5 7 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
100. 0.002 0.076 ↑ 2.1 7 1

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

101. 0.034 0.074 ↑ 2.1 7 1

HashAggregate (cost=5.45..5.60 rows=15 width=11) (actual time=0.072..0.074 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
102. 0.040 0.040 ↑ 1.0 27 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
104. 0.094 0.094 ↓ 3.5 7 1

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

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

Index Scan using idx_device_values_devid on device_values dv_1 (cost=0.42..0.50 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,184)

  • Index Cond: (device_id = dp.device_id)
  • Filter: (target OR trusted)
106. 0.312 191.140 ↑ 37.8 511 1

Subquery Scan on cifsp (cost=40,232.55..45,492.35 rows=19,302 width=149) (actual time=187.342..191.140 rows=511 loops=1)

107. 4.185 190.828 ↑ 37.8 511 1

HashAggregate (cost=40,232.55..45,251.07 rows=19,302 width=139) (actual time=187.338..190.828 rows=511 loops=1)

  • Group Key: dr_1.collector_id, d_3.priority, phases_2.zone_id, d_3.id, d_3.ip, dp_1.open
108. 0.635 186.643 ↑ 37.8 511 1

Nested Loop Left Join (cost=7.37..39,943.02 rows=19,302 width=107) (actual time=1.153..186.643 rows=511 loops=1)

  • Filter: ((cc.collector_id IS NOT NULL) OR (ct.cidrval IS NOT NULL))
109. 0.893 101.182 ↓ 3.7 511 1

Nested Loop Left Join (cost=7.08..4,835.40 rows=137 width=111) (actual time=1.037..101.182 rows=511 loops=1)

  • Join Filter: (dr_1.collector_id = cc.collector_id)
  • Rows Removed by Join Filter: 511
110. 0.182 10.864 ↓ 30.1 511 1

Nested Loop (cost=6.79..472.99 rows=17 width=107) (actual time=0.119..10.864 rows=511 loops=1)

  • Join Filter: (d_3.id = dp_1.device_id)
111. 0.590 6.824 ↓ 40.2 1,286 1

Hash Join (cost=6.37..456.88 rows=32 width=80) (actual time=0.110..6.824 rows=1,286 loops=1)

  • Hash Cond: (dr_1.collector_id = phases_2.collector_id)
112. 0.223 6.161 ↓ 33.0 1,286 1

Nested Loop (cost=0.43..450.48 rows=39 width=76) (actual time=0.031..6.161 rows=1,286 loops=1)

113. 0.010 0.010 ↑ 1.0 1 1

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

  • Filter: (scantype = 'tcpPorts'::text)
  • Rows Removed by Filter: 17
114. 0.406 5.928 ↓ 12.9 1,286 1

Nested Loop (cost=0.43..448.25 rows=100 width=80) (actual time=0.020..5.928 rows=1,286 loops=1)

115. 0.167 0.167 ↓ 7.7 765 1

CTE Scan on devs d_3 (cost=0.00..2.00 rows=100 width=68) (actual time=0.000..0.167 rows=765 loops=1)

116. 5.355 5.355 ↓ 2.0 2 765

Index Scan using idx_device_response_devst on device_response dr_1 (cost=0.43..4.45 rows=1 width=12) (actual time=0.006..0.007 rows=2 loops=765)

  • Index Cond: ((scantype_id = st.id) AND (device_id = d_3.id))
117. 0.006 0.073 ↑ 2.1 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
118. 0.000 0.067 ↑ 2.1 7 1

Subquery Scan on phases_2 (cost=5.45..5.75 rows=15 width=8) (actual time=0.064..0.067 rows=7 loops=1)

119. 0.028 0.067 ↑ 2.1 7 1

HashAggregate (cost=5.45..5.60 rows=15 width=11) (actual time=0.064..0.067 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
120. 0.039 0.039 ↑ 1.0 27 1

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

  • Filter: (key = ANY ('{profileDiscovery.collectCIFS,enabled,archived}'::text[]))
  • Rows Removed by Filter: 170
121. 3.858 3.858 ↓ 0.0 0 1,286

Index Scan using pk_device_ports on device_ports dp_1 (cost=0.42..0.49 rows=1 width=35) (actual time=0.003..0.003 rows=0 loops=1,286)

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

Index Scan using idx_zone_attribute_cidr_ipr on attribute_cidr cc (cost=0.29..254.85 rows=141 width=11) (actual time=0.058..0.175 rows=1 loops=511)

  • Index Cond: (iprange(cidrval) >>= iprange((d_3.ip)::cidr))
  • Filter: (attributetype_id = system.attributetype('target'::text))
  • Rows Removed by Filter: 16
123. 84.826 84.826 ↑ 141.0 1 511

Index Scan using idx_zone_attribute_cidr_ipr on attribute_cidr ct (cost=0.29..254.85 rows=141 width=7) (actual time=0.083..0.166 rows=1 loops=511)

  • Index Cond: (iprange(cidrval) >>= iprange((d_3.ip)::cidr))
  • Filter: (attributetype_id = system.attributetype('trusted'::text))
  • Rows Removed by Filter: 16
124. 0.036 73.216 ↓ 182.0 182 1

Subquery Scan on *SELECT* 4 (cost=1,471.11..1,471.15 rows=1 width=149) (actual time=73.089..73.216 rows=182 loops=1)

125. 0.079 73.180 ↓ 182.0 182 1

Group (cost=1,471.11..1,471.14 rows=1 width=149) (actual time=73.088..73.180 rows=182 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
126. 0.148 73.101 ↓ 184.0 184 1

Sort (cost=1,471.11..1,471.11 rows=1 width=109) (actual time=73.086..73.101 rows=184 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: 63kB
127. 0.766 72.953 ↓ 184.0 184 1

Hash Join (cost=1,470.66..1,471.10 rows=1 width=109) (actual time=72.203..72.953 rows=184 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: 368
128. 0.114 0.634 ↑ 5.0 3 1

HashAggregate (cost=5.75..5.90 rows=15 width=11) (actual time=0.628..0.634 rows=3 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: 16
129. 0.520 0.520 ↓ 1.0 30 1

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

  • Filter: ((key = ANY ('{enabled,archived}'::text[])) OR (key ~ 'snmpDiscovery.collect.+'::text))
  • Rows Removed by Filter: 167
130. 0.298 71.553 ↓ 368.0 736 1

Hash (cost=1,464.88..1,464.88 rows=2 width=148) (actual time=71.553..71.553 rows=736 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 142kB
131. 0.164 71.255 ↓ 368.0 736 1

Merge Join (cost=1,464.03..1,464.88 rows=2 width=148) (actual time=70.912..71.255 rows=736 loops=1)

  • Merge Cond: (config.zone_id = ids.zone_id)
132. 0.116 0.526 ↑ 3.5 4 1

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

  • Group Key: config.zone_id, config.collector_id
133. 0.022 0.410 ↑ 1.0 25 1

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

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

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

  • Filter: ((value IS NOT NULL) AND (key ~ 'snmpDiscovery'::text))
  • Rows Removed by Filter: 172
135. 0.081 70.565 ↓ 366.5 733 1

Materialize (cost=1,458.98..1,459.01 rows=2 width=108) (actual time=70.458..70.565 rows=733 loops=1)

136. 0.348 70.484 ↓ 92.0 184 1

Sort (cost=1,458.98..1,458.99 rows=2 width=131) (actual time=70.456..70.484 rows=184 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: 63kB
137. 0.228 70.136 ↓ 92.0 184 1

WindowAgg (cost=1,458.92..1,458.97 rows=2 width=131) (actual time=69.897..70.136 rows=184 loops=1)

138. 0.204 69.908 ↓ 92.0 184 1

Sort (cost=1,458.92..1,458.92 rows=2 width=99) (actual time=69.880..69.908 rows=184 loops=1)

  • Sort Key: d_4.ip, dr_2.collector_id, snac.aliasorder
  • Sort Method: quicksort Memory: 63kB
139. 3.131 69.704 ↓ 92.0 184 1

GroupAggregate (cost=1,458.83..1,458.91 rows=2 width=99) (actual time=66.520..69.704 rows=184 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
140. 2.102 66.573 ↓ 876.0 1,752 1

Sort (cost=1,458.83..1,458.83 rows=2 width=67) (actual time=66.404..66.573 rows=1,752 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: 280kB
141. 0.558 64.471 ↓ 876.0 1,752 1

Nested Loop (cost=977.64..1,458.82 rows=2 width=67) (actual time=19.763..64.471 rows=1,752 loops=1)

142. 0.978 62.161 ↓ 876.0 1,752 1

Nested Loop Left Join (cost=977.50..1,458.48 rows=2 width=64) (actual time=19.760..62.161 rows=1,752 loops=1)

  • Join Filter: (snac.collector_id = dr_2.collector_id)
  • Rows Removed by Join Filter: 204
143. 8.478 59.431 ↓ 876.0 1,752 1

Nested Loop (cost=977.36..1,458.14 rows=2 width=56) (actual time=19.754..59.431 rows=1,752 loops=1)

144. 0.000 45.535 ↓ 129.0 774 1

Nested Loop (cost=976.68..1,453.18 rows=6 width=64) (actual time=19.621..45.535 rows=774 loops=1)

145. 1.157 38.586 ↓ 37.5 3,754 1

Nested Loop (cost=976.39..1,421.22 rows=100 width=56) (actual time=19.284..38.586 rows=3,754 loops=1)

146. 1.418 21.201 ↓ 40.6 4,057 1

Group (cost=975.97..976.97 rows=100 width=62) (actual time=19.275..21.201 rows=4,057 loops=1)

  • Group Key: ids.zone_id, d_4.id, dl.id
147. 2.883 19.783 ↓ 40.6 4,057 1

Sort (cost=975.97..976.22 rows=100 width=26) (actual time=19.273..19.783 rows=4,057 loops=1)

  • Sort Key: ids.zone_id, d_4.id, dl.id
  • Sort Method: quicksort Memory: 397kB
148. 1.059 16.900 ↓ 40.6 4,057 1

Nested Loop Left Join (cost=451.42..972.64 rows=100 width=26) (actual time=2.822..16.900 rows=4,057 loops=1)

149. 0.563 5.948 ↓ 7.6 761 1

Nested Loop (cost=451.00..897.57 rows=100 width=15) (actual time=2.804..5.948 rows=761 loops=1)

150. 0.176 3.102 ↓ 7.6 761 1

Group (cost=450.57..451.32 rows=100 width=8) (actual time=2.800..3.102 rows=761 loops=1)

  • Group Key: ids.zone_id, (COALESCE(d_5.device_id, d_5.id))
151. 0.480 2.926 ↓ 7.7 765 1

Sort (cost=450.57..450.82 rows=100 width=8) (actual time=2.799..2.926 rows=765 loops=1)

  • Sort Key: ids.zone_id, (COALESCE(d_5.device_id, d_5.id))
  • Sort Method: quicksort Memory: 60kB
152. 0.052 2.446 ↓ 7.7 765 1

Nested Loop (cost=0.42..447.25 rows=100 width=8) (actual time=0.015..2.446 rows=765 loops=1)

153. 0.099 0.099 ↓ 7.7 765 1

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

154. 2.295 2.295 ↑ 1.0 1 765

Index Scan using pk_device on device d_5 (cost=0.42..4.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=765)

  • Index Cond: (id = ids.id)
155. 2.283 2.283 ↑ 1.0 1 761

Index Scan using pk_device on device d_4 (cost=0.42..4.44 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=761)

  • Index Cond: (id = (COALESCE(d_5.device_id, d_5.id)))
156. 9.893 9.893 ↑ 2.0 5 761

Index Scan using idx_zone_device_devid on device dl (cost=0.42..0.65 rows=10 width=15) (actual time=0.003..0.013 rows=5 loops=761)

  • Index Cond: (device_id = d_4.id)
157. 16.228 16.228 ↑ 1.0 1 4,057

Index Scan using idx_device_values_devid on device_values dv_2 (cost=0.42..4.42 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=4,057)

  • Index Cond: (device_id = (COALESCE(dl.id, d_4.id)))
  • Filter: (target OR trusted)
  • Rows Removed by Filter: 0
158. 7.508 7.508 ↓ 0.0 0 3,754

Index Only Scan using pk_device_alias on device_snmpalias dsa (cost=0.29..0.31 rows=1 width=8) (actual time=0.001..0.002 rows=0 loops=3,754)

  • Index Cond: (device_id = dv_2.device_id)
  • Heap Fetches: 774
159. 5.418 5.418 ↓ 2.0 2 774

Index Scan using idx_device_response_devst on device_response dr_2 (cost=0.68..0.82 rows=1 width=8) (actual time=0.005..0.007 rows=2 loops=774)

  • Index Cond: ((scantype_id = system.scantype_ro('snmpDiscovery'::text)) AND (device_id = dv_2.device_id))
160. 1.752 1.752 ↑ 1.0 1 1,752

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

  • Index Cond: (id = dsa.snmpalias_id)
161. 1.752 1.752 ↑ 1.0 1 1,752

Index Scan using pk_snmpalias on snmpalias sa_1 (cost=0.14..0.16 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=1,752)

  • Index Cond: (id = dsa.snmpalias_id)
162. 0.000 0.075 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=160,716.58..160,723.70 rows=150 width=149) (actual time=0.075..0.075 rows=0 loops=1)

163. 0.001 0.075 ↓ 0.0 0 1

GroupAggregate (cost=160,716.58..160,722.20 rows=150 width=149) (actual time=0.075..0.075 rows=0 loops=1)

  • Group Key: pd.zone_id, pd.collector_id, pd.cidrval, pd.priority, pd.overwrite
164. 0.011 0.074 ↓ 0.0 0 1

Sort (cost=160,716.58..160,716.95 rows=150 width=117) (actual time=0.074..0.074 rows=0 loops=1)

  • Sort Key: pd.zone_id, pd.collector_id, pd.cidrval, pd.priority, pd.overwrite
  • Sort Method: quicksort Memory: 25kB
165. 0.000 0.063 ↓ 0.0 0 1

Subquery Scan on pd (cost=160,708.16..160,711.16 rows=150 width=117) (actual time=0.063..0.063 rows=0 loops=1)

166. 0.002 0.063 ↓ 0.0 0 1

HashAggregate (cost=160,708.16..160,709.66 rows=150 width=117) (actual time=0.063..0.063 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)
167. 0.000 0.061 ↓ 0.0 0 1

Append (cost=28.97..160,704.78 rows=150 width=117) (actual time=0.061..0.061 rows=0 loops=1)

168. 0.001 0.026 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=28.97..29.50 rows=10 width=117) (actual time=0.026..0.026 rows=0 loops=1)

169. 0.002 0.025 ↓ 0.0 0 1

GroupAggregate (cost=28.97..29.40 rows=10 width=149) (actual time=0.025..0.025 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)
170. 0.009 0.023 ↓ 0.0 0 1

Sort (cost=28.97..29.00 rows=10 width=115) (actual time=0.023..0.023 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
171. 0.001 0.014 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.88..28.81 rows=10 width=115) (actual time=0.014..0.014 rows=0 loops=1)

172. 0.000 0.013 ↓ 0.0 0 1

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

173. 0.008 0.013 ↓ 0.0 0 1

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

  • Hash Cond: (d_6.zone_id = c_3.zone_id)
174. 0.001 0.001 ↑ 100.0 1 1

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

175. 0.001 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
176. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: (tracehosts AND pathenabled AND (collector_id IS NOT NULL) AND (scantype = 'pathDiscovery'::text))
  • Rows Removed by Filter: 14
177. 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
178. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (device_id = d_6.parent_id)
179. 0.001 0.026 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=5,035.04..5,042.34 rows=139 width=117) (actual time=0.026..0.026 rows=0 loops=1)

180. 0.001 0.025 ↓ 0.0 0 1

GroupAggregate (cost=5,035.04..5,040.95 rows=139 width=149) (actual time=0.025..0.025 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)
181. 0.011 0.024 ↓ 0.0 0 1

Sort (cost=5,035.04..5,035.39 rows=139 width=115) (actual time=0.024..0.024 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
182. 0.000 0.013 ↓ 0.0 0 1

Nested Loop (cost=89.84..5,030.09 rows=139 width=115) (actual time=0.013..0.013 rows=0 loops=1)

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

Nested Loop Left Join (cost=0.88..28.81 rows=10 width=119) (actual time=0.013..0.013 rows=0 loops=1)

184. 0.000 0.013 ↓ 0.0 0 1

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

185. 0.010 0.013 ↓ 0.0 0 1

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

  • Hash Cond: (d_7.zone_id = c_4.zone_id)
186. 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)

187. 0.001 0.003 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
188. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (tracehosts AND pathenabled AND (collector_id IS NOT NULL) AND (scantype = 'pathDiscovery'::text))
  • Rows Removed by Filter: 14
189. 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
190. 0.000 0.000 ↓ 0.0 0

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

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

Bitmap Heap Scan on attribute_cidr cc_1 (cost=88.96..496.60 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))
192. 0.000 0.000 ↓ 0.0 0

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

193. 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))
194. 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))
195. 0.000 0.009 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=155,632.14..155,632.95 rows=1 width=117) (actual time=0.009..0.009 rows=0 loops=1)

196. 0.001 0.009 ↓ 0.0 0 1

Group (cost=155,632.14..155,632.94 rows=1 width=149) (actual time=0.009..0.009 rows=0 loops=1)

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

Sort (cost=155,632.14..155,632.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
198. 0.000 0.002 ↓ 0.0 0 1

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

  • Join Filter: ((iprange(cc_2.cidrval)) >>= iprange(rt.route))
199. 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)
200. 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)) 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))
201. 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))
202. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Filter: (attributetype_id = system.attributetype('trusted'::text))
204. 0.848 938.959 ↓ 2,289.0 4,578 1

Subquery Scan on *SELECT* 6 (cost=155,897.65..155,897.74 rows=2 width=149) (actual time=928.094..938.959 rows=4,578 loops=1)

205. 9.455 938.111 ↓ 2,289.0 4,578 1

GroupAggregate (cost=155,897.65..155,897.72 rows=2 width=149) (actual time=928.092..938.111 rows=4,578 loops=1)

  • Group Key: hd.zone_id, hd.collector_id, hd.cidrval, hd.priority
206. 8.663 928.656 ↓ 2,289.0 4,578 1

Sort (cost=155,897.65..155,897.65 rows=2 width=116) (actual time=928.079..928.656 rows=4,578 loops=1)

  • Sort Key: hd.zone_id, hd.collector_id, hd.cidrval, hd.priority
  • Sort Method: quicksort Memory: 836kB
207. 0.845 919.993 ↓ 2,289.0 4,578 1

Subquery Scan on hd (cost=155,897.57..155,897.64 rows=2 width=116) (actual time=917.162..919.993 rows=4,578 loops=1)

208. 1.545 919.148 ↓ 2,289.0 4,578 1

Unique (cost=155,897.57..155,897.62 rows=2 width=116) (actual time=917.161..919.148 rows=4,578 loops=1)

209. 2.246 917.603 ↓ 2,289.0 4,578 1

Sort (cost=155,897.57..155,897.58 rows=2 width=116) (actual time=917.160..917.603 rows=4,578 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: 836kB
210. 0.471 915.357 ↓ 2,289.0 4,578 1

Append (cost=264.58..155,897.56 rows=2 width=116) (actual time=911.115..915.357 rows=4,578 loops=1)

211. 0.725 914.872 ↓ 4,578.0 4,578 1

Subquery Scan on *SELECT* 1_2 (cost=264.58..264.61 rows=1 width=116) (actual time=911.114..914.872 rows=4,578 loops=1)

212. 2.539 914.147 ↓ 4,578.0 4,578 1

Group (cost=264.58..264.60 rows=1 width=180) (actual time=911.113..914.147 rows=4,578 loops=1)

  • Group Key: c_6.zone_id, d_8.priority, c_6.collector_id, d_8.id, d_8.ip, c_6.hash
213. 8.283 911.608 ↓ 4,578.0 4,578 1

Sort (cost=264.58..264.58 rows=1 width=108) (actual time=911.107..911.608 rows=4,578 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: 836kB
214. 7.674 903.325 ↓ 4,578.0 4,578 1

Nested Loop Left Join (cost=0.75..264.57 rows=1 width=108) (actual time=0.445..903.325 rows=4,578 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: 4578
  • Filter: (cc_3.id IS NULL)
  • Rows Removed by Filter: 763
215. 0.172 19.727 ↓ 5,341.0 5,341 1

Nested Loop (cost=0.46..7.60 rows=1 width=108) (actual time=0.039..19.727 rows=5,341 loops=1)

216. 1.742 3.532 ↓ 5,341.0 5,341 1

Hash Join (cost=0.04..3.17 rows=1 width=108) (actual time=0.029..3.532 rows=5,341 loops=1)

  • Hash Cond: (d_8.zone_id = c_6.zone_id)
217. 1.781 1.781 ↓ 7.6 763 1

CTE Scan on devs d_8 (cost=0.00..2.75 rows=100 width=72) (actual time=0.012..1.781 rows=763 loops=1)

  • Filter: (host(((ip)::cidr)::inet) !~~ '%.255'::text)
  • Rows Removed by Filter: 2
218. 0.005 0.009 ↓ 7.0 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
219. 0.004 0.004 ↓ 7.0 7 1

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

  • Filter: (hostenabled AND (collector_id IS NOT NULL) AND (scantype = 'hostDiscovery'::text))
  • Rows Removed by Filter: 7
220. 16.023 16.023 ↑ 1.0 1 5,341

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.003 rows=1 loops=5,341)

  • Index Cond: (device_id = d_8.id)
  • Filter: trusted
221. 875.924 875.924 ↑ 141.0 1 5,341

Index Scan using idx_zone_attribute_cidr_ipr on attribute_cidr cc_3 (cost=0.29..254.85 rows=141 width=19) (actual time=0.051..0.164 rows=1 loops=5,341)

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

Subquery Scan on *SELECT* 2_1 (cost=155,632.14..155,632.95 rows=1 width=116) (actual time=0.014..0.014 rows=0 loops=1)

223. 0.001 0.014 ↓ 0.0 0 1

Group (cost=155,632.14..155,632.94 rows=1 width=148) (actual time=0.014..0.014 rows=0 loops=1)

  • Group Key: c_7.zone_id, c_7.collector_id, r.id, r.route, c_7.hash
224. 0.011 0.013 ↓ 0.0 0 1

Sort (cost=155,632.14..155,632.28 rows=53 width=76) (actual time=0.013..0.013 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
225. 0.001 0.002 ↓ 0.0 0 1

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

  • Join Filter: ((iprange(cc_4.cidrval)) >>= iprange(r.route))
226. 0.001 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)
227. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on rts r (cost=0.00..0.05 rows=1 width=40) (actual time=0.000..0.000 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)))))
228. 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))
229. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Filter: (attributetype_id = system.attributetype('trusted'::text))
231. 1.510 2,697,341.420 ↓ 1,967.0 3,934 1

Unique (cost=204,100.84..204,100.90 rows=2 width=121) (actual time=2,697,339.525..2,697,341.420 rows=3,934 loops=1)

232. 2.437 2,697,339.910 ↓ 1,967.0 3,934 1

Sort (cost=204,100.84..204,100.85 rows=2 width=121) (actual time=2,697,339.525..2,697,339.910 rows=3,934 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: 581kB
233. 0.462 2,697,337.473 ↓ 1,967.0 3,934 1

Append (cost=199,134.97..204,100.83 rows=2 width=121) (actual time=2,697,332.182..2,697,337.473 rows=3,934 loops=1)

234. 1.608 2,697,334.259 ↓ 3,934.0 3,934 1

Group (cost=199,134.97..199,135.00 rows=1 width=121) (actual time=2,697,332.180..2,697,334.259 rows=3,934 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
235. 19.909 2,697,332.651 ↓ 3,934.0 3,934 1

Sort (cost=199,134.97..199,134.98 rows=1 width=121) (actual time=2,697,332.178..2,697,332.651 rows=3,934 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: 581kB
236. 1,659,875.274 2,697,312.742 ↓ 3,934.0 3,934 1

Merge Left Join (cost=165,204.21..199,134.96 rows=1 width=121) (actual time=5,704.655..2,697,312.742 rows=3,934 loops=1)

  • Merge Cond: (targets.zone_id = cc_5.zone_id)
  • Join Filter: (((cc_5.collector_id = targets.collector_id) OR (cc_5.collector_id IS NULL)) AND (iprange(cc_5.cidrval) >>= iprange((targets.cidrval)::cidr)))
  • Rows Removed by Join Filter: 9214921954
  • Filter: (cc_5.id IS NULL)
  • Rows Removed by Filter: 16526
237. 25.139 1,419.610 ↓ 11.7 20,460 1

Sort (cost=575.34..579.72 rows=1,751 width=121) (actual time=1,400.876..1,419.610 rows=20,460 loops=1)

  • Sort Key: targets.zone_id
  • Sort Method: quicksort Memory: 3193kB
238. 7.925 1,394.471 ↓ 11.7 20,460 1

Hash Join (cost=1.41..481.02 rows=1,751 width=121) (actual time=1,369.632..1,394.471 rows=20,460 loops=1)

  • Hash Cond: (targets.scantype = st_1.scantype)
239. 1,386.532 1,386.532 ↓ 1.1 20,460 1

CTE Scan on targets (cost=0.00..389.14 rows=19,457 width=149) (actual time=1,369.607..1,386.532 rows=20,460 loops=1)

240. 0.005 0.014 ↑ 1.0 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
241. 0.009 0.009 ↑ 1.0 18 1

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

242. 1,031,988.770 1,036,017.858 ↓ 65,403.2 9,214,918,021 1

Sort (cost=164,628.87..164,981.10 rows=140,894 width=19) (actual time=4,169.917..1,036,017.858 rows=9,214,918,021 loops=1)

  • Sort Key: cc_5.zone_id
  • Sort Method: quicksort Memory: 47475kB
243. 4,029.088 4,029.088 ↓ 3.2 450,388 1

Seq Scan on attribute_cidr cc_5 (cost=0.00..152,579.44 rows=140,894 width=19) (actual time=0.056..4,029.088 rows=450,388 loops=1)

  • Filter: (attributetype_id = system.attributetype('avoid'::text))
  • Rows Removed by Filter: 113187
244. 0.001 2.752 ↓ 0.0 0 1

Group (cost=4,965.78..4,965.81 rows=1 width=121) (actual time=2.752..2.752 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
245. 0.020 2.751 ↓ 0.0 0 1

Sort (cost=4,965.78..4,965.79 rows=1 width=121) (actual time=2.751..2.751 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
246. 0.001 2.731 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.70..4,965.77 rows=1 width=121) (actual time=2.731..2.731 rows=0 loops=1)

  • Filter: (cc_6.id IS NULL)
247. 0.001 2.730 ↓ 0.0 0 1

Hash Join (cost=1.41..435.78 rows=876 width=121) (actual time=2.730..2.730 rows=0 loops=1)

  • Hash Cond: (targets_1.scantype = st_2.scantype)
248. 2.729 2.729 ↓ 0.0 0 1

CTE Scan on targets targets_1 (cost=0.00..389.14 rows=9,728 width=149) (actual time=2.729..2.729 rows=0 loops=1)

  • Filter: overwrite
  • Rows Removed by Filter: 20460
249. 0.000 0.000 ↓ 0.0 0

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

250. 0.000 0.000 ↓ 0.0 0

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

251. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (iprange(cidrval) = iprange((targets_1.cidrval)::cidr))
  • Filter: (((collector_id = targets_1.collector_id) OR (collector_id IS NULL)) AND (zone_id = targets_1.zone_id) AND (attributetype_id = system.attributetype('avoid'::text)))
252.          

CTE updates

253. 0.001 20.539 ↓ 0.0 0 1

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

254. 0.000 20.538 ↓ 0.0 0 1

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

255. 1.204 1.204 ↓ 1,967.0 3,934 1

CTE Scan on targets st_3 (cost=0.00..0.04 rows=2 width=168) (actual time=0.079..1.204 rows=3,934 loops=1)

256. 19.670 19.670 ↓ 0.0 0 3,934

Index Scan using uk_target on target zt_1 (cost=0.43..4.46 rows=1 width=114) (actual time=0.005..0.005 rows=0 loops=3,934)

  • 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
257.          

CTE inserts

258. 0.004 541.660 ↓ 0.0 0 1

Insert on target (cost=9.66..9.76 rows=3 width=140) (actual time=541.660..541.660 rows=0 loops=1)

  • Conflict Resolution: NOTHING
  • Conflict Arbiter Indexes: uk_target
  • Tuples Inserted: 0
  • Conflicting Tuples: 0
259. 0.002 541.656 ↓ 0.0 0 1

Subquery Scan on *SELECT* (cost=9.66..9.76 rows=3 width=140) (actual time=541.656..541.656 rows=0 loops=1)

260. 0.004 541.654 ↓ 0.0 0 1

HashAggregate (cost=9.66..9.69 rows=3 width=88) (actual time=541.654..541.654 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
261. 0.002 541.650 ↓ 0.0 0 1

Append (cost=0.42..9.60 rows=3 width=88) (actual time=541.650..541.650 rows=0 loops=1)

262. 3.841 541.209 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.42..5.07 rows=2 width=88) (actual time=541.209..541.209 rows=0 loops=1)

263. 1.560 2.344 ↓ 1,967.0 3,934 1

Nested Loop Left Join (cost=0.00..0.06 rows=2 width=88) (actual time=0.004..2.344 rows=3,934 loops=1)

  • Join Filter: false
264. 0.784 0.784 ↓ 1,967.0 3,934 1

CTE Scan on targets t_1 (cost=0.00..0.04 rows=2 width=88) (actual time=0.000..0.784 rows=3,934 loops=1)

265. 0.000 0.000 ↓ 0.0 0 3,934

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=3,934)

  • One-Time Filter: false
266. 535.024 535.024 ↑ 34.0 1 3,934

Index Scan using idx_target_colscancidr_gist on target zt_2 (cost=0.42..71.19 rows=34 width=15) (actual time=0.136..0.136 rows=1 loops=3,934)

  • Index Cond: ((collector_id = t_1.collector_id) AND (scantype_id = t_1.scantype_id) AND (iprange(cidrval) >>= iprange((t_1.cidrval)::cidr)))
267. 0.000 0.439 ↓ 0.0 0 1

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

268. 0.000 0.439 ↓ 0.0 0 1

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

  • Join Filter: false
269. 0.439 0.439 ↓ 0.0 0 1

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

  • Filter: overwrite
  • Rows Removed by Filter: 3934
270. 0.000 0.000 ↓ 0.0 0

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

  • One-Time Filter: false
271. 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)))
272. 0.001 2,697,353.080 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.14..9.63 rows=2 width=144) (actual time=2,697,353.080..2,697,353.080 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))
273. 0.000 2,697,353.079 ↓ 0.0 0 1

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

274. 2,697,353.079 2,697,353.079 ↓ 0.0 0 1

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

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

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