explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wR337

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2.700 ↓ 0.0 0 1

GroupAggregate (cost=5,286.80..5,315.52 rows=8 width=1,400) (actual time=2.700..2.700 rows=0 loops=1)

  • Group Key: h.sensor, h.ip_addr, hosts_base.sn_addr, h.sum_flow, h.sum_pkt, h.sum_dat, h.sum_sec, h.sum_peer, h.sum_port, hosts_model_gauss.sum_flow, hosts_model_gauss.sum_pkt, hosts_model_gauss.sum_dat, hosts_model_gauss.sum_sec, hosts_model_gauss.sum_peer, hosts_model_gauss.sum_port, policy_limit.max_flow, policy_
2. 0.085 2.697 ↓ 0.0 0 1

Sort (cost=5,286.80..5,286.82 rows=8 width=1,369) (actual time=2.697..2.697 rows=0 loops=1)

  • Sort Key: h.ip_addr, hosts_base.sn_addr, h.sum_flow, h.sum_pkt, h.sum_dat, h.sum_sec, h.sum_peer, h.sum_port, hosts_model_gauss.sum_flow, hosts_model_gauss.sum_pkt, hosts_model_gauss.sum_dat, hosts_model_gauss.sum_sec, hosts_model_gauss.sum_peer, hosts_model_gauss.sum_port, policy_limit.max_flow, policy_limit
  • Sort Method: quicksort Memory: 25kB
3. 0.000 2.612 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.85..5,286.68 rows=8 width=1,369) (actual time=2.612..2.612 rows=0 loops=1)

  • Join Filter: (subnets_base.policy_id = policy_limit_1.policy_id)
4. 0.002 2.612 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.85..5,256.80 rows=8 width=1,369) (actual time=2.612..2.612 rows=0 loops=1)

  • Join Filter: (((subnets_base.sensor)::text = (h.sensor)::text) AND ((subnets_base.sn_addr)::inet = (COALESCE(hosts_base.sn_addr, to_sn_addr(h.sensor, h.ip_addr)))::inet))
5. 0.000 2.610 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.71..5,246.50 rows=8 width=1,073) (actual time=2.610..2.610 rows=0 loops=1)

6. 0.000 2.610 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.43..5,236.27 rows=8 width=711) (actual time=2.610..2.610 rows=0 loops=1)

  • Filter: (((limit_compare(h.sum_flow, policy_limit.max_flow))[6] > '0'::double precision) OR ((limit_compare(h.sum_pkt, policy_limit.max_pkt))[6] > '0'::double precision) OR ((limit_compare(h.sum_dat, policy_limit.max_dat))[6] > '0'::double precision) OR ((limit_compare(h.sum_sec, polic
7. 0.000 2.610 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.28..5,234.26 rows=9 width=518) (actual time=2.610..2.610 rows=0 loops=1)

8. 0.001 2.610 ↓ 0.0 0 1

Append (cost=0.00..5,163.45 rows=9 width=470) (actual time=2.610..2.610 rows=0 loops=1)

9. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on hosts01 h (cost=0.00..0.00 rows=1 width=470) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (((sensor)::text = 'mendel2'::text) AND ("timestamp" = '2019-03-11 07:55:00+00'::timestamp with time zone))
10. 2.303 2.606 ↓ 0.0 0 1

Bitmap Heap Scan on hosts01_mendel2_2019006 h_1 (cost=23.78..5,163.45 rows=8 width=470) (actual time=2.606..2.606 rows=0 loops=1)

  • Recheck Cond: ("timestamp" = '2019-03-11 07:55:00+00'::timestamp with time zone)
  • Rows Removed by Index Recheck: 2974
  • Filter: ((sensor)::text = 'mendel2'::text)
  • Heap Blocks: lossy=185
11. 0.303 0.303 ↓ 1.6 2,560 1

Bitmap Index Scan on hosts01_mendel2_2019006_timestamp_idx (cost=0.00..23.78 rows=1,571 width=0) (actual time=0.303..0.303 rows=2,560 loops=1)

  • Index Cond: ("timestamp" = '2019-03-11 07:55:00+00'::timestamp with time zone)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using hosts_base_unique on hosts_base (cost=0.28..7.86 rows=1 width=67) (never executed)

  • Index Cond: (((sensor)::text = (h.sensor)::text) AND ((sensor)::text = 'mendel2'::text) AND (ip_addr = h.ip_addr))
13. 0.000 0.000 ↓ 0.0 0

Index Scan using policy_limit_pkey on policy_limit (cost=0.14..0.18 rows=1 width=201) (never executed)

  • Index Cond: (hosts_base.policy_id = policy_id)
14. 0.000 0.000 ↓ 0.0 0

Index Scan using hosts_model_gauss_idx on hosts_model_gauss (cost=0.28..1.27 rows=1 width=370) (never executed)

  • Index Cond: (hosts_base.host_id = host_id)
15. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.14..8.17 rows=1 width=474) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Index Scan using subnets_unique_sensor_sn_addr on subnets_base (cost=0.14..8.16 rows=1 width=474) (never executed)

  • Index Cond: ((sensor)::text = 'mendel2'::text)
17. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..12.25 rows=150 width=8) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Seq Scan on policy_limit policy_limit_1 (cost=0.00..11.50 rows=150 width=8) (never executed)