explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o1iV

Settings
# exclusive inclusive rows x rows loops node
1. 0.060 56,692.145 ↑ 1.0 11 1

GroupAggregate (cost=112,985.45..113,025.88 rows=11 width=292) (actual time=56,692.097..56,692.145 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.035 56,692.085 ↑ 4.5 17 1

Sort (cost=112,985.45..112,985.64 rows=77 width=252) (actual time=56,692.084..56,692.085 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.003 56,692.050 ↑ 4.5 17 1

Nested Loop Left Join (cost=112,129.58..112,983.04 rows=77 width=252) (actual time=56,689.222..56,692.050 rows=17 loops=1)

4. 1.412 56,691.996 ↑ 4.5 17 1

Hash Right Join (cost=112,129.17..112,919.78 rows=77 width=256) (actual time=56,689.203..56,691.996 rows=17 loops=1)

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

6. 0.003 56,689.183 ↑ 1.0 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.009 56,689.180 ↑ 1.0 11 1

Limit (cost=112,128.90..112,128.92 rows=11 width=228) (actual time=56,689.173..56,689.180 rows=11 loops=1)

8. 171.805 56,689.171 ↑ 14.4 11 1

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

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

GroupAggregate (cost=112,079.16..112,125.37 rows=158 width=228) (actual time=48,872.827..56,517.366 rows=569,387 loops=1)

  • Group Key: "*SELECT* 1".ip_addr
10. 5,748.057 49,687.035 ↓ 49,984.5 7,897,556 1

Sort (cost=112,079.16..112,079.55 rows=158 width=88) (actual time=48,872.786..49,687.035 rows=7,897,556 loops=1)

  • Sort Key: "*SELECT* 1".ip_addr
  • Sort Method: quicksort Memory: 1087469kB
11. 441.292 43,938.978 ↓ 49,984.5 7,897,556 1

Append (cost=0.00..112,073.39 rows=158 width=88) (actual time=74.320..43,938.978 rows=7,897,556 loops=1)

12. 418.391 39,600.983 ↓ 49,984.5 3,948,778 1

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

13. 581.568 39,182.592 ↓ 49,984.5 3,948,778 1

Result (cost=0.00..56,035.90 rows=79 width=124) (actual time=74.320..39,182.592 rows=3,948,778 loops=1)

14. 238.512 38,601.024 ↓ 49,984.5 3,948,778 1

Append (cost=0.00..56,035.11 rows=79 width=88) (actual time=74.319..38,601.024 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. 1,454.429 1,493.389 ↓ 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=74.316..1,493.389 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. 38.960 38.960 ↓ 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=38.960..38.960 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. 36,791.333 36,869.120 ↓ 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=112.591..36,869.120 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. 77.787 77.787 ↓ 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=77.787..77.787 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. 355.071 3,896.703 ↓ 49,984.5 3,948,778 1

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

21. 457.383 3,541.632 ↓ 49,984.5 3,948,778 1

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

22. 197.410 3,084.249 ↓ 49,984.5 3,948,778 1

Append (cost=0.00..56,035.11 rows=79 width=88) (actual time=3.118..3,084.249 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. 303.449 306.173 ↓ 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=3.113..306.173 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.724 2.724 ↓ 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.724..2.724 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. 2,563.842 2,580.663 ↓ 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.937..2,580.663 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.821 16.821 ↓ 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.821..16.821 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.002..0.003 rows=0 loops=17)

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