explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PfYT

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 23,560.511 ↑ 1.0 10 1

GroupAggregate (cost=389,524.55..389,524.83 rows=10 width=118) (actual time=23,560.502..23,560.511 rows=10 loops=1)

  • Output: u.host__ip_addr, last(hw.name), last(hosts_base.icon), u.event__calc_severity, u.event__cnt_event, hosts_base.sn_addr
  • Group Key: u.event__calc_severity, u.event__cnt_event, u.host__ip_addr, hosts_base.sn_addr
  • Buffers: shared hit=113,373 read=30,674
  • I/O Timings: read=11,379.568
2.          

CTE hosts

3. 10.689 2,768.157 ↑ 4.0 2,469 1

Finalize HashAggregate (cost=29,876.69..30,001.09 rows=9,952 width=51) (actual time=2,766.103..2,768.157 rows=2,469 loops=1)

  • Output: t.sensor, t.ip_addr, modus(t.name)
  • Group Key: t.sensor, t.ip_addr
  • Buffers: shared hit=33,547 read=9,424
  • I/O Timings: read=2,147.443
4. 7.026 2,757.468 ↑ 3.0 9,834 1

Gather (cost=29,155.17..29,578.13 rows=29,856 width=51) (actual time=2,753.998..2,757.468 rows=9,834 loops=1)

  • Output: t.sensor, t.ip_addr, (PARTIAL modus(t.name))
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=33,547 read=9,424
  • I/O Timings: read=2,147.443
5. 93.326 2,750.442 ↑ 4.0 2,458 4 / 4

Partial HashAggregate (cost=28,155.17..28,279.57 rows=9,952 width=51) (actual time=2,748.761..2,750.442 rows=2,458 loops=4)

  • Output: t.sensor, t.ip_addr, PARTIAL modus(t.name)
  • Group Key: t.sensor, t.ip_addr
  • Buffers: shared hit=33,547 read=9,424
  • I/O Timings: read=2,147.443
  • Worker 0: actual time=2,745.873..2747.466 rows=2,454 loops=1
  • Buffers: shared hit=6,421 read=3,447
  • I/O Timings: read=972.557
  • Worker 1: actual time=2,747.689..2749.348 rows=2,467 loops=1
  • Buffers: shared hit=10,735 read=751
  • I/O Timings: read=236.712
  • Worker 2: actual time=2,748.066..2749.719 rows=2,454 loops=1
  • Buffers: shared hit=6,680 read=4,531
  • I/O Timings: read=802.871
6. 12.650 2,657.116 ↓ 5.0 159,368 4 / 4

Parallel Append (cost=0.00..27,914.38 rows=32,106 width=36) (actual time=27.152..2,657.116 rows=159,368 loops=4)

  • Buffers: shared hit=33,547 read=9,424
  • I/O Timings: read=2,147.443
  • Worker 0: actual time=48.279..2658.589 rows=143,658 loops=1
  • Buffers: shared hit=6,421 read=3,447
  • I/O Timings: read=972.557
  • Worker 1: actual time=2.018..2654.330 rows=173,905 loops=1
  • Buffers: shared hit=10,735 read=751
  • I/O Timings: read=236.712
  • Worker 2: actual time=50.553..2653.221 rows=162,279 loops=1
  • Buffers: shared hit=6,680 read=4,531
  • I/O Timings: read=802.871
7. 1,537.603 1,540.207 ↓ 86,312.0 86,312 4 / 4

Parallel Bitmap Heap Scan on dc.hosts05_mendel10_2020067 t (cost=16.03..6,534.57 rows=1 width=36) (actual time=25.971..1,540.207 rows=86,312 loops=4)

  • Output: t.sensor, t.ip_addr, t.name
  • Recheck Cond: ((t."timestamp" >= '2020-10-11 13:00:00+02'::timestamp with time zone) AND (t."timestamp" < '2020-10-12 15:00:00+02'::timestamp with time zone))
  • Rows Removed by Index Recheck: 3,284
  • Filter: ((t.sum_dat[4] + t.sum_dat[5]) > '0'::double precision)
  • Rows Removed by Filter: 1,942
  • Heap Blocks: lossy=1,287
  • Buffers: shared hit=14,864 read=8,814
  • I/O Timings: read=2,013.431
  • Worker 0: actual time=48.277..2646.458 rows=143,658 loops=1
  • Buffers: shared hit=6,421 read=3,447
  • I/O Timings: read=972.557
  • Worker 1: actual time=2.475..437.224 rows=19,834 loops=1
  • Buffers: shared hit=886 read=426
  • I/O Timings: read=125.218
  • Worker 2: actual time=50.552..2639.805 rows=162,279 loops=1
  • Buffers: shared hit=6,680 read=4,531
  • I/O Timings: read=802.871
8. 2.604 2.604 ↓ 119.6 236,800 1 / 4

Bitmap Index Scan on hosts05_mendel10_2020067_timestamp_idx (cost=0.00..16.03 rows=1,980 width=0) (actual time=10.417..10.417 rows=236,800 loops=1)

  • Index Cond: ((t."timestamp" >= '2020-10-11 13:00:00+02'::timestamp with time zone) AND (t."timestamp" < '2020-10-12 15:00:00+02'::timestamp with time zone))
  • Buffers: shared hit=26
  • Worker 2: actual time=10.417..10.417 rows=236,800 loops=1
  • Buffers: shared hit=26
9. 1,104.259 1,104.259 ↓ 4.6 146,110 2 / 4

Parallel Seq Scan on dc.hosts05_mendel10_2020068 t_1 (cost=0.00..21,219.28 rows=32,105 width=36) (actual time=4.887..2,208.518 rows=146,110 loops=2)

  • Output: t_1.sensor, t_1.ip_addr, t_1.name
  • Filter: ((t_1."timestamp" >= '2020-10-11 13:00:00+02'::timestamp with time zone) AND (t_1."timestamp" < '2020-10-12 15:00:00+02'::timestamp with time zone) AND ((t_1.sum_dat[4] + t_1.sum_dat[5]) > '0'::double precision))
  • Rows Removed by Filter: 3,187
  • Buffers: shared hit=18,683 read=610
  • I/O Timings: read=134.012
  • Worker 1: actual time=2.016..2203.662 rows=154,071 loops=1
  • Buffers: shared hit=9,849 read=325
  • I/O Timings: read=111.494
10. 0.027 23,560.494 ↑ 1.0 10 1

Sort (cost=359,523.46..359,523.48 rows=10 width=102) (actual time=23,560.493..23,560.494 rows=10 loops=1)

  • Output: u.host__ip_addr, u.event__calc_severity, u.event__cnt_event, hosts_base.sn_addr, hw.name, hosts_base.icon
  • Sort Key: u.event__calc_severity DESC, u.event__cnt_event DESC, u.host__ip_addr, hosts_base.sn_addr
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=113,373 read=30,674
  • I/O Timings: read=11,379.568
11. 0.337 23,560.467 ↑ 1.0 10 1

Hash Right Join (cost=359,249.59..359,523.29 rows=10 width=102) (actual time=23,559.947..23,560.467 rows=10 loops=1)

  • Output: u.host__ip_addr, u.event__calc_severity, u.event__cnt_event, hosts_base.sn_addr, hw.name, hosts_base.icon
  • Hash Cond: ((hw.ip_addr = u.host__ip_addr) AND ((hw.sensor)::text = (u.sensor)::text))
  • Buffers: shared hit=113,370 read=30,674
  • I/O Timings: read=11,379.568
12. 0.234 0.234 ↑ 4.0 2,469 1

CTE Scan on hosts hw (cost=0.00..199.04 rows=9,952 width=210) (actual time=0.001..0.234 rows=2,469 loops=1)

  • Output: hw.sensor, hw.ip_addr, hw.name
13. 0.008 23,559.896 ↑ 1.0 10 1

Hash (cost=359,249.44..359,249.44 rows=10 width=79) (actual time=23,559.896..23,559.896 rows=10 loops=1)

  • Output: u.host__ip_addr, u.event__calc_severity, u.event__cnt_event, u.sensor, hosts_base.icon, hosts_base.sn_addr
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=113,370 read=30,674
  • I/O Timings: read=11,379.568
14. 0.216 23,559.888 ↑ 1.0 10 1

Merge Left Join (cost=359,230.31..359,249.44 rows=10 width=79) (actual time=23,559.664..23,559.888 rows=10 loops=1)

  • Output: u.host__ip_addr, u.event__calc_severity, u.event__cnt_event, u.sensor, hosts_base.icon, hosts_base.sn_addr
  • Inner Unique: true
  • Merge Cond: ((u.host__ip_addr = hosts_base.ip_addr) AND ((u.sensor)::text = (hosts_base.sensor)::text))
  • Buffers: shared hit=113,370 read=30,674
  • I/O Timings: read=11,379.568
15. 0.008 23,556.898 ↑ 1.0 10 1

Sort (cost=358,955.34..358,955.37 rows=10 width=52) (actual time=23,556.898..23,556.898 rows=10 loops=1)

  • Output: u.host__ip_addr, u.event__calc_severity, u.event__cnt_event, u.sensor
  • Sort Key: u.host__ip_addr, u.sensor
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=113,264 read=30,674
  • I/O Timings: read=11,379.568
16. 0.005 23,556.890 ↑ 1.0 10 1

Subquery Scan on u (cost=358,955.05..358,955.18 rows=10 width=52) (actual time=23,556.886..23,556.890 rows=10 loops=1)

  • Output: u.host__ip_addr, u.event__calc_severity, u.event__cnt_event, u.sensor
  • Buffers: shared hit=113,264 read=30,674
  • I/O Timings: read=11,379.568
17. 0.002 23,556.885 ↑ 1.0 10 1

Limit (cost=358,955.05..358,955.08 rows=10 width=52) (actual time=23,556.884..23,556.885 rows=10 loops=1)

  • Output: u_1.sensor, u_1.ip_addr, (max(u_1.severity)), (sum(u_1.events))
  • Buffers: shared hit=113,264 read=30,674
  • I/O Timings: read=11,379.568
18. 0.278 23,556.883 ↑ 10.0 10 1

Sort (cost=358,955.05..358,955.30 rows=100 width=52) (actual time=23,556.882..23,556.883 rows=10 loops=1)

  • Output: u_1.sensor, u_1.ip_addr, (max(u_1.severity)), (sum(u_1.events))
  • Sort Key: (max(u_1.severity)) DESC, (sum(u_1.events)) DESC, u_1.ip_addr
  • Sort Method: top-N heapsort Memory: 26kB
  • Buffers: shared hit=113,264 read=30,674
  • I/O Timings: read=11,379.568
19. 1.015 23,556.605 ↓ 12.9 1,287 1

GroupAggregate (cost=358,950.39..358,952.89 rows=100 width=52) (actual time=23,555.507..23,556.605 rows=1,287 loops=1)

  • Output: u_1.sensor, u_1.ip_addr, max(u_1.severity), sum(u_1.events)
  • Group Key: u_1.sensor, u_1.ip_addr
  • Buffers: shared hit=113,264 read=30,674
  • I/O Timings: read=11,379.568
20. 2.269 23,555.590 ↓ 18.7 1,866 1

Sort (cost=358,950.39..358,950.64 rows=100 width=52) (actual time=23,555.492..23,555.590 rows=1,866 loops=1)

  • Output: u_1.sensor, u_1.ip_addr, u_1.severity, u_1.events
  • Sort Key: u_1.sensor, u_1.ip_addr
  • Sort Method: quicksort Memory: 194kB
  • Buffers: shared hit=113,264 read=30,674
  • I/O Timings: read=11,379.568
21. 0.240 23,553.321 ↓ 18.7 1,866 1

Append (cost=178,725.46..358,947.07 rows=100 width=52) (actual time=21,485.761..23,553.321 rows=1,866 loops=1)

  • Buffers: shared hit=113,264 read=30,674
  • I/O Timings: read=11,379.568
22. 1.203 21,490.211 ↓ 23.1 1,154 1

Hash Join (cost=178,725.46..179,472.36 rows=50 width=52) (actual time=21,485.759..21,490.211 rows=1,154 loops=1)

  • Output: u_1.sensor, u_1.ip_addr, u_1.severity, u_1.events
  • Hash Cond: (((h.sensor)::text = (u_1.sensor)::text) AND (h.ip_addr = u_1.ip_addr))
  • Buffers: shared hit=61,499 read=30,674
  • I/O Timings: read=11,379.568
23. 2,769.408 2,769.408 ↑ 4.0 2,469 1

CTE Scan on hosts h (cost=0.00..199.04 rows=9,952 width=178) (actual time=2,766.106..2,769.408 rows=2,469 loops=1)

  • Output: h.sensor, h.ip_addr, h.name
  • Buffers: shared hit=33,547 read=9,424
  • I/O Timings: read=2,147.443
24. 0.742 18,719.600 ↓ 5.8 1,154 1

Hash (cost=178,722.46..178,722.46 rows=200 width=52) (actual time=18,719.600..18,719.600 rows=1,154 loops=1)

  • Output: u_1.sensor, u_1.ip_addr, u_1.severity, u_1.events
  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 83kB
  • Buffers: shared hit=27,952 read=21,250
  • I/O Timings: read=9,232.126
25. 0.214 18,718.858 ↓ 5.8 1,154 1

Subquery Scan on u_1 (cost=178,677.57..178,722.46 rows=200 width=52) (actual time=18,649.266..18,718.858 rows=1,154 loops=1)

  • Output: u_1.sensor, u_1.ip_addr, u_1.severity, u_1.events
  • Buffers: shared hit=27,952 read=21,250
  • I/O Timings: read=9,232.126
26. 4.710 18,718.644 ↓ 5.8 1,154 1

Finalize GroupAggregate (cost=178,677.57..178,720.46 rows=200 width=84) (actual time=18,649.264..18,718.644 rows=1,154 loops=1)

  • Output: u_2.sensor, u_2.src_ip_addr, max(u_2.severity), sum(u_2.cnt), u_2.src_sn_addr
  • Group Key: u_2.sensor, u_2.src_ip_addr, u_2.src_sn_addr
  • Buffers: shared hit=27,952 read=21,250
  • I/O Timings: read=9,232.126
27. 40.538 18,713.934 ↓ 6.6 5,729 1

Gather Merge (cost=178,677.57..178,704.91 rows=870 width=84) (actual time=18,649.221..18,713.934 rows=5,729 loops=1)

  • Output: u_2.sensor, u_2.src_ip_addr, u_2.src_sn_addr, (PARTIAL max(u_2.severity)), (PARTIAL sum(u_2.cnt))
  • Workers Planned: 5
  • Workers Launched: 5
  • Buffers: shared hit=140,454 read=131,269
  • I/O Timings: read=54,225.458
28. 30.846 18,673.396 ↓ 5.5 955 6 / 6

Partial GroupAggregate (cost=177,677.50..177,682.28 rows=174 width=84) (actual time=18,630.401..18,673.396 rows=955 loops=6)

  • Output: u_2.sensor, u_2.src_ip_addr, u_2.src_sn_addr, PARTIAL max(u_2.severity), PARTIAL sum(u_2.cnt)
  • Group Key: u_2.sensor, u_2.src_ip_addr, u_2.src_sn_addr
  • Buffers: shared hit=140,454 read=131,269
  • I/O Timings: read=54,225.458
  • Worker 0: actual time=18,621.988..18659.992 rows=955 loops=1
  • Buffers: shared hit=19,147 read=23,327
  • I/O Timings: read=9,366.004
  • Worker 1: actual time=18,641.689..18679.266 rows=961 loops=1
  • Buffers: shared hit=27,562 read=20,278
  • I/O Timings: read=8,772.262
  • Worker 2: actual time=18,618.955..18656.167 rows=963 loops=1
  • Buffers: shared hit=27,410 read=20,291
  • I/O Timings: read=8,868.541
  • Worker 3: actual time=18,639.577..18682.588 rows=949 loops=1
  • Buffers: shared hit=19,325 read=23,198
  • I/O Timings: read=8,785.076
  • Worker 4: actual time=18,627.348..18670.027 rows=935 loops=1
  • Buffers: shared hit=19,058 read=22,925
  • I/O Timings: read=9,201.448
29. 131.255 18,642.550 ↓ 541.4 94,205 6 / 6

Sort (cost=177,677.50..177,677.93 rows=174 width=60) (actual time=18,630.368..18,642.550 rows=94,205 loops=6)

  • Output: u_2.sensor, u_2.src_ip_addr, u_2.src_sn_addr, u_2.severity, u_2.cnt
  • Sort Key: u_2.sensor, u_2.src_ip_addr, u_2.src_sn_addr
  • Sort Method: quicksort Memory: 11,058kB
  • Worker 0: Sort Method: quicksort Memory: 10,177kB
  • Worker 1: Sort Method: quicksort Memory: 10,947kB
  • Worker 2: Sort Method: quicksort Memory: 10,847kB
  • Worker 3: Sort Method: quicksort Memory: 10,107kB
  • Worker 4: Sort Method: quicksort Memory: 9,729kB
  • Buffers: shared hit=140,454 read=131,269
  • I/O Timings: read=54,225.458
  • Worker 0: actual time=18,621.958..18632.999 rows=90,327 loops=1
  • Buffers: shared hit=19,147 read=23,327
  • I/O Timings: read=9,366.004
  • Worker 1: actual time=18,641.659..18653.294 rows=100,170 loops=1
  • Buffers: shared hit=27,562 read=20,278
  • I/O Timings: read=8,772.262
  • Worker 2: actual time=18,618.919..18630.237 rows=98,899 loops=1
  • Buffers: shared hit=27,410 read=20,291
  • I/O Timings: read=8,868.541
  • Worker 3: actual time=18,639.545..18651.781 rows=89,567 loops=1
  • Buffers: shared hit=19,325 read=23,198
  • I/O Timings: read=8,785.076
  • Worker 4: actual time=18,627.315..18638.999 rows=84,666 loops=1
  • Buffers: shared hit=19,058 read=22,925
  • I/O Timings: read=9,201.448
30. 9.127 18,511.295 ↓ 541.4 94,205 6 / 6

Parallel Append (cost=0.00..177,671.02 rows=174 width=60) (actual time=42.973..18,511.295 rows=94,205 loops=6)

  • Buffers: shared hit=140,364 read=131,269
  • I/O Timings: read=54,225.458
  • Worker 0: actual time=98.426..18508.639 rows=90,327 loops=1
  • Buffers: shared hit=19,129 read=23,327
  • I/O Timings: read=9,366.004
  • Worker 1: actual time=15.833..18510.246 rows=100,170 loops=1
  • Buffers: shared hit=27,544 read=20,278
  • I/O Timings: read=8,772.262
  • Worker 2: actual time=15.968..18510.391 rows=98,899 loops=1
  • Buffers: shared hit=27,392 read=20,291
  • I/O Timings: read=8,868.541
  • Worker 3: actual time=61.353..18510.297 rows=89,567 loops=1
  • Buffers: shared hit=19,307 read=23,198
  • I/O Timings: read=8,785.076
  • Worker 4: actual time=62.238..18512.387 rows=84,666 loops=1
  • Buffers: shared hit=19,040 read=22,925
  • I/O Timings: read=9,201.448
31. 11,438.979 11,443.263 ↓ 48,631.0 48,631 6 / 6

Parallel Bitmap Heap Scan on di.tab05_mendel10_2020067 u_2 (cost=41.03..8,654.80 rows=1 width=60) (actual time=59.957..11,443.263 rows=48,631 loops=6)

  • Output: u_2.sensor, u_2.src_ip_addr, u_2.src_sn_addr, u_2.severity, u_2.cnt
  • Recheck Cond: ((u_2."timestamp" >= '2020-10-11 14:00:00+02'::timestamp with time zone) AND (u_2."timestamp" < '2020-10-12 14:00:00+02'::timestamp with time zone))
  • Rows Removed by Index Recheck: 2,932
  • Filter: (((u_2.src_sn_addr)::inet <> '0.0.0.0/0'::inet) AND (u_2.src_ip_addr <> '0.0.0.0'::inet) AND (u_2.sys <> 'SM'::sys) AND (u_2.severity <> 0) AND (u_2.severity <> 0) AND ((u_2.sys = ('NB'::cstring)::sys) OR (u_2.sys = ('ID'::cstring)::sys) OR (u_2.sys = ('EC'::cstring)::sys) OR (u_2.sys = ('SM'::cstring)::sys)) AND ((u_2.sys = ('NB'::cstring)::sys) OR (u_2.sys = ('ID'::cstring)::sys) OR (u_2.sys = ('EC'::cstring)::sys) OR (u_2.sys = ('SM'::cstring)::sys)))
  • Rows Removed by Filter: 373,172
  • Heap Blocks: lossy=5,369
  • Buffers: shared hit=66,002 read=76,838
  • I/O Timings: read=33,833.594
  • Worker 0: actual time=98.424..18499.552 rows=90,327 loops=1
  • Buffers: shared hit=19,129 read=23,327
  • I/O Timings: read=9,366.004
  • Worker 1: actual time=46.466..4384.854 rows=9,134 loops=1
  • Buffers: shared hit=2,800 read=2,470
  • I/O Timings: read=2,019.641
  • Worker 2: actual time=42.319..4385.007 rows=9,062 loops=1
  • Buffers: shared hit=2,891 read=2,384
  • I/O Timings: read=2,168.409
  • Worker 3: actual time=61.349..18501.459 rows=89,567 loops=1
  • Buffers: shared hit=19,307 read=23,198
  • I/O Timings: read=8,785.076
  • Worker 4: actual time=62.237..18503.725 rows=84,666 loops=1
  • Buffers: shared hit=19,040 read=22,925
  • I/O Timings: read=9,201.448
32. 4.284 4.284 ↓ 624.9 1,428,480 1 / 6

Bitmap Index Scan on tab05_mendel10_2020067_timestamp_idx (cost=0.00..41.03 rows=2,286 width=0) (actual time=25.702..25.703 rows=1,428,480 loops=1)

  • Index Cond: ((u_2."timestamp" >= '2020-10-11 14:00:00+02'::timestamp with time zone) AND (u_2."timestamp" < '2020-10-12 14:00:00+02'::timestamp with time zone))
  • Buffers: shared hit=32 read=3
  • I/O Timings: read=15.387
  • Worker 4: actual time=25.702..25.703 rows=1,428,480 loops=1
  • Buffers: shared hit=32 read=3
  • I/O Timings: read=15.387
33. 7,058.905 7,058.905 ↓ 526.9 91,148 3 / 6

Parallel Seq Scan on di.tab05_mendel10_2020068 u_3 (cost=0.00..169,015.35 rows=173 width=60) (actual time=11.939..14,117.810 rows=91,148 loops=3)

  • Output: u_3.sensor, u_3.src_ip_addr, u_3.src_sn_addr, u_3.severity, u_3.cnt
  • Filter: ((u_3."timestamp" >= '2020-10-11 14:00:00+02'::timestamp with time zone) AND (u_3."timestamp" < '2020-10-12 14:00:00+02'::timestamp with time zone) AND ((u_3.src_sn_addr)::inet <> '0.0.0.0/0'::inet) AND (u_3.src_ip_addr <> '0.0.0.0'::inet) AND (u_3.sys <> 'SM'::sys) AND (u_3.severity <> 0) AND (u_3.severity <> 0) AND ((u_3.sys = ('NB'::cstring)::sys) OR (u_3.sys = ('ID'::cstring)::sys) OR (u_3.sys = ('EC'::cstring)::sys) OR (u_3.sys = ('SM'::cstring)::sys)) AND ((u_3.sys = ('NB'::cstring)::sys) OR (u_3.sys = ('ID'::cstring)::sys) OR (u_3.sys = ('EC'::cstring)::sys) OR (u_3.sys = ('SM'::cstring)::sys)))
  • Rows Removed by Filter: 675,472
  • Buffers: shared hit=74,362 read=54,431
  • I/O Timings: read=20,391.864
  • Worker 1: actual time=15.831..14115.905 rows=91,036 loops=1
  • Buffers: shared hit=24,744 read=17,808
  • I/O Timings: read=6,752.621
  • Worker 2: actual time=15.966..14115.940 rows=89,837 loops=1
  • Buffers: shared hit=24,501 read=17,907
  • I/O Timings: read=6,700.132
34. 0.594 2,062.870 ↓ 14.2 712 1

Hash Join (cost=178,726.31..179,473.21 rows=50 width=52) (actual time=2,061.903..2,062.870 rows=712 loops=1)

  • Output: u_4.sensor, u_4.ip_addr, u_4.severity, u_4.events
  • Hash Cond: (((h_1.sensor)::text = (u_4.sensor)::text) AND (h_1.ip_addr = u_4.ip_addr))
  • Buffers: shared hit=51,765
35. 0.406 0.406 ↑ 4.0 2,469 1

CTE Scan on hosts h_1 (cost=0.00..199.04 rows=9,952 width=178) (actual time=0.001..0.406 rows=2,469 loops=1)

  • Output: h_1.sensor, h_1.ip_addr, h_1.name
36. 0.270 2,061.870 ↓ 4.2 850 1

Hash (cost=178,723.31..178,723.31 rows=200 width=52) (actual time=2,061.870..2,061.870 rows=850 loops=1)

  • Output: u_4.sensor, u_4.ip_addr, u_4.severity, u_4.events
  • Buckets: 1,024 Batches: 1 Memory Usage: 56kB
  • Buffers: shared hit=51,765
37. 0.154 2,061.600 ↓ 4.2 850 1

Subquery Scan on u_4 (cost=178,677.72..178,723.31 rows=200 width=52) (actual time=1,543.177..2,061.600 rows=850 loops=1)

  • Output: u_4.sensor, u_4.ip_addr, u_4.severity, u_4.events
  • Buffers: shared hit=51,765
38. 1.965 2,061.446 ↓ 4.2 850 1

Finalize GroupAggregate (cost=178,677.72..178,721.31 rows=200 width=84) (actual time=1,543.175..2,061.446 rows=850 loops=1)

  • Output: u_5.sensor, u_5.dst_ip_addr, max(u_5.severity), sum(u_5.cnt), u_5.dst_sn_addr
  • Group Key: u_5.sensor, u_5.dst_ip_addr, u_5.dst_sn_addr
  • Buffers: shared hit=51,765
39. 277.715 2,059.481 ↓ 5.0 4,415 1

Gather Merge (cost=178,677.72..178,705.53 rows=885 width=84) (actual time=1,543.135..2,059.481 rows=4,415 loops=1)

  • Output: u_5.sensor, u_5.dst_ip_addr, u_5.dst_sn_addr, (PARTIAL max(u_5.severity)), (PARTIAL sum(u_5.cnt))
  • Workers Planned: 5
  • Workers Launched: 5
  • Buffers: shared hit=271,719
40. 191.410 1,781.766 ↓ 4.2 736 6 / 6

Partial GroupAggregate (cost=177,677.64..177,682.51 rows=177 width=84) (actual time=1,515.359..1,781.766 rows=736 loops=6)

  • Output: u_5.sensor, u_5.dst_ip_addr, u_5.dst_sn_addr, PARTIAL max(u_5.severity), PARTIAL sum(u_5.cnt)
  • Group Key: u_5.sensor, u_5.dst_ip_addr, u_5.dst_sn_addr
  • Buffers: shared hit=271,719
  • Worker 0: actual time=1,505.536..1759.602 rows=697 loops=1
  • Buffers: shared hit=43,806
  • Worker 1: actual time=1,516.494..1788.668 rows=774 loops=1
  • Buffers: shared hit=46,265
  • Worker 2: actual time=1,513.541..1774.424 rows=776 loops=1
  • Buffers: shared hit=44,459
  • Worker 3: actual time=1,504.886..1759.752 rows=690 loops=1
  • Buffers: shared hit=43,836
  • Worker 4: actual time=1,509.632..1766.281 rows=688 loops=1
  • Buffers: shared hit=41,588
41. 423.708 1,590.356 ↓ 4,221.4 747,194 6 / 6

Sort (cost=177,677.64..177,678.09 rows=177 width=60) (actual time=1,515.315..1,590.356 rows=747,194 loops=6)

  • Output: u_5.sensor, u_5.dst_ip_addr, u_5.dst_sn_addr, u_5.severity, u_5.cnt
  • Sort Key: u_5.sensor, u_5.dst_ip_addr, u_5.dst_sn_addr
  • Sort Method: quicksort Memory: 91,343kB
  • Worker 0: Sort Method: quicksort Memory: 80,801kB
  • Worker 1: Sort Method: quicksort Memory: 84,687kB
  • Worker 2: Sort Method: quicksort Memory: 82,271kB
  • Worker 3: Sort Method: quicksort Memory: 80,851kB
  • Worker 4: Sort Method: quicksort Memory: 77,896kB
  • Buffers: shared hit=271,719
  • Worker 0: actual time=1,505.500..1577.188 rows=719,396 loops=1
  • Buffers: shared hit=43,806
  • Worker 1: actual time=1,516.450..1593.425 rows=769,072 loops=1
  • Buffers: shared hit=46,265
  • Worker 2: actual time=1,513.493..1587.109 rows=738,183 loops=1
  • Buffers: shared hit=44,459
  • Worker 3: actual time=1,504.837..1576.938 rows=720,011 loops=1
  • Buffers: shared hit=43,836
  • Worker 4: actual time=1,509.586..1580.638 rows=682,223 loops=1
  • Buffers: shared hit=41,588
42. 49.665 1,166.648 ↓ 4,221.4 747,194 6 / 6

Parallel Append (cost=0.00..177,671.03 rows=177 width=60) (actual time=3.560..1,166.648 rows=747,194 loops=6)

  • Buffers: shared hit=271,629
  • Worker 0: actual time=6.955..1173.638 rows=719,396 loops=1
  • Buffers: shared hit=43,788
  • Worker 1: actual time=0.093..1163.991 rows=769,072 loops=1
  • Buffers: shared hit=46,247
  • Worker 2: actual time=0.092..1162.882 rows=738,183 loops=1
  • Buffers: shared hit=44,441
  • Worker 3: actual time=6.989..1174.277 rows=720,011 loops=1
  • Buffers: shared hit=43,818
  • Worker 4: actual time=7.182..1170.368 rows=682,223 loops=1
  • Buffers: shared hit=41,570
43. 619.686 620.670 ↓ 392,155.0 392,155 6 / 6

Parallel Bitmap Heap Scan on di.tab05_mendel10_2020067 u_5 (cost=41.03..8,654.80 rows=1 width=60) (actual time=3.555..620.670 rows=392,155 loops=6)

  • Output: u_5.sensor, u_5.dst_ip_addr, u_5.dst_sn_addr, u_5.severity, u_5.cnt
  • Recheck Cond: ((u_5."timestamp" >= '2020-10-11 14:00:00+02'::timestamp with time zone) AND (u_5."timestamp" < '2020-10-12 14:00:00+02'::timestamp with time zone))
  • Rows Removed by Index Recheck: 2,932
  • Filter: (((u_5.dst_sn_addr)::inet <> '0.0.0.0/0'::inet) AND (u_5.dst_ip_addr <> '0.0.0.0'::inet) AND (u_5.sys <> 'SM'::sys) AND (u_5.severity <> 0) AND (u_5.severity <> 0) AND ((u_5.sys = ('NB'::cstring)::sys) OR (u_5.sys = ('ID'::cstring)::sys) OR (u_5.sys = ('EC'::cstring)::sys) OR (u_5.sys = ('SM'::cstring)::sys)) AND ((u_5.sys = ('NB'::cstring)::sys) OR (u_5.sys = ('ID'::cstring)::sys) OR (u_5.sys = ('EC'::cstring)::sys) OR (u_5.sys = ('SM'::cstring)::sys)))
  • Rows Removed by Filter: 29,648
  • Heap Blocks: lossy=4,699
  • Buffers: shared hit=142,838
  • Worker 0: actual time=6.953..1127.032 rows=719,396 loops=1
  • Buffers: shared hit=43,788
  • Worker 1: actual time=0.066..116.275 rows=76,079 loops=1
  • Buffers: shared hit=4,491
  • Worker 2: actual time=0.076..116.290 rows=75,715 loops=1
  • Buffers: shared hit=4,472
  • Worker 3: actual time=6.987..1127.541 rows=720,011 loops=1
  • Buffers: shared hit=43,818
  • Worker 4: actual time=7.181..1122.515 rows=682,223 loops=1
  • Buffers: shared hit=41,570
44. 0.984 0.984 ↓ 624.9 1,428,480 1 / 6

Bitmap Index Scan on tab05_mendel10_2020067_timestamp_idx (cost=0.00..41.03 rows=2,286 width=0) (actual time=5.905..5.905 rows=1,428,480 loops=1)

  • Index Cond: ((u_5."timestamp" >= '2020-10-11 14:00:00+02'::timestamp with time zone) AND (u_5."timestamp" < '2020-10-12 14:00:00+02'::timestamp with time zone))
  • Buffers: shared hit=35
  • Worker 4: actual time=5.905..5.905 rows=1,428,480 loops=1
  • Buffers: shared hit=35
45. 496.313 496.313 ↓ 4,034.5 710,078 3 / 6

Parallel Seq Scan on di.tab05_mendel10_2020068 u_6 (cost=0.00..169,015.35 rows=176 width=60) (actual time=0.076..992.626 rows=710,078 loops=3)

  • Output: u_6.sensor, u_6.dst_ip_addr, u_6.dst_sn_addr, u_6.severity, u_6.cnt
  • Filter: ((u_6."timestamp" >= '2020-10-11 14:00:00+02'::timestamp with time zone) AND (u_6."timestamp" < '2020-10-12 14:00:00+02'::timestamp with time zone) AND ((u_6.dst_sn_addr)::inet <> '0.0.0.0/0'::inet) AND (u_6.dst_ip_addr <> '0.0.0.0'::inet) AND (u_6.sys <> 'SM'::sys) AND (u_6.severity <> 0) AND (u_6.severity <> 0) AND ((u_6.sys = ('NB'::cstring)::sys) OR (u_6.sys = ('ID'::cstring)::sys) OR (u_6.sys = ('EC'::cstring)::sys) OR (u_6.sys = ('SM'::cstring)::sys)) AND ((u_6.sys = ('NB'::cstring)::sys) OR (u_6.sys = ('ID'::cstring)::sys) OR (u_6.sys = ('EC'::cstring)::sys) OR (u_6.sys = ('SM'::cstring)::sys)))
  • Rows Removed by Filter: 56,542
  • Buffers: shared hit=128,791
  • Worker 1: actual time=0.091..997.973 rows=692,993 loops=1
  • Buffers: shared hit=41,756
  • Worker 2: actual time=0.089..996.630 rows=662,468 loops=1
  • Buffers: shared hit=39,969
46. 1.685 2.774 ↑ 1.5 1,649 1

Sort (cost=274.97..281.32 rows=2,539 width=55) (actual time=2.691..2.774 rows=1,649 loops=1)

  • Output: hosts_base.icon, hosts_base.sn_addr, hosts_base.policy_id, hosts_base.host_id, hosts_base.ip_addr, hosts_base.sensor
  • Sort Key: hosts_base.ip_addr, hosts_base.sensor
  • Sort Method: quicksort Memory: 454kB
  • Buffers: shared hit=106
47. 1.089 1.089 ↓ 1.0 2,540 1

Seq Scan on ti.hosts_base (cost=0.00..131.39 rows=2,539 width=55) (actual time=0.026..1.089 rows=2,540 loops=1)

  • Output: hosts_base.icon, hosts_base.sn_addr, hosts_base.policy_id, hosts_base.host_id, hosts_base.ip_addr, hosts_base.sensor
  • Buffers: shared hit=106