explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MT4x

Settings
# exclusive inclusive rows x rows loops node
1. 0.069 27,800.750 ↑ 1.0 11 1

GroupAggregate (cost=114,429.63..114,470.05 rows=11 width=292) (actual time=27,800.695..27,800.750 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.027 27,800.681 ↑ 4.5 17 1

Sort (cost=114,429.63..114,429.82 rows=77 width=252) (actual time=27,800.681..27,800.681 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.004 27,800.654 ↑ 4.5 17 1

Nested Loop Left Join (cost=113,573.76..114,427.21 rows=77 width=252) (actual time=27,797.587..27,800.654 rows=17 loops=1)

4. 1.530 27,800.599 ↑ 4.5 17 1

Hash Right Join (cost=113,573.35..114,363.96 rows=77 width=256) (actual time=27,797.568..27,800.599 rows=17 loops=1)

  • Hash Cond: (hosts_base.ip_addr = "*SELECT* 1".ip_addr)
5. 1.521 1.521 ↑ 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.521 rows=15,843 loops=1)

6. 0.003 27,797.548 ↑ 1.0 11 1

Hash (cost=113,573.21..113,573.21 rows=11 width=228) (actual time=27,797.548..27,797.548 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.008 27,797.545 ↑ 1.0 11 1

Limit (cost=113,573.07..113,573.10 rows=11 width=228) (actual time=27,797.537..27,797.545 rows=11 loops=1)

8. 160.428 27,797.537 ↑ 14.5 11 1

Sort (cost=113,573.07..113,573.47 rows=160 width=228) (actual time=27,797.536..27,797.537 rows=11 loops=1)

  • Sort Key: (sum("*SELECT* 1".dat_in)) DESC
  • Sort Method: top-N heapsort Memory: 26kB
9. 6,553.508 27,637.109 ↓ 3,612.6 578,016 1

GroupAggregate (cost=113,522.70..113,569.50 rows=160 width=228) (actual time=20,279.779..27,637.109 rows=578,016 loops=1)

  • Group Key: "*SELECT* 1".ip_addr
10. 5,459.493 21,083.601 ↓ 50,896.4 8,143,418 1

Sort (cost=113,522.70..113,523.10 rows=160 width=88) (actual time=20,279.737..21,083.601 rows=8,143,418 loops=1)

  • Sort Key: "*SELECT* 1".ip_addr
  • Sort Method: quicksort Memory: 1115344kB
11. 420.730 15,624.108 ↓ 50,896.4 8,143,418 1

Append (cost=0.00..113,516.85 rows=160 width=88) (actual time=75.439..15,624.108 rows=8,143,418 loops=1)

12. 383.405 12,284.549 ↓ 50,896.4 4,071,709 1

Subquery Scan on *SELECT* 1 (cost=0.00..56,758.42 rows=80 width=88) (actual time=75.439..12,284.549 rows=4,071,709 loops=1)

13. 510.827 11,901.144 ↓ 50,896.4 4,071,709 1

Result (cost=0.00..56,757.62 rows=80 width=124) (actual time=75.439..11,901.144 rows=4,071,709 loops=1)

14. 225.052 11,390.317 ↓ 50,896.4 4,071,709 1

Append (cost=0.00..56,756.82 rows=80 width=88) (actual time=75.438..11,390.317 rows=4,071,709 loops=1)

15. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on flows30 f (cost=0.00..0.00 rows=1 width=88) (actual time=0.001..0.001 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 = 'baalSensor1'::text))
16. 1,461.715 1,501.403 ↓ 7,810.3 382,705 1

Bitmap Heap Scan on "flows30_baalSensor1_2019019" f_1 (cost=169.66..34,934.50 rows=49 width=88) (actual time=75.436..1,501.403 rows=382,705 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: 4805
  • Filter: ((sensor)::text = 'baalSensor1'::text)
  • Heap Blocks: lossy=32558
17. 39.688 39.688 ↓ 33.4 326,400 1

Bitmap Index Scan on "flows30_baalSensor1_2019019_timestamp_idx" (cost=0.00..169.65 rows=9,765 width=0) (actual time=39.688..39.688 rows=326,400 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. 9,646.112 9,663.861 ↓ 122,966.8 3,689,004 1

Bitmap Heap Scan on "flows30_baalSensor1_2019020" f_2 (cost=109.00..21,822.32 rows=30 width=88) (actual time=37.604..9,663.861 rows=3,689,004 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 = 'baalSensor1'::text)
  • Heap Blocks: lossy=304970
19. 17.749 17.749 ↓ 500.1 3,050,240 1

Bitmap Index Scan on "flows30_baalSensor1_2019020_timestamp_idx" (cost=0.00..108.99 rows=6,099 width=0) (actual time=17.749..17.749 rows=3,050,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. 351.895 2,918.829 ↓ 50,896.4 4,071,709 1

Subquery Scan on *SELECT* 2 (cost=0.00..56,758.42 rows=80 width=88) (actual time=2.679..2,918.829 rows=4,071,709 loops=1)

21. 436.955 2,566.934 ↓ 50,896.4 4,071,709 1

Result (cost=0.00..56,757.62 rows=80 width=124) (actual time=2.679..2,566.934 rows=4,071,709 loops=1)

22. 195.087 2,129.979 ↓ 50,896.4 4,071,709 1

Append (cost=0.00..56,756.82 rows=80 width=88) (actual time=2.678..2,129.979 rows=4,071,709 loops=1)

23. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on flows30 f_3 (cost=0.00..0.00 rows=1 width=88) (actual time=0.005..0.005 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 = 'baalSensor1'::text))
24. 180.494 183.025 ↓ 7,810.3 382,705 1

Bitmap Heap Scan on "flows30_baalSensor1_2019019" f_4 (cost=169.66..34,934.50 rows=49 width=88) (actual time=2.673..183.025 rows=382,705 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: 4805
  • Filter: ((sensor)::text = 'baalSensor1'::text)
  • Heap Blocks: lossy=32558
25. 2.531 2.531 ↓ 33.4 326,400 1

Bitmap Index Scan on "flows30_baalSensor1_2019019_timestamp_idx" (cost=0.00..169.65 rows=9,765 width=0) (actual time=2.531..2.531 rows=326,400 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,734.372 1,751.862 ↓ 122,966.8 3,689,004 1

Bitmap Heap Scan on "flows30_baalSensor1_2019020" f_5 (cost=109.00..21,822.32 rows=30 width=88) (actual time=17.616..1,751.862 rows=3,689,004 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 = 'baalSensor1'::text)
  • Heap Blocks: lossy=304970
27. 17.490 17.490 ↓ 500.1 3,050,240 1

Bitmap Index Scan on "flows30_baalSensor1_2019020_timestamp_idx" (cost=0.00..108.99 rows=6,099 width=0) (actual time=17.490..17.490 rows=3,050,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.051 0.051 ↓ 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.003 rows=0 loops=17)

  • Index Cond: (host_id = hosts_base.host_id)
  • Heap Fetches: 7
Planning time : 4.489 ms
Execution time : 27,922.710 ms