explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8nIM

Settings
# exclusive inclusive rows x rows loops node
1. 0.079 34,247.502 ↑ 1.0 11 1

GroupAggregate (cost=112,985.45..113,025.88 rows=11 width=292) (actual time=34,247.435..34,247.502 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.028 34,247.423 ↑ 4.5 17 1

Sort (cost=112,985.45..112,985.64 rows=77 width=252) (actual time=34,247.422..34,247.423 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.005 34,247.395 ↑ 4.5 17 1

Nested Loop Left Join (cost=112,129.58..112,983.04 rows=77 width=252) (actual time=34,244.648..34,247.395 rows=17 loops=1)

4. 1.449 34,247.339 ↑ 4.5 17 1

Hash Right Join (cost=112,129.17..112,919.78 rows=77 width=256) (actual time=34,244.633..34,247.339 rows=17 loops=1)

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

6. 0.006 34,244.613 ↑ 1.0 11 1

Hash (cost=112,129.03..112,129.03 rows=11 width=228) (actual time=34,244.613..34,244.613 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.005 34,244.607 ↑ 1.0 11 1

Limit (cost=112,128.90..112,128.92 rows=11 width=228) (actual time=34,244.602..34,244.607 rows=11 loops=1)

8. 154.644 34,244.602 ↑ 14.4 11 1

Sort (cost=112,128.90..112,129.29 rows=158 width=228) (actual time=34,244.602..34,244.602 rows=11 loops=1)

  • Sort Key: (sum("*SELECT* 1".dat_in)) DESC
  • Sort Method: top-N heapsort Memory: 26kB
9. 6,225.744 34,089.958 ↓ 3,603.7 569,387 1

GroupAggregate (cost=112,079.16..112,125.37 rows=158 width=228) (actual time=27,073.198..34,089.958 rows=569,387 loops=1)

  • Group Key: "*SELECT* 1".ip_addr
10. 5,611.857 27,864.214 ↓ 49,984.5 7,897,556 1

Sort (cost=112,079.16..112,079.55 rows=158 width=88) (actual time=27,073.155..27,864.214 rows=7,897,556 loops=1)

  • Sort Key: "*SELECT* 1".ip_addr
  • Sort Method: quicksort Memory: 1087469kB
11. 416.604 22,252.357 ↓ 49,984.5 7,897,556 1

Append (cost=0.00..112,073.39 rows=158 width=88) (actual time=97.760..22,252.357 rows=7,897,556 loops=1)

12. 388.555 18,824.032 ↓ 49,984.5 3,948,778 1

Subquery Scan on *SELECT* 1 (cost=0.00..56,036.69 rows=79 width=88) (actual time=97.760..18,824.032 rows=3,948,778 loops=1)

13. 497.621 18,435.477 ↓ 49,984.5 3,948,778 1

Result (cost=0.00..56,035.90 rows=79 width=124) (actual time=97.759..18,435.477 rows=3,948,778 loops=1)

14. 220.189 17,937.856 ↓ 49,984.5 3,948,778 1

Append (cost=0.00..56,035.11 rows=79 width=88) (actual time=97.758..17,937.856 rows=3,948,778 loops=1)

15. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on flows30 f (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 = 'baalSensor1'::text))
16. 327.763 425.073 ↓ 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=97.755..425.073 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. 97.310 97.310 ↓ 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=97.310..97.310 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. 17,231.948 17,292.591 ↓ 122,968.0 3,566,073 1

Bitmap Heap Scan on "flows30_baalSensor1_2019020" f_2 (cost=102.98..21,100.61 rows=29 width=88) (actual time=60.774..17,292.591 rows=3,566,073 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=294877
19. 60.643 60.643 ↓ 500.0 2,949,120 1

Bitmap Index Scan on "flows30_baalSensor1_2019020_timestamp_idx" (cost=0.00..102.98 rows=5,898 width=0) (actual time=60.643..60.643 rows=2,949,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))
20. 360.005 3,011.721 ↓ 49,984.5 3,948,778 1

Subquery Scan on *SELECT* 2 (cost=0.00..56,036.69 rows=79 width=88) (actual time=2.854..3,011.721 rows=3,948,778 loops=1)

21. 448.954 2,651.716 ↓ 49,984.5 3,948,778 1

Result (cost=0.00..56,035.90 rows=79 width=124) (actual time=2.854..2,651.716 rows=3,948,778 loops=1)

22. 198.575 2,202.762 ↓ 49,984.5 3,948,778 1

Append (cost=0.00..56,035.11 rows=79 width=88) (actual time=2.853..2,202.762 rows=3,948,778 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 = 'baalSensor1'::text))
24. 204.343 207.027 ↓ 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.836..207.027 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.684 2.684 ↓ 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.684..2.684 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,780.622 1,797.157 ↓ 122,968.0 3,566,073 1

Bitmap Heap Scan on "flows30_baalSensor1_2019020" f_5 (cost=102.98..21,100.61 rows=29 width=88) (actual time=16.638..1,797.157 rows=3,566,073 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=294877
27. 16.535 16.535 ↓ 500.0 2,949,120 1

Bitmap Index Scan on "flows30_baalSensor1_2019020_timestamp_idx" (cost=0.00..102.98 rows=5,898 width=0) (actual time=16.535..16.535 rows=2,949,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))
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.003..0.003 rows=0 loops=17)

  • Index Cond: (host_id = hosts_base.host_id)
  • Heap Fetches: 7
Planning time : 5.118 ms
Execution time : 34,345.498 ms