explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BFFj : Optimization for: plan #wSJV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.010 50.037 ↓ 50.0 50 1

Limit (cost=235.31..235.31 rows=1 width=249) (actual time=50.024..50.037 rows=50 loops=1)

2. 0.096 50.027 ↓ 50.0 50 1

Sort (cost=235.31..235.31 rows=1 width=249) (actual time=50.023..50.027 rows=50 loops=1)

  • Sort Key: (sum(t.sum_dat[1])) DESC
  • Sort Method: quicksort Memory: 76kB
3. 4.356 49.931 ↓ 100.0 100 1

GroupAggregate (cost=234.99..235.30 rows=1 width=249) (actual time=46.044..49.931 rows=100 loops=1)

  • Group Key: t.sensor, t.ip_addr, hosts_base.discovered, hosts_base.icon
4. 2.692 45.575 ↓ 1,295.0 1,295 1

Sort (cost=234.99..235.00 rows=1 width=319) (actual time=45.468..45.575 rows=1,295 loops=1)

  • Sort Key: t.sensor, t.ip_addr, hosts_base.discovered, hosts_base.icon
  • Sort Method: quicksort Memory: 392kB
5. 1.263 42.883 ↓ 1,295.0 1,295 1

Nested Loop (cost=11.02..234.98 rows=1 width=319) (actual time=0.291..42.883 rows=1,295 loops=1)

6. 1.165 40.274 ↓ 1,346.0 1,346 1

Nested Loop Left Join (cost=10.87..230.30 rows=1 width=323) (actual time=0.270..40.274 rows=1,346 loops=1)

7. 21.383 35.071 ↓ 1,346.0 1,346 1

Nested Loop (cost=10.72..230.09 rows=1 width=297) (actual time=0.247..35.071 rows=1,346 loops=1)

  • Join Filter: (hb.host_id = ho.host_id)
  • Rows Removed by Join Filter: 138638
8. 0.064 0.064 ↓ 13.0 104 1

Seq Scan on hosts_os ho (cost=0.00..25.70 rows=8 width=8) (actual time=0.015..0.064 rows=104 loops=1)

  • Filter: (valid_until IS NULL)
9. 9.788 13.624 ↓ 673.0 1,346 104

Materialize (cost=10.72..204.15 rows=2 width=297) (actual time=0.002..0.131 rows=1,346 loops=104)

10. 0.713 3.836 ↓ 673.0 1,346 1

Hash Join (cost=10.72..204.14 rows=2 width=297) (actual time=0.224..3.836 rows=1,346 loops=1)

  • Hash Cond: (((t.sensor)::text = (hb.sensor)::text) AND (t.ip_addr = hb.ip_addr))
11. 0.177 2.990 ↓ 2.8 2,084 1

Append (cost=0.00..189.50 rows=747 width=293) (actual time=0.063..2.990 rows=2,084 loops=1)

12. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on hosts05 t (cost=0.00..0.00 rows=1 width=452) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (("timestamp" >= '2019-10-07 11:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-10-08 11:00:00+00'::timestamp with time zone)
13. 2.810 2.810 ↓ 2.8 2,084 1

Seq Scan on "hosts05_pliesk-virt_2019001" t_1 (cost=0.00..185.76 rows=746 width=293) (actual time=0.059..2.810 rows=2,084 loops=1)

  • Filter: (("timestamp" >= '2019-10-07 11:00:00+00'::timestamp with time zone) AND ("timestamp" < '2019-10-08 11:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 172
14. 0.076 0.133 ↑ 1.0 104 1

Hash (cost=9.09..9.09 rows=109 width=23) (actual time=0.133..0.133 rows=104 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.057 0.057 ↑ 1.0 104 1

Seq Scan on hosts_base hb (cost=0.00..9.09 rows=109 width=23) (actual time=0.010..0.057 rows=104 loops=1)

16. 4.038 4.038 ↑ 1.0 1 1,346

Index Scan using hosts_base_unique on hosts_base (cost=0.14..0.22 rows=1 width=53) (actual time=0.003..0.003 rows=1 loops=1,346)

  • Index Cond: (((sensor)::text = (t.sensor)::text) AND (ip_addr = t.ip_addr))
17. 1.346 1.346 ↑ 1.0 1 1,346

Index Scan using os_pkey on os (cost=0.15..4.17 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,346)

  • Index Cond: (os_id = ho.os_id)
  • Filter: (parent_os_id = ANY ('{1013,1000,1001,1019,1037}'::integer[]))
  • Rows Removed by Filter: 0
Planning time : 0.913 ms
Execution time : 50.272 ms