explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 44DB

Settings
# exclusive inclusive rows x rows loops node
1. 0.069 65,562.507 ↑ 1.0 11 1

GroupAggregate (cost=134,493.48..134,563.69 rows=11 width=292) (actual time=65,562.449..65,562.507 rows=11 loops=1)

  • Output: "*SELECT* 1".ip_addr, (last("*SELECT* 1".flag_icon)), (max("*SELECT* 1".ip_rep)), (sum("*SELECT* 1".flow)), (sum("*SELECT* 1".pkt_in)), (sum("*SELECT* 1".pkt_out)), (sum("*SELECT* 1".dat_in)), (sum("*SELECT* 1".dat_out)), COALESCE(last(hosts_base.icon), (CASE WHEN (((last("*SELECT* 1".flag_icon)) IS NOT NULL) AND (char_length((last("*SELECT* 1".flag_icon))) = 2)) THEN concat('flags/', (last("*SELECT* 1".flag_icon)), '.png') ELSE 'icons/.png'::text END)::character varying), last(hosts_base.name)
  • Group Key: (sum("*SELECT* 1".dat_in)), "*SELECT* 1".ip_addr, (last("*SELECT* 1".flag_icon)), (max("*SELECT* 1".ip_rep)), (sum("*SELECT* 1".flow)), (sum("*SELECT* 1".pkt_in)), (sum("*SELECT* 1".pkt_out)), (sum("*SELECT* 1".dat_out))
  • Buffers: shared hit=428930 read=531946 written=13604
  • I/O Timings: read=32177.506 write=261.537
2. 0.038 65,562.438 ↑ 5.8 23 1

Sort (cost=134,493.48..134,493.82 rows=134 width=252) (actual time=65,562.436..65,562.438 rows=23 loops=1)

  • Output: "*SELECT* 1".ip_addr, (last("*SELECT* 1".flag_icon)), (max("*SELECT* 1".ip_rep)), (sum("*SELECT* 1".flow)), (sum("*SELECT* 1".pkt_in)), (sum("*SELECT* 1".pkt_out)), (sum("*SELECT* 1".dat_in)), (sum("*SELECT* 1".dat_out)), hosts_base.icon, hosts_base.name
  • Sort Key: (sum("*SELECT* 1".dat_in)) DESC, "*SELECT* 1".ip_addr, (last("*SELECT* 1".flag_icon)), (max("*SELECT* 1".ip_rep)), (sum("*SELECT* 1".flow)), (sum("*SELECT* 1".pkt_in)), (sum("*SELECT* 1".pkt_out)), (sum("*SELECT* 1".dat_out))
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=428930 read=531946 written=13604
  • I/O Timings: read=32177.506 write=261.537
3. 0.008 65,562.400 ↑ 5.8 23 1

Nested Loop Left Join (cost=133,648.85..134,488.75 rows=134 width=252) (actual time=65,559.113..65,562.400 rows=23 loops=1)

  • Output: "*SELECT* 1".ip_addr, (last("*SELECT* 1".flag_icon)), (max("*SELECT* 1".ip_rep)), (sum("*SELECT* 1".flow)), (sum("*SELECT* 1".pkt_in)), (sum("*SELECT* 1".pkt_out)), (sum("*SELECT* 1".dat_in)), (sum("*SELECT* 1".dat_out)), hosts_base.icon, hosts_base.name
  • Buffers: shared hit=428930 read=531946 written=13604
  • I/O Timings: read=32177.506 write=261.537
4. 1.688 65,562.346 ↑ 3.3 23 1

Hash Right Join (cost=133,648.43..134,439.04 rows=77 width=256) (actual time=65,559.096..65,562.346 rows=23 loops=1)

  • Output: "*SELECT* 1".ip_addr, (last("*SELECT* 1".flag_icon)), (max("*SELECT* 1".ip_rep)), (sum("*SELECT* 1".flow)), (sum("*SELECT* 1".pkt_in)), (sum("*SELECT* 1".pkt_out)), (sum("*SELECT* 1".dat_in)), (sum("*SELECT* 1".dat_out)), hosts_base.icon, hosts_base.name, hosts_base.host_id
  • Hash Cond: (hosts_base.ip_addr = "*SELECT* 1".ip_addr)
  • Buffers: shared hit=428882 read=531946 written=13604
  • I/O Timings: read=32177.506 write=261.537
5. 1.600 1.600 ↑ 1.0 15,843 1

Seq Scan on ti.hosts_base (cost=0.00..730.43 rows=15,843 width=39) (actual time=0.010..1.600 rows=15,843 loops=1)

  • Output: hosts_base.host_id, hosts_base.policy_id, hosts_base.sensor, hosts_base.ip_addr, hosts_base.name, hosts_base.sn_addr, hosts_base.hostname, hosts_base.icon, hosts_base.description, hosts_base.us_ids, hosts_base.us_names, hosts_base.us_icons, hosts_base.mac_addrs, hosts_base.ip_family, hosts_base.vlan_ids, hosts_base.interfaces, hosts_base.cve_ids, hosts_base.discovered
  • Buffers: shared hit=572
6. 0.005 65,559.058 ↑ 1.0 11 1

Hash (cost=133,648.30..133,648.30 rows=11 width=228) (actual time=65,559.058..65,559.058 rows=11 loops=1)

  • Output: "*SELECT* 1".ip_addr, (last("*SELECT* 1".flag_icon)), (max("*SELECT* 1".ip_rep)), (sum("*SELECT* 1".flow)), (sum("*SELECT* 1".pkt_in)), (sum("*SELECT* 1".pkt_out)), (sum("*SELECT* 1".dat_in)), (sum("*SELECT* 1".dat_out))
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=428310 read=531946 written=13604
  • I/O Timings: read=32177.506 write=261.537
7. 0.007 65,559.053 ↑ 1.0 11 1

Limit (cost=133,648.16..133,648.19 rows=11 width=228) (actual time=65,559.046..65,559.053 rows=11 loops=1)

  • Output: "*SELECT* 1".ip_addr, (last("*SELECT* 1".flag_icon)), (max("*SELECT* 1".ip_rep)), (sum("*SELECT* 1".flow)), (sum("*SELECT* 1".pkt_in)), (sum("*SELECT* 1".pkt_out)), (sum("*SELECT* 1".dat_in)), (sum("*SELECT* 1".dat_out))
  • Buffers: shared hit=428310 read=531946 written=13604
  • I/O Timings: read=32177.506 write=261.537
8. 188.923 65,559.046 ↑ 17.1 11 1

Sort (cost=133,648.16..133,648.63 rows=188 width=228) (actual time=65,559.044..65,559.046 rows=11 loops=1)

  • Output: "*SELECT* 1".ip_addr, (last("*SELECT* 1".flag_icon)), (max("*SELECT* 1".ip_rep)), (sum("*SELECT* 1".flow)), (sum("*SELECT* 1".pkt_in)), (sum("*SELECT* 1".pkt_out)), (sum("*SELECT* 1".dat_in)), (sum("*SELECT* 1".dat_out))
  • Sort Key: (sum("*SELECT* 1".dat_in)) DESC
  • Sort Method: top-N heapsort Memory: 26kB
  • Buffers: shared hit=428310 read=531946 written=13604
  • I/O Timings: read=32177.506 write=261.537
9. 9,492.070 65,370.123 ↓ 3,548.1 667,042 1

GroupAggregate (cost=133,588.98..133,643.97 rows=188 width=228) (actual time=54,648.116..65,370.123 rows=667,042 loops=1)

  • Output: "*SELECT* 1".ip_addr, last("*SELECT* 1".flag_icon), max("*SELECT* 1".ip_rep), sum("*SELECT* 1".flow), sum("*SELECT* 1".pkt_in), sum("*SELECT* 1".pkt_out), sum("*SELECT* 1".dat_in), sum("*SELECT* 1".dat_out)
  • Group Key: "*SELECT* 1".ip_addr
  • Buffers: shared hit=428310 read=531946 written=13604
  • I/O Timings: read=32177.506 write=261.537
10. 8,443.109 55,878.053 ↓ 62,391.0 11,729,510 1

Sort (cost=133,588.98..133,589.45 rows=188 width=88) (actual time=54,648.057..55,878.053 rows=11,729,510 loops=1)

  • Output: "*SELECT* 1".ip_addr, "*SELECT* 1".flag_icon, "*SELECT* 1".ip_rep, "*SELECT* 1".flow, "*SELECT* 1".pkt_in, "*SELECT* 1".pkt_out, "*SELECT* 1".dat_in, "*SELECT* 1".dat_out
  • Sort Key: "*SELECT* 1".ip_addr
  • Sort Method: quicksort Memory: 1670542kB
  • Buffers: shared hit=428310 read=531946 written=13604
  • I/O Timings: read=32177.506 write=261.537
11. 780.272 47,434.944 ↓ 62,391.0 11,729,510 1

Append (cost=0.00..133,581.88 rows=188 width=88) (actual time=81.862..47,434.944 rows=11,729,510 loops=1)

  • Buffers: shared hit=428310 read=531946 written=13604
  • I/O Timings: read=32177.506 write=261.537
12. 635.454 38,205.458 ↓ 62,391.0 5,864,755 1

Subquery Scan on *SELECT* 1 (cost=0.00..66,790.94 rows=94 width=88) (actual time=81.861..38,205.458 rows=5,864,755 loops=1)

  • Output: "*SELECT* 1".ip_addr, "*SELECT* 1".flag_icon, "*SELECT* 1".ip_rep, "*SELECT* 1".flow, "*SELECT* 1".pkt_in, "*SELECT* 1".pkt_out, "*SELECT* 1".dat_in, "*SELECT* 1".dat_out
  • Buffers: shared hit=14 read=480117 written=13202
  • I/O Timings: read=28495.519 write=257.806
13. 887.172 37,570.004 ↓ 62,391.0 5,864,755 1

Result (cost=0.00..66,790.00 rows=94 width=124) (actual time=81.848..37,570.004 rows=5,864,755 loops=1)

  • Output: f.src_ip_addr, f.src_country, f.src_ip_rep, f.sum_flow, f.dst_pktcnt, f.src_pktcnt, f.dst_octets, f.src_octets, NULL::bigint, NULL::bigint, NULL::real, NULL::real, NULL::real, NULL::real, NULL::real
  • Buffers: shared hit=14 read=480117 written=13202
  • I/O Timings: read=28495.519 write=257.806
14. 429.686 36,682.832 ↓ 62,391.0 5,864,755 1

Append (cost=0.00..66,789.06 rows=94 width=88) (actual time=81.847..36,682.832 rows=5,864,755 loops=1)

  • Buffers: shared hit=14 read=480117 written=13202
  • I/O Timings: read=28495.519 write=257.806
15. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on nb.flows30 f (cost=0.00..0.00 rows=1 width=88) (actual time=0.002..0.003 rows=0 loops=1)

  • Output: f.src_ip_addr, f.src_country, f.src_ip_rep, f.sum_flow, f.dst_pktcnt, f.src_pktcnt, f.dst_octets, f.src_octets
  • Filter: ((f."timestamp" >= '2019-04-07 22:00:00+00'::timestamp with time zone) AND (f."timestamp" < '2019-04-08 22:00:00+00'::timestamp with time zone) AND ((f.sensor)::text = 'baalSensor4'::text))
16. 1,724.238 1,789.025 ↓ 8,565.7 394,021 1

Bitmap Heap Scan on db."flows30_baalSensor4_2019017" f_1 (cost=160.84..33,207.12 rows=46 width=88) (actual time=81.844..1,789.025 rows=394,021 loops=1)

  • Output: f_1.src_ip_addr, f_1.src_country, f_1.src_ip_rep, f_1.sum_flow, f_1.dst_pktcnt, f_1.src_pktcnt, f_1.dst_octets, f_1.src_octets
  • Recheck Cond: ((f_1."timestamp" >= '2019-04-07 22:00:00+00'::timestamp with time zone) AND (f_1."timestamp" < '2019-04-08 22:00:00+00'::timestamp with time zone))
  • Rows Removed by Index Recheck: 1416
  • Filter: ((f_1.sensor)::text = 'baalSensor4'::text)
  • Heap Blocks: lossy=32892
  • Buffers: shared hit=7 read=32908
  • I/O Timings: read=1330.398
17. 64.787 64.787 ↓ 35.4 328,960 1

Bitmap Index Scan on "flows30_baalSensor4_2019017_timestamp_idx" (cost=0.00..160.82 rows=9,282 width=0) (actual time=64.786..64.787 rows=328,960 loops=1)

  • Index Cond: ((f_1."timestamp" >= '2019-04-07 22:00:00+00'::timestamp with time zone) AND (f_1."timestamp" < '2019-04-08 22:00:00+00'::timestamp with time zone))
  • Buffers: shared hit=4 read=16
  • I/O Timings: read=62.012
18. 34,383.167 34,464.118 ↓ 116,398.6 5,470,734 1

Bitmap Heap Scan on db."flows30_baalSensor4_2019018" f_2 (cost=161.88..33,581.94 rows=47 width=88) (actual time=104.348..34,464.118 rows=5,470,734 loops=1)

  • Output: f_2.src_ip_addr, f_2.src_country, f_2.src_ip_rep, f_2.sum_flow, f_2.dst_pktcnt, f_2.src_pktcnt, f_2.dst_octets, f_2.src_octets
  • Recheck Cond: ((f_2."timestamp" >= '2019-04-07 22:00:00+00'::timestamp with time zone) AND (f_2."timestamp" < '2019-04-08 22:00:00+00'::timestamp with time zone))
  • Rows Removed by Index Recheck: 2371
  • Filter: ((f_2.sensor)::text = 'baalSensor4'::text)
  • Heap Blocks: lossy=447193
  • Buffers: shared hit=7 read=447209 written=13202
  • I/O Timings: read=27165.121 write=257.806
19. 80.951 80.951 ↓ 476.4 4,472,320 1

Bitmap Index Scan on "flows30_baalSensor4_2019018_timestamp_idx" (cost=0.00..161.87 rows=9,387 width=0) (actual time=80.951..80.951 rows=4,472,320 loops=1)

  • Index Cond: ((f_2."timestamp" >= '2019-04-07 22:00:00+00'::timestamp with time zone) AND (f_2."timestamp" < '2019-04-08 22:00:00+00'::timestamp with time zone))
  • Buffers: shared hit=4 read=16
  • I/O Timings: read=27.871
20. 527.247 8,449.214 ↓ 62,391.0 5,864,755 1

Subquery Scan on *SELECT* 2 (cost=0.00..66,790.94 rows=94 width=88) (actual time=226.607..8,449.214 rows=5,864,755 loops=1)

  • Output: "*SELECT* 2".ip_addr, "*SELECT* 2".flag_icon, "*SELECT* 2".ip_rep, "*SELECT* 2".flow, "*SELECT* 2".pkt_in, "*SELECT* 2".pkt_out, "*SELECT* 2".dat_in, "*SELECT* 2".dat_out
  • Buffers: shared hit=428296 read=51829 written=402
  • I/O Timings: read=3681.987 write=3.731
21. 698.715 7,921.967 ↓ 62,391.0 5,864,755 1

Result (cost=0.00..66,790.00 rows=94 width=124) (actual time=226.606..7,921.967 rows=5,864,755 loops=1)

  • Output: f_3.dst_ip_addr, f_3.dst_country, f_3.dst_ip_rep, f_3.sum_flow, f_3.src_pktcnt, f_3.dst_pktcnt, f_3.src_octets, f_3.dst_octets, NULL::bigint, NULL::bigint, NULL::real, NULL::real, NULL::real, NULL::real, NULL::real
  • Buffers: shared hit=428296 read=51829 written=402
  • I/O Timings: read=3681.987 write=3.731
22. 344.930 7,223.252 ↓ 62,391.0 5,864,755 1

Append (cost=0.00..66,789.06 rows=94 width=88) (actual time=226.605..7,223.252 rows=5,864,755 loops=1)

  • Buffers: shared hit=428296 read=51829 written=402
  • I/O Timings: read=3681.987 write=3.731
23. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on nb.flows30 f_3 (cost=0.00..0.00 rows=1 width=88) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: f_3.dst_ip_addr, f_3.dst_country, f_3.dst_ip_rep, f_3.sum_flow, f_3.src_pktcnt, f_3.dst_pktcnt, f_3.src_octets, f_3.dst_octets
  • Filter: ((f_3."timestamp" >= '2019-04-07 22:00:00+00'::timestamp with time zone) AND (f_3."timestamp" < '2019-04-08 22:00:00+00'::timestamp with time zone) AND ((f_3.sensor)::text = 'baalSensor4'::text))
24. 2,064.189 2,193.308 ↓ 8,565.7 394,021 1

Bitmap Heap Scan on db."flows30_baalSensor4_2019017" f_4 (cost=160.84..33,207.12 rows=46 width=88) (actual time=226.598..2,193.308 rows=394,021 loops=1)

  • Output: f_4.dst_ip_addr, f_4.dst_country, f_4.dst_ip_rep, f_4.sum_flow, f_4.src_pktcnt, f_4.dst_pktcnt, f_4.src_octets, f_4.dst_octets
  • Recheck Cond: ((f_4."timestamp" >= '2019-04-07 22:00:00+00'::timestamp with time zone) AND (f_4."timestamp" < '2019-04-08 22:00:00+00'::timestamp with time zone))
  • Rows Removed by Index Recheck: 1416
  • Filter: ((f_4.sensor)::text = 'baalSensor4'::text)
  • Heap Blocks: lossy=32892
  • Buffers: shared hit=10 read=32902 written=110
  • I/O Timings: read=1693.976 write=1.426
25. 129.119 129.119 ↓ 35.4 328,960 1

Bitmap Index Scan on "flows30_baalSensor4_2019017_timestamp_idx" (cost=0.00..160.82 rows=9,282 width=0) (actual time=129.119..129.119 rows=328,960 loops=1)

  • Index Cond: ((f_4."timestamp" >= '2019-04-07 22:00:00+00'::timestamp with time zone) AND (f_4."timestamp" < '2019-04-08 22:00:00+00'::timestamp with time zone))
  • Buffers: shared hit=9 read=11
  • I/O Timings: read=126.158
26. 4,441.607 4,685.009 ↓ 116,398.6 5,470,734 1

Bitmap Heap Scan on db."flows30_baalSensor4_2019018" f_5 (cost=161.88..33,581.94 rows=47 width=88) (actual time=312.829..4,685.009 rows=5,470,734 loops=1)

  • Output: f_5.dst_ip_addr, f_5.dst_country, f_5.dst_ip_rep, f_5.sum_flow, f_5.src_pktcnt, f_5.dst_pktcnt, f_5.src_octets, f_5.dst_octets
  • Recheck Cond: ((f_5."timestamp" >= '2019-04-07 22:00:00+00'::timestamp with time zone) AND (f_5."timestamp" < '2019-04-08 22:00:00+00'::timestamp with time zone))
  • Rows Removed by Index Recheck: 2371
  • Filter: ((f_5.sensor)::text = 'baalSensor4'::text)
  • Heap Blocks: lossy=447193
  • Buffers: shared hit=428286 read=18927 written=292
  • I/O Timings: read=1988.011 write=2.305
27. 243.402 243.402 ↓ 476.4 4,472,320 1

Bitmap Index Scan on "flows30_baalSensor4_2019018_timestamp_idx" (cost=0.00..161.87 rows=9,387 width=0) (actual time=243.402..243.402 rows=4,472,320 loops=1)

  • Index Cond: ((f_5."timestamp" >= '2019-04-07 22:00:00+00'::timestamp with time zone) AND (f_5."timestamp" < '2019-04-08 22:00:00+00'::timestamp with time zone))
  • Buffers: shared hit=9 read=11 written=1
  • I/O Timings: read=217.880 write=0.018
28. 0.046 0.046 ↑ 1.0 1 23

Index Only Scan using hosts_model_gauss_idx on ti.hosts_model_gauss (cost=0.41..0.64 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=23)

  • Output: hosts_model_gauss.host_id
  • Index Cond: (hosts_model_gauss.host_id = hosts_base.host_id)
  • Heap Fetches: 4
  • Buffers: shared hit=48