explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W4AC

Settings
# exclusive inclusive rows x rows loops node
1. 0.066 16,365.818 ↑ 1.0 11 1

GroupAggregate (cost=108,656.28..108,696.70 rows=11 width=292) (actual time=16,365.764..16,365.818 rows=11 loops=1)

  • 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))
2. 0.026 16,365.752 ↑ 4.5 17 1

Sort (cost=108,656.28..108,656.47 rows=77 width=252) (actual time=16,365.751..16,365.752 rows=17 loops=1)

  • 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: 27kB
3. 0.009 16,365.726 ↑ 4.5 17 1

Nested Loop Left Join (cost=107,800.41..108,653.87 rows=77 width=252) (actual time=16,362.881..16,365.726 rows=17 loops=1)

4. 1.526 16,365.683 ↑ 4.5 17 1

Hash Right Join (cost=107,800.00..108,590.61 rows=77 width=256) (actual time=16,362.867..16,365.683 rows=17 loops=1)

  • Hash Cond: (hosts_base.ip_addr = "*SELECT* 1".ip_addr)
5. 1.310 1.310 ↑ 1.0 15,843 1

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

6. 0.005 16,362.847 ↑ 1.0 11 1

Hash (cost=107,799.86..107,799.86 rows=11 width=228) (actual time=16,362.847..16,362.847 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.006 16,362.842 ↑ 1.0 11 1

Limit (cost=107,799.72..107,799.75 rows=11 width=228) (actual time=16,362.836..16,362.842 rows=11 loops=1)

8. 144.264 16,362.836 ↑ 13.8 11 1

Sort (cost=107,799.72..107,800.10 rows=152 width=228) (actual time=16,362.836..16,362.836 rows=11 loops=1)

  • Sort Key: (sum("*SELECT* 1".dat_in)) DESC
  • Sort Method: top-N heapsort Memory: 26kB
9. 5,666.030 16,218.572 ↓ 3,645.1 554,059 1

GroupAggregate (cost=107,751.87..107,796.33 rows=152 width=228) (actual time=9,853.076..16,218.572 rows=554,059 loops=1)

  • Group Key: "*SELECT* 1".ip_addr
10. 4,767.436 10,552.542 ↓ 49,023.5 7,451,568 1

Sort (cost=107,751.87..107,752.25 rows=152 width=88) (actual time=9,853.037..10,552.542 rows=7,451,568 loops=1)

  • Sort Key: "*SELECT* 1".ip_addr
  • Sort Method: quicksort Memory: 1037565kB
11. 364.821 5,785.106 ↓ 49,023.5 7,451,568 1

Append (cost=0.00..107,746.36 rows=152 width=88) (actual time=2.341..5,785.106 rows=7,451,568 loops=1)

12. 315.003 2,654.457 ↓ 49,023.5 3,725,784 1

Subquery Scan on *SELECT* 1 (cost=0.00..53,873.18 rows=76 width=88) (actual time=2.341..2,654.457 rows=3,725,784 loops=1)

13. 405.504 2,339.454 ↓ 49,023.5 3,725,784 1

Result (cost=0.00..53,872.42 rows=76 width=124) (actual time=2.341..2,339.454 rows=3,725,784 loops=1)

14. 179.149 1,933.950 ↓ 49,023.5 3,725,784 1

Append (cost=0.00..53,871.66 rows=76 width=88) (actual time=2.340..1,933.950 rows=3,725,784 loops=1)

15. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (("timestamp" >= '2019-04-08 22:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-04-09 22:00:00+00'::timestamp with time zone) AND ((sensor)::text = 'baalSensor3'::text))
16. 128.384 130.444 ↓ 5,709.1 268,329 1

Bitmap Heap Scan on "flows30_baalSensor3_2019018" f_1 (cost=162.09..33,650.76 rows=47 width=88) (actual time=2.338..130.444 rows=268,329 loops=1)

  • Recheck Cond: (("timestamp" >= '2019-04-08 22:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-04-09 22:00:00+00'::timestamp with time zone))
  • Rows Removed by Index Recheck: 4111
  • Filter: ((sensor)::text = 'baalSensor3'::text)
  • Heap Blocks: lossy=22879
17. 2.060 2.060 ↓ 24.4 229,120 1

Bitmap Index Scan on "flows30_baalSensor3_2019018_timestamp_idx" (cost=0.00..162.07 rows=9,407 width=0) (actual time=2.060..2.060 rows=229,120 loops=1)

  • Index Cond: (("timestamp" >= '2019-04-08 22:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-04-09 22:00:00+00'::timestamp with time zone))
18. 1,609.080 1,624.355 ↓ 123,480.5 3,457,455 1

Bitmap Heap Scan on "flows30_baalSensor3_2019019" f_2 (cost=100.52..20,220.90 rows=28 width=88) (actual time=15.383..1,624.355 rows=3,457,455 loops=1)

  • Recheck Cond: (("timestamp" >= '2019-04-08 22:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-04-09 22:00:00+00'::timestamp with time zone))
  • Filter: ((sensor)::text = 'baalSensor3'::text)
  • Heap Blocks: lossy=282570
19. 15.275 15.275 ↓ 500.1 2,826,240 1

Bitmap Index Scan on "flows30_baalSensor3_2019019_timestamp_idx" (cost=0.00..100.51 rows=5,651 width=0) (actual time=15.275..15.275 rows=2,826,240 loops=1)

  • Index Cond: (("timestamp" >= '2019-04-08 22:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-04-09 22:00:00+00'::timestamp with time zone))
20. 316.457 2,765.828 ↓ 49,023.5 3,725,784 1

Subquery Scan on *SELECT* 2 (cost=0.00..53,873.18 rows=76 width=88) (actual time=2.195..2,765.828 rows=3,725,784 loops=1)

21. 400.658 2,449.371 ↓ 49,023.5 3,725,784 1

Result (cost=0.00..53,872.42 rows=76 width=124) (actual time=2.194..2,449.371 rows=3,725,784 loops=1)

22. 175.425 2,048.713 ↓ 49,023.5 3,725,784 1

Append (cost=0.00..53,871.66 rows=76 width=88) (actual time=2.193..2,048.713 rows=3,725,784 loops=1)

23. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: (("timestamp" >= '2019-04-08 22:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-04-09 22:00:00+00'::timestamp with time zone) AND ((sensor)::text = 'baalSensor3'::text))
24. 131.926 133.847 ↓ 5,709.1 268,329 1

Bitmap Heap Scan on "flows30_baalSensor3_2019018" f_4 (cost=162.09..33,650.76 rows=47 width=88) (actual time=2.189..133.847 rows=268,329 loops=1)

  • Recheck Cond: (("timestamp" >= '2019-04-08 22:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-04-09 22:00:00+00'::timestamp with time zone))
  • Rows Removed by Index Recheck: 4111
  • Filter: ((sensor)::text = 'baalSensor3'::text)
  • Heap Blocks: lossy=22879
25. 1.921 1.921 ↓ 24.4 229,120 1

Bitmap Index Scan on "flows30_baalSensor3_2019018_timestamp_idx" (cost=0.00..162.07 rows=9,407 width=0) (actual time=1.921..1.921 rows=229,120 loops=1)

  • Index Cond: (("timestamp" >= '2019-04-08 22:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-04-09 22:00:00+00'::timestamp with time zone))
26. 1,724.281 1,739.438 ↓ 123,480.5 3,457,455 1

Bitmap Heap Scan on "flows30_baalSensor3_2019019" f_5 (cost=100.52..20,220.90 rows=28 width=88) (actual time=15.265..1,739.438 rows=3,457,455 loops=1)

  • Recheck Cond: (("timestamp" >= '2019-04-08 22:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-04-09 22:00:00+00'::timestamp with time zone))
  • Filter: ((sensor)::text = 'baalSensor3'::text)
  • Heap Blocks: lossy=282570
27. 15.157 15.157 ↓ 500.1 2,826,240 1

Bitmap Index Scan on "flows30_baalSensor3_2019019_timestamp_idx" (cost=0.00..100.51 rows=5,651 width=0) (actual time=15.157..15.157 rows=2,826,240 loops=1)

  • Index Cond: (("timestamp" >= '2019-04-08 22:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-04-09 22:00:00+00'::timestamp with time zone))
28. 0.034 0.034 ↓ 0.0 0 17

Index Only Scan using hosts_model_gauss_idx on hosts_model_gauss (cost=0.41..0.81 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=17)

  • Index Cond: (host_id = hosts_base.host_id)
  • Heap Fetches: 7