explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 55dx

Settings
# exclusive inclusive rows x rows loops node
1. 0.140 169,545.815 ↑ 1.0 11 1

GroupAggregate (cost=4,360,156.23..4,360,226.44 rows=11 width=268) (actual time=169,545.707..169,545.815 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=677 read=1004456 written=12567
  • I/O Timings: read=141829.142 write=560.012
2. 0.061 169,545.675 ↑ 5.8 23 1

Sort (cost=4,360,156.23..4,360,156.57 rows=134 width=228) (actual time=169,545.675..169,545.675 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=677 read=1004456 written=12567
  • I/O Timings: read=141829.142 write=560.012
3. 0.024 169,545.614 ↑ 5.8 23 1

Nested Loop Left Join (cost=4,359,311.60..4,360,151.50 rows=134 width=228) (actual time=169,540.304..169,545.614 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=674 read=1004456 written=12567
  • I/O Timings: read=141829.142 write=560.012
4. 2.698 169,545.452 ↑ 3.3 23 1

Hash Right Join (cost=4,359,311.18..4,360,101.80 rows=77 width=232) (actual time=169,540.264..169,545.452 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=12567
  • I/O Timings: read=141829.142 write=560.012
5. 2.524 2.524 ↑ 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.524 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.009 169,540.230 ↑ 1.0 11 1

Hash (cost=4,359,311.05..4,359,311.05 rows=11 width=204) (actual time=169,540.230..169,540.230 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=12567
  • I/O Timings: read=141829.142 write=560.012
7. 0.006 169,540.221 ↑ 1.0 11 1

Limit (cost=4,359,310.91..4,359,310.94 rows=11 width=204) (actual time=169,540.213..169,540.221 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=12567
  • I/O Timings: read=141829.142 write=560.012
8. 168.016 169,540.215 ↑ 18.2 11 1

Sort (cost=4,359,310.91..4,359,311.41 rows=200 width=204) (actual time=169,540.212..169,540.215 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=12567
  • I/O Timings: read=141829.142 write=560.012
9. 14,961.771 169,372.199 ↓ 3,335.2 667,042 1

HashAggregate (cost=4,359,301.95..4,359,306.45 rows=200 width=204) (actual time=168,851.299..169,372.199 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=12567
  • I/O Timings: read=141829.142 write=560.012
10. 842.853 154,410.428 ↓ 1.1 11,729,510 1

Append (cost=0.00..1,425,729.49 rows=10,966,626 width=55) (actual time=83.061..154,410.428 rows=11,729,510 loops=1)

  • Buffers: shared hit=54 read=1004456 written=12567
  • I/O Timings: read=141829.142 write=560.012
11. 656.518 116,832.850 ↓ 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=83.060..116,832.850 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=8061
  • I/O Timings: read=110786.683 write=68.197
12. 906.370 116,176.332 ↓ 1.1 5,864,755 1

Result (cost=0.00..658,031.62 rows=5,483,313 width=91) (actual time=83.060..116,176.332 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=8061
  • I/O Timings: read=110786.683 write=68.197
13. 446.837 115,269.962 ↓ 1.1 5,864,755 1

Append (cost=0.00..603,198.49 rows=5,483,313 width=55) (actual time=83.059..115,269.962 rows=5,864,755 loops=1)

  • Buffers: shared hit=16 read=502245 written=8061
  • I/O Timings: read=110786.683 write=68.197
14. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on nb.flows30 f (cost=0.00..0.00 rows=1 width=88) (actual time=0.002..0.002 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. 2,104.561 2,170.371 ↓ 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=83.057..2,170.371 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=8061
  • I/O Timings: read=1639.601 write=68.197
16. 65.810 65.810 ↓ 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=65.810..65.810 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
  • I/O Timings: read=63.147
17. 112,652.752 112,652.752 ↑ 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=46.051..112,652.752 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
  • I/O Timings: read=109147.082
18. 626.154 36,734.725 ↓ 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=129.689..36,734.725 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=4506
  • I/O Timings: read=31042.459 write=491.815
19. 856.102 36,108.571 ↓ 1.1 5,864,755 1

Result (cost=0.00..658,031.62 rows=5,483,313 width=91) (actual time=129.688..36,108.571 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=4506
  • I/O Timings: read=31042.459 write=491.815
20. 389.245 35,252.469 ↓ 1.1 5,864,755 1

Append (cost=0.00..603,198.49 rows=5,483,313 width=55) (actual time=129.687..35,252.469 rows=5,864,755 loops=1)

  • Buffers: shared hit=38 read=502211 written=4506
  • I/O Timings: read=31042.459 write=491.815
21. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on nb.flows30 f_3 (cost=0.00..0.00 rows=1 width=88) (actual time=0.003..0.003 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. 2,067.530 2,185.257 ↓ 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=129.682..2,185.257 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=4506
  • I/O Timings: read=1213.480 write=491.815
23. 117.727 117.727 ↓ 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=117.727..117.727 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
  • I/O Timings: read=115.044
24. 32,677.964 32,677.964 ↑ 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=37.808..32,677.964 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
  • I/O Timings: read=29828.979
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: 4
  • Buffers: shared hit=48
Planning time : 46.581 ms
Execution time : 169,556.326 ms