explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EvQj

Settings
# exclusive inclusive rows x rows loops node
1. 0.147 192,086.198 ↑ 1.0 11 1

GroupAggregate (cost=4,360,156.32..4,360,227.57 rows=11 width=268) (actual time=192,086.071..192,086.198 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=689 read=1004456 written=7599
  • I/O Timings: read=163101.346 write=634.533
2. 0.088 192,086.051 ↑ 5.9 23 1

Sort (cost=4,360,156.32..4,360,156.66 rows=136 width=228) (actual time=192,086.049..192,086.051 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=689 read=1004456 written=7599
  • I/O Timings: read=163101.346 write=634.533
3. 0.017 192,085.963 ↑ 5.9 23 1

Nested Loop Left Join (cost=4,359,311.60..4,360,151.50 rows=136 width=228) (actual time=192,080.808..192,085.963 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=675 read=1004456 written=7599
  • I/O Timings: read=163101.346 write=634.533
4. 2.602 192,085.808 ↑ 3.3 23 1

Hash Right Join (cost=4,359,311.18..4,360,101.80 rows=77 width=232) (actual time=192,080.767..192,085.808 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=626 read=1004456 written=7599
  • I/O Timings: read=163101.346 write=634.533
5. 2.480 2.480 ↑ 1.0 15,843 1

Seq Scan on ti.hosts_base (cost=0.00..730.43 rows=15,843 width=39) (actual time=0.009..2.480 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.030 192,080.726 ↑ 1.0 11 1

Hash (cost=4,359,311.05..4,359,311.05 rows=11 width=204) (actual time=192,080.726..192,080.726 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=54 read=1004456 written=7599
  • I/O Timings: read=163101.346 write=634.533
7. 0.006 192,080.696 ↑ 1.0 11 1

Limit (cost=4,359,310.91..4,359,310.94 rows=11 width=204) (actual time=192,080.688..192,080.696 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=54 read=1004456 written=7599
  • I/O Timings: read=163101.346 write=634.533
8. 168.391 192,080.690 ↑ 18.2 11 1

Sort (cost=4,359,310.91..4,359,311.41 rows=200 width=204) (actual time=192,080.687..192,080.690 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=54 read=1004456 written=7599
  • I/O Timings: read=163101.346 write=634.533
9. 15,122.022 191,912.299 ↓ 3,335.2 667,042 1

HashAggregate (cost=4,359,301.95..4,359,306.45 rows=200 width=204) (actual time=191,392.288..191,912.299 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=54 read=1004456 written=7599
  • I/O Timings: read=163101.346 write=634.533
10. 789.800 176,790.277 ↓ 1.1 11,729,510 1

Append (cost=0.00..1,425,729.49 rows=10,966,626 width=55) (actual time=1,316.190..176,790.277 rows=11,729,510 loops=1)

  • Buffers: shared hit=54 read=1004456 written=7599
  • I/O Timings: read=163101.346 write=634.533
11. 646.133 154,976.737 ↓ 1.1 5,864,755 1

Subquery Scan on *SELECT* 1 (cost=0.00..712,864.75 rows=5,483,313 width=55) (actual time=1,316.190..154,976.737 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=16 read=502245 written=4852
  • I/O Timings: read=147223.245 write=595.205
12. 909.644 154,330.604 ↓ 1.1 5,864,755 1

Result (cost=0.00..658,031.62 rows=5,483,313 width=91) (actual time=1,316.189..154,330.604 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=16 read=502245 written=4852
  • I/O Timings: read=147223.245 write=595.205
13. 441.405 153,420.960 ↓ 1.1 5,864,755 1

Append (cost=0.00..603,198.49 rows=5,483,313 width=55) (actual time=1,316.188..153,420.960 rows=5,864,755 loops=1)

  • Buffers: shared hit=16 read=502245 written=4852
  • I/O Timings: read=147223.245 write=595.205
14. 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.003..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))
15. 13,920.233 14,184.734 ↓ 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=1,316.185..14,184.734 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=13 read=32908 written=4850
  • I/O Timings: read=11748.652 write=595.181
16. 264.501 264.501 ↓ 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=264.501..264.501 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=10 read=16 written=11
  • I/O Timings: read=261.721 write=0.098
17. 138,794.818 138,794.818 ↑ 1.0 5,470,734 1

Seq Scan on db."flows30_baalSensor4_2019018" f_2 (cost=0.00..569,991.36 rows=5,483,266 width=55) (actual time=379.750..138,794.818 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
  • Filter: ((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) AND ((f_2.sensor)::text = 'baalSensor4'::text))
  • Rows Removed by Filter: 267454
  • Buffers: shared hit=3 read=469337 written=2
  • I/O Timings: read=135474.593 write=0.024
18. 579.369 21,023.740 ↓ 1.1 5,864,755 1

Subquery Scan on *SELECT* 2 (cost=0.00..712,864.75 rows=5,483,313 width=55) (actual time=177.986..21,023.740 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=38 read=502211 written=2747
  • I/O Timings: read=15878.101 write=39.328
19. 832.757 20,444.371 ↓ 1.1 5,864,755 1

Result (cost=0.00..658,031.62 rows=5,483,313 width=91) (actual time=177.985..20,444.371 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=38 read=502211 written=2747
  • I/O Timings: read=15878.101 write=39.328
20. 381.581 19,611.614 ↓ 1.1 5,864,755 1

Append (cost=0.00..603,198.49 rows=5,483,313 width=55) (actual time=177.983..19,611.614 rows=5,864,755 loops=1)

  • Buffers: shared hit=38 read=502211 written=2747
  • I/O Timings: read=15878.101 write=39.328
21. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on nb.flows30 f_3 (cost=0.00..0.00 rows=1 width=88) (actual time=0.002..0.002 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))
22. 1,762.871 1,897.442 ↓ 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=177.964..1,897.442 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=6 read=32906 written=2745
  • I/O Timings: read=1411.420 write=39.310
23. 134.571 134.571 ↓ 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=134.570..134.571 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=5 read=15 written=2
  • I/O Timings: read=123.860 write=0.086
24. 17,332.589 17,332.589 ↑ 1.0 5,470,734 1

Seq Scan on db."flows30_baalSensor4_2019018" f_5 (cost=0.00..569,991.36 rows=5,483,266 width=55) (actual time=0.014..17,332.589 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
  • Filter: ((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) AND ((f_5.sensor)::text = 'baalSensor4'::text))
  • Rows Removed by Filter: 267454
  • Buffers: shared hit=32 read=469305 written=2
  • I/O Timings: read=14466.681 write=0.018
25. 0.138 0.138 ↑ 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.006..0.006 rows=1 loops=23)

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