explain.depesz.com

PostgreSQL's explain analyze made readable

Result: apPM

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 3,050.017 ↓ 0.0 0 1

Insert on target_highpriority (cost=368,056.59..368,066.08 rows=2 width=144) (actual time=3,050.017..3,050.017 rows=0 loops=1)

2.          

CTE attype

3. 0.006 0.006 ↑ 1.0 1 1

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

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

CTE attype_trusted

5. 0.024 0.024 ↑ 1.0 1 1

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

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

CTE attype_target

7. 0.008 0.008 ↑ 1.0 1 1

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

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

CTE targets

9. 123.182 3,048.232 ↓ 6,841.0 13,682 1

Sort (cost=368,033.90..368,033.90 rows=2 width=121) (actual time=2,954.673..3,048.232 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.036 5.529 ↓ 42.0 42 1

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

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

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

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

Sort (cost=403.21..403.46 rows=98 width=70) (actual time=4.945..4.949 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. 4.619 4.856 ↑ 2.3 42 1

Nested Loop (cost=0.00..399.97 rows=98 width=70) (actual time=0.812..4.856 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.040 0.040 ↑ 1.0 197 1

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

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

17. 0.015 0.015 ↑ 16.7 6 1

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

18. 0.017 0.352 ↑ 1.0 17 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.203 0.335 ↑ 1.0 17 1

HashAggregate (cost=10.88..11.05 rows=17 width=19) (actual time=0.318..0.335 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.132 0.132 ↑ 1.2 34 1

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

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

CTE devs

22. 5.387 45.607 ↓ 86.7 8,668 1

Nested Loop (cost=0.43..446.25 rows=100 width=51) (actual time=13.022..45.607 rows=8,668 loops=1)

23. 14.210 14.210 ↓ 86.7 8,670 1

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

24. 26.010 26.010 ↑ 1.0 1 8,670

Index Scan using pk_device on device d (cost=0.42..4.44 rows=1 width=15) (actual time=0.003..0.003 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. 139.156 624.492 ↓ 4.4 70,869 1

HashAggregate (cost=341,278.59..341,440.61 rows=16,202 width=149) (actual time=598.604..624.492 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.298 485.336 ↓ 14.2 230,053 1

Append (cost=2,430.10..340,873.54 rows=16,202 width=149) (actual time=222.263..485.336 rows=230,053 loops=1)

30. 37.144 262.814 ↓ 228,528.0 228,528 1

Hash Join (cost=2,430.10..2,445.55 rows=1 width=149) (actual time=222.262..262.814 rows=228,528 loops=1)

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

HashAggregate (cost=2,003.07..2,009.07 rows=600 width=81) (actual time=213.601..217.044 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. 6.426 194.715 ↓ 70.6 42,360 1

Nested Loop Left Join (cost=1.28..1,994.07 rows=600 width=77) (actual time=13.085..194.715 rows=42,360 loops=1)

33. 1.971 101.609 ↓ 94.2 8,668 1

Nested Loop (cost=0.85..492.05 rows=92 width=73) (actual time=13.051..101.609 rows=8,668 loops=1)

34. 0.000 64.966 ↓ 94.2 8,668 1

Nested Loop (cost=0.42..447.25 rows=92 width=76) (actual time=13.028..64.966 rows=8,668 loops=1)

35. 48.431 48.431 ↓ 86.7 8,668 1

CTE Scan on devs d_1 (cost=0.00..2.00 rows=100 width=72) (actual time=13.023..48.431 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.42..4.44 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=8,668)

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
40. 0.007 8.615 ↑ 2.0 16 1

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

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

Sort (cost=425.90..425.98 rows=32 width=72) (actual time=8.606..8.608 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.031 8.575 ↑ 2.0 16 1

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

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

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

44. 0.004 1.041 ↑ 7.0 2 1

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

45. 0.074 1.037 ↑ 7.0 2 1

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

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

Sort (cost=43.47..43.51 rows=14 width=40) (actual time=0.962..0.963 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.011 0.918 ↑ 7.0 2 1

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

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

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

  • Group Key: sa.zone_id, sa.collector_id
49. 0.075 0.468 ↑ 4.3 60 1

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

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

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

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

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

52. 0.090 0.108 ↓ 1.1 60 1

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

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

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

54. 0.053 0.127 ↑ 3.8 52 1

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

55. 0.014 0.014 ↑ 1.0 1 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
58. 0.001 0.206 ↑ 7.0 2 1

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

59. 0.080 0.205 ↑ 7.0 2 1

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Group Key: cpe.zone_id, cpe.collector_id
66. 0.125 1.099 ↑ 31.7 126 1

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

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

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

  • Hash Cond: ((cpe.key = (regexp_split_to_array(unnest('{profileDiscovery.collectHTTP:profileDiscovery.httpPorts,profileDiscovery.collectHTTP
68. 0.081 0.081 ↑ 1.0 104 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
70. 0.099 0.349 ↑ 3.1 21 1

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

  • Hash Cond: (cpp.key = (regexp_split_to_array(unnest('{profileDiscovery.collectHTTP:profileDiscovery.httpPorts,profileDiscovery.
71. 0.100 0.100 ↓ 2.8 183 1

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

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

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

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

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

74. 0.007 0.242 ↑ 2.1 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
75. 0.060 0.235 ↑ 2.1 7 1

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

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

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

77. 0.008 0.161 ↑ 2.1 7 1

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

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

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

79. 0.072 0.148 ↑ 2.1 7 1

HashAggregate (cost=5.45..5.60 rows=15 width=11) (actual time=0.142..0.148 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 = 'ena
  • Rows Removed by Filter: 12
80. 0.076 0.076 ↑ 1.0 27 1

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

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

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

82. 0.063 0.063 ↓ 0.0 0 1

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

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

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

84. 0.056 0.056 ↓ 1.8 7 1

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

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

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

86. 0.048 0.048 ↓ 1.8 7 1

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

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

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

  • Filter: dnsenabled
  • Rows Removed by Filter: 35
88. 0.006 0.006 ↓ 0.0 0 1

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

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

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

  • Filter: (outboundleak AND leakenabled AND (scantype = 'leakDiscovery'::text))
  • Rows Removed by Filter: 42
90. 0.658 74.139 ↓ 1,197.0 1,197 1

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

91. 0.830 73.481 ↓ 1,197.0 1,197 1

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

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

Sort (cost=460.77..460.78 rows=1 width=140) (actual time=72.445..72.651 rows=2,394 loops=1)

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

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

94. 24.561 69.043 ↓ 2,394.0 2,394 1

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

  • Join Filter: (d_2.id = dv_1.device_id)
95. 10.267 39.694 ↓ 2,394.0 2,394 1

Hash Join (cost=17.28..459.73 rows=1 width=174) (actual time=0.441..39.694 rows=2,394 loops=1)

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

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

97. 1.531 1.531 ↓ 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.531 rows=8,668 loops=1)

98. 26.004 26.004 ↑ 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.003..0.003 rows=1 loops=8,668)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
100. 0.001 0.283 ↑ 2.1 7 1

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

101. 0.070 0.282 ↑ 2.1 7 1

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

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

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

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

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
106. 0.002 0.063 ↑ 2.1 7 1

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

107. 0.024 0.061 ↑ 2.1 7 1

HashAggregate (cost=5.45..5.60 rows=15 width=11) (actual time=0.059..0.061 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 = 'en
  • Rows Removed by Filter: 12
108. 0.037 0.037 ↑ 1.0 27 1

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

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

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

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

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

  • Filter: ((value ~ '^[0-9,]+$'::text) AND (key = 'profileDiscovery.httpsPorts'::text))
  • Rows Removed by Filter: 190
111. 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)
112. 0.089 66.462 ↑ 73.7 218 1

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

113. 1.276 66.373 ↑ 73.7 218 1

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

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

Initplan (forHashAggregate)

115. 0.011 0.011 ↑ 1.0 1 1

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

116. 0.030 0.030 ↑ 1.0 1 1

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

117. 0.274 65.056 ↑ 36.8 436 1

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

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

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

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

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

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

GroupAggregate (cost=5.35..6.31 rows=15 width=11) (actual time=0.058..0.087 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)))
  • Rows Removed by Filter: 12
121. 0.015 0.050 ↑ 1.0 27 1

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

  • Sort Key: cfg_4.collector_id, cfg_4.zone_id
  • Sort Method: quicksort Memory: 27kB
122. 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.015..0.035 rows=27 loops=1)

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

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

124. 0.000 27.636 ↓ 27.2 436 1

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

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

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

126. 0.008 0.008 ↑ 1.0 1 1

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

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

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

128. 1.144 1.144 ↓ 86.7 8,668 1

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

129. 17.336 17.336 ↑ 1.0 1 8,668

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

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

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

  • Index Cond: (device_id = dr_1.device_id)
  • Filter: (open && '{445,139}'::integer[])
  • Rows Removed by Filter: 1
131. 17.876 17.876 ↑ 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.033..0.041 rows=1 loops=436)

  • Index Cond: (iprange(cidrval) >>= iprange((d_3.ip)::cidr))
  • Filter: (attributetype_id = $7)
  • Rows Removed by Filter: 16
132. 17.876 17.876 ↑ 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.027..0.041 rows=1 loops=436)

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

Subquery Scan on *SELECT* 4 (cost=1,307.30..1,307.35 rows=1 width=149) (actual time=61.430..61.500 rows=110 loops=1)

134. 0.043 61.477 ↓ 110.0 110 1

Group (cost=1,307.30..1,307.34 rows=1 width=149) (actual time=61.429..61.477 rows=110 loops=1)

  • Group Key: cfg_5.zone_id, cfg_5.collector_id, d_4.id, (COALESCE((((min(((COALESCE(dl.ip, d_4.ip)))::text)))::cidr)::inet, d_4.ip)), 'snmpDetails'::text, ((((((first_value(sa_1.alias) OVER (?)) || '|'::text
135. 0.070 61.434 ↓ 128.0 128 1

Sort (cost=1,307.30..1,307.31 rows=1 width=109) (actual time=61.427..61.434 rows=128 loops=1)

  • Sort Key: cfg_5.zone_id, cfg_5.collector_id, d_4.id, (COALESCE((((min(((COALESCE(dl.ip, d_4.ip)))::text)))::cidr)::inet, d_4.ip)), ((((((first_value(sa_1.alias) OVER (?)) || '|'::text) || array_to_st
  • Sort Method: quicksort Memory: 45kB
136. 0.224 61.364 ↓ 128.0 128 1

Hash Join (cost=1,306.85..1,307.29 rows=1 width=109) (actual time=61.164..61.364 rows=128 loops=1)

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

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

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

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

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

Hash (cost=1,301.09..1,301.09 rows=1 width=148) (actual time=60.671..60.671 rows=512 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 84kB
140. 0.095 60.542 ↓ 512.0 512 1

Merge Join (cost=1,300.26..1,301.09 rows=1 width=148) (actual time=60.348..60.542 rows=512 loops=1)

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

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

  • Group Key: config.zone_id, config.collector_id
142. 0.020 0.295 ↑ 1.0 25 1

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

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

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

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

Materialize (cost=1,295.22..1,295.24 rows=1 width=108) (actual time=60.018..60.075 rows=509 loops=1)

145. 0.149 60.032 ↓ 128.0 128 1

Sort (cost=1,295.22..1,295.22 rows=1 width=131) (actual time=60.016..60.032 rows=128 loops=1)

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

WindowAgg (cost=1,295.18..1,295.21 rows=1 width=131) (actual time=59.758..59.883 rows=128 loops=1)

147. 0.098 59.769 ↓ 128.0 128 1

Sort (cost=1,295.18..1,295.18 rows=1 width=99) (actual time=59.746..59.769 rows=128 loops=1)

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

GroupAggregate (cost=1,295.13..1,295.17 rows=1 width=99) (actual time=58.929..59.671 rows=128 loops=1)

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

Sort (cost=1,295.13..1,295.13 rows=1 width=67) (actual time=58.858..58.904 rows=532 loops=1)

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

Nested Loop (cost=974.53..1,295.12 rows=1 width=67) (actual time=36.716..58.456 rows=532 loops=1)

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

Nested Loop Left Join (cost=974.39..1,294.27 rows=5 width=64) (actual time=36.712..57.513 rows=562 loops=1)

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

Nested Loop (cost=974.25..1,293.42 rows=5 width=56) (actual time=36.706..56.564 rows=562 loops=1)

  • -> Index Scan using idx_device_response_devst on device_response dr_2 (cost=0.68..0.80 rows=1 width=8) (actual time=0.005..0.
153. 1.112 51.836 ↓ 14.0 266 1

Nested Loop (cost=973.57..1,277.95 rows=19 width=64) (actual time=36.602..51.836 rows=266 loops=1)

  • Join Filter: ((COALESCE(dl.id, d_4.id)) = dv_2.device_id)
  • -> Index Scan using idx_device_values_devid on device_values dv_2 (cost=0.42..0.61 rows=1 width=5) (actual time=0.003..
  • Index Cond: ((scantype_id = system.scantype_ro('snmpDiscovery'::text)) AND (device_id = dv_2.device_id))
154. 11.265 50.724 ↓ 2.4 266 1

Nested Loop (cost=973.15..1,207.48 rows=113 width=59) (actual time=36.593..50.724 rows=266 loops=1)

  • -> Index Only Scan using pk_device_alias on device_snmpalias dsa (cost=0.42..2.32 rows=1 width=8) (actual time=0.
  • Index Cond: (device_id = dsa.device_id)
  • Filter: (target OR trusted)
155. 2.250 39.459 ↓ 44.3 4,429 1

Group (cost=972.73..973.73 rows=100 width=62) (actual time=36.520..39.459 rows=4,429 loops=1)

  • Group Key: ids.zone_id, d_4.id, dl.id
  • Index Cond: (device_id = (COALESCE(dl.id, d_4.id)))
  • Heap Fetches: 190
156. 37.209 37.209 ↓ 44.3 4,429 1

Sort (cost=972.73..972.98 rows=100 width=26) (actual time=36.518..37.209 rows=4,429 loops=1)

  • Sort Key: ids.zone_id, d_4.id, dl.id
  • Sort Method: quicksort Memory: 412kB
  • -> Nested Loop Left Join (cost=451.42..969.40 rows=100 width=26) (actual time=20.497..35.382 rows=442
  • -> Nested Loop (cost=451.00..897.57 rows=100 width=15) (actual time=20.481..29.809 rows=4193 lo
  • -> Group (cost=450.57..451.32 rows=100 width=8) (actual time=20.475..22.603 rows=4193 loo
  • Group Key: ids.zone_id, (COALESCE(d_5.device_id, d_5.id))
  • -> Sort (cost=450.57..450.82 rows=100 width=8) (actual time=20.474..21.408 rows=866
  • Sort Key: ids.zone_id, (COALESCE(d_5.device_id, d_5.id))
  • Sort Method: quicksort Memory: 791kB
  • -> Nested Loop (cost=0.42..447.25 rows=100 width=8) (actual time=0.012..17.16
  • -> CTE Scan on devs ids (cost=0.00..2.00 rows=100 width=8) (actual time
  • -> Index Scan using pk_device on device d_5 (cost=0.42..4.44 rows=1 wid
  • Index Cond: (id = ids.id)
  • -> Index Scan using pk_device on device d_4 (cost=0.42..4.44 rows=1 width=11) (actual tim
  • Index Cond: (id = (COALESCE(d_5.device_id, d_5.id)))
  • -> Index Scan using idx_zone_device_devid on device dl (cost=0.42..0.63 rows=9 width=15) (actua
  • Index Cond: (device_id = d_4.id)
157. 0.562 0.562 ↑ 1.0 1 562

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=562)

  • Index Cond: (id = dsa.snmpalias_id)
158. 0.562 0.562 ↑ 1.0 1 562

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

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

Subquery Scan on *SELECT* 5 (cost=160,339.25..160,345.66 rows=135 width=149) (actual time=0.068..0.068 rows=0 loops=1)

160. 0.001 0.068 ↓ 0.0 0 1

GroupAggregate (cost=160,339.25..160,344.31 rows=135 width=149) (actual time=0.068..0.068 rows=0 loops=1)

  • Group Key: pd.zone_id, pd.collector_id, pd.cidrval, pd.priority, pd.overwrite
161. 0.010 0.067 ↓ 0.0 0 1

Sort (cost=160,339.25..160,339.58 rows=135 width=117) (actual time=0.067..0.067 rows=0 loops=1)

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

Subquery Scan on pd (cost=160,331.77..160,334.47 rows=135 width=117) (actual time=0.057..0.057 rows=0 loops=1)

163. 0.001 0.056 ↓ 0.0 0 1

HashAggregate (cost=160,331.77..160,333.12 rows=135 width=117) (actual time=0.056..0.056 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
164. 0.001 0.055 ↓ 0.0 0 1

Append (cost=27.11..160,328.73 rows=135 width=117) (actual time=0.055..0.055 rows=0 loops=1)

165. 0.001 0.024 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=27.11..27.59 rows=9 width=117) (actual time=0.024..0.024 rows=0 loops=1)

166. 0.001 0.023 ↓ 0.0 0 1

GroupAggregate (cost=27.11..27.50 rows=9 width=149) (actual time=0.023..0.023 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)
167. 0.007 0.022 ↓ 0.0 0 1

Sort (cost=27.11..27.14 rows=9 width=115) (actual time=0.022..0.022 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
168. 0.001 0.015 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.88..26.97 rows=9 width=115) (actual time=0.015..0.015 rows=0 loops=1)

169. 0.000 0.014 ↓ 0.0 0 1

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

170. 0.007 0.014 ↓ 0.0 0 1

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

  • Hash Cond: (d_6.zone_id = c_3.zone_id)
171. 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)

172. 0.000 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
173. 0.006 0.006 ↓ 0.0 0 1

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

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

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

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

Subquery Scan on *SELECT* 2 (cost=4,549.64..4,556.20 rows=125 width=117) (actual time=0.020..0.020 rows=0 loops=1)

177. 0.001 0.019 ↓ 0.0 0 1

GroupAggregate (cost=4,549.64..4,554.95 rows=125 width=149) (actual time=0.019..0.019 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)
178. 0.006 0.018 ↓ 0.0 0 1

Sort (cost=4,549.64..4,549.95 rows=125 width=115) (actual time=0.018..0.018 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
179. 0.001 0.012 ↓ 0.0 0 1

Nested Loop (cost=89.84..4,545.28 rows=125 width=115) (actual time=0.012..0.012 rows=0 loops=1)

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

Nested Loop Left Join (cost=0.88..26.97 rows=9 width=119) (actual time=0.011..0.011 rows=0 loops=1)

181. 0.001 0.011 ↓ 0.0 0 1

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

182. 0.005 0.010 ↓ 0.0 0 1

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

  • Hash Cond: (d_7.zone_id = c_4.zone_id)
183. 0.001 0.001 ↑ 100.0 1 1

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

184. 0.000 0.004 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

190. 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))
191. 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))
192. 0.000 0.010 ↓ 0.0 0 1

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

193. 0.002 0.010 ↓ 0.0 0 1

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

  • Group Key: c_5.zone_id, c_5.collector_id, rt.id, rt.route, c_5.hash
194. 0.006 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
195. 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_2.cidrval)) >>= iprange(rt.route))
196. 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: (rt.zone_id = c_5.zone_id)
197. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on rts rt (cost=0.00..0.05 rows=1 width=40) (actual time=0.001..0.001 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 >>= (ro
198. 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))
199. 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
200. 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))
201. 0.000 0.055 ↓ 0.0 0 1

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

202. 0.000 0.055 ↓ 0.0 0 1

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

  • Group Key: hd.zone_id, hd.collector_id, hd.cidrval, hd.priority
203. 0.007 0.055 ↓ 0.0 0 1

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

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

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

205. 0.000 0.048 ↓ 0.0 0 1

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

206. 0.008 0.048 ↓ 0.0 0 1

Sort (cost=156,011.81..156,011.82 rows=2 width=116) (actual time=0.048..0.048 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::integer), "*SELECT* 1_2".cidrval, "*SELECT*
  • Sort Method: quicksort Memory: 25kB
207. 0.001 0.040 ↓ 0.0 0 1

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

208. 0.000 0.030 ↓ 0.0 0 1

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

209. 0.001 0.030 ↓ 0.0 0 1

Group (cost=266.82..266.84 rows=1 width=180) (actual time=0.030..0.030 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
210. 0.007 0.029 ↓ 0.0 0 1

Sort (cost=266.82..266.82 rows=1 width=108) (actual time=0.029..0.029 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
211. 0.000 0.022 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.75..266.81 rows=1 width=108) (actual time=0.022..0.022 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)
212. 0.001 0.022 ↓ 0.0 0 1

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

213. 0.005 0.021 ↓ 0.0 0 1

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

  • Hash Cond: (d_8.zone_id = c_6.zone_id)
214. 0.008 0.008 ↑ 100.0 1 1

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

  • Filter: (host(((ip)::cidr)::inet) !~~ '%.255'::text)
215. 0.001 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
216. 0.007 0.007 ↓ 0.0 0 1

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

  • Filter: (hostenabled AND (collector_id IS NOT NULL) AND (scantype = 'hostDiscovery'::text))
  • Rows Removed by Filter: 42
217. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (iprange(cidrval) >>= iprange((d_8.ip)::cidr))
  • Filter: (attributetype_id = system.attributetype('target'::text))
219. 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)

220. 0.000 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
221. 0.008 0.009 ↓ 0.0 0 1

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

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

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

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

  • Join Filter: (r.zone_id = c_7.zone_id)
224. 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):
225. 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))
226. 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
227. 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))
228. 4.049 2,925.050 ↓ 6,841.0 13,682 1

Unique (cost=25,729.42..25,729.48 rows=2 width=121) (actual time=2,919.927..2,925.050 rows=13,682 loops=1)

229. 5.139 2,921.001 ↓ 6,841.0 13,682 1

Sort (cost=25,729.42..25,729.43 rows=2 width=121) (actual time=2,919.925..2,921.001 rows=13,682 loops=1)

  • Sort Key: targets.zone_id, targets.priority, targets.collector_id, targets.device_id, targets.route_id, targets.cidr_id, targets.cidrval, st_1.id, targets.config, targets.overwrite
  • Sort Method: quicksort Memory: 2153kB
230. 1.176 2,915.862 ↓ 6,841.0 13,682 1

Append (cost=23,337.83..25,729.41 rows=2 width=121) (actual time=2,902.715..2,915.862 rows=13,682 loops=1)

231. 4.384 2,908.431 ↓ 13,682.0 13,682 1

Group (cost=23,337.83..23,337.86 rows=1 width=121) (actual time=2,902.713..2,908.431 rows=13,682 loops=1)

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

Initplan (forGroup)

233. 0.007 0.007 ↑ 1.0 1 1

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

234. 19.737 2,904.040 ↓ 13,682.0 13,682 1

Sort (cost=23,337.81..23,337.82 rows=1 width=121) (actual time=2,902.711..2,904.040 rows=13,682 loops=1)

  • Sort Key: targets.zone_id, targets.priority, targets.collector_id, targets.device_id, targets.route_id, targets.cidr_id, targets.cidrval, st_1.id, targets.config, targets.overwrite
  • Sort Method: quicksort Memory: 2153kB
235. 9.595 2,884.303 ↓ 13,682.0 13,682 1

Nested Loop Left Join (cost=1.70..23,337.80 rows=1 width=121) (actual time=599.148..2,884.303 rows=13,682 loops=1)

  • Filter: (cc_5.id IS NULL)
  • Rows Removed by Filter: 57187
236. 23.679 677.769 ↓ 48.6 70,869 1

Hash Join (cost=1.41..400.78 rows=1,458 width=121) (actual time=598.628..677.769 rows=70,869 loops=1)

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

CTE Scan on targets (cost=0.00..324.04 rows=16,202 width=149) (actual time=598.607..654.079 rows=70,869 loops=1)

238. 0.007 0.011 ↑ 1.0 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
239. 0.004 0.004 ↑ 1.0 18 1

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

240. 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
241. 0.001 6.255 ↓ 0.0 0 1

Group (cost=2,391.50..2,391.53 rows=1 width=121) (actual time=6.255..6.255 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
242.          

Initplan (forGroup)

243. 0.000 0.000 ↓ 0.0 0

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

244. 0.016 6.254 ↓ 0.0 0 1

Sort (cost=2,391.48..2,391.49 rows=1 width=121) (actual time=6.254..6.254 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
245. 0.000 6.238 ↓ 0.0 0 1

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

  • Filter: (cc_6.id IS NULL)
246. 0.002 6.238 ↓ 0.0 0 1

Hash Join (cost=1.41..363.11 rows=729 width=121) (actual time=6.238..6.238 rows=0 loops=1)

  • Hash Cond: (targets_1.scantype = st_2.scantype)
247. 6.236 6.236 ↓ 0.0 0 1

CTE Scan on targets targets_1 (cost=0.00..324.04 rows=8,101 width=149) (actual time=6.236..6.236 rows=0 loops=1)

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

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

249. 0.000 0.000 ↓ 0.0 0

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

250. 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))
251.          

CTE updates

252. 0.000 44.574 ↓ 0.0 0 1

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

253. 0.050 44.574 ↓ 0.0 0 1

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

254. 3.478 3.478 ↓ 6,841.0 13,682 1

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

255. 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
256.          

CTE inserts

257. 0.002 1,604.867 ↓ 0.0 0 1

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

  • Conflict Resolution: NOTHING
  • Conflict Arbiter Indexes: uk_target
  • Tuples Inserted: 0
  • Conflicting Tuples: 0
258. 0.000 1,604.865 ↓ 0.0 0 1

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

259. 0.003 1,604.865 ↓ 0.0 0 1

HashAggregate (cost=9.65..9.68 rows=3 width=88) (actual time=1,604.865..1,604.865 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
260. 0.001 1,604.862 ↓ 0.0 0 1

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

261. 10.490 1,603.805 ↓ 0.0 0 1

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

262. 4.015 6.203 ↓ 6,841.0 13,682 1

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

  • Join Filter: false
263. 2.188 2.188 ↓ 6,841.0 13,682 1

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

264. 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
265. 1,587.112 1,587.112 ↑ 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.116..0.116 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)))
266. 0.000 1.056 ↓ 0.0 0 1

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

267. 0.001 1.056 ↓ 0.0 0 1

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

  • Join Filter: false
268. 1.055 1.055 ↓ 0.0 0 1

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

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

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

  • One-Time Filter: false
270. 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)))
271. 0.003 3,050.010 ↓ 0.0 0 1

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

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

273. 3,050.007 3,050.007 ↓ 0.0 0 1

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

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

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