explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4m8X : Optimization for: plan #K5AN

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.008 144.941 ↓ 50.0 50 1

Limit (cost=275.55..275.55 rows=1 width=249) (actual time=144.932..144.941 rows=50 loops=1)

2. 0.057 144.933 ↓ 50.0 50 1

Sort (cost=275.55..275.55 rows=1 width=249) (actual time=144.931..144.933 rows=50 loops=1)

  • Sort Key: (sum(t.sum_dat[1])) DESC
  • Sort Method: top-N heapsort Memory: 70kB
3. 2.246 144.876 ↓ 104.0 104 1

GroupAggregate (cost=275.23..275.54 rows=1 width=249) (actual time=142.710..144.876 rows=104 loops=1)

  • Group Key: t.sensor, t.ip_addr, hosts_base.discovered, hosts_base.icon
4. 1.351 142.630 ↓ 1,346.0 1,346 1

Sort (cost=275.23..275.24 rows=1 width=319) (actual time=142.551..142.630 rows=1,346 loops=1)

  • Sort Key: t.sensor, t.ip_addr, hosts_base.discovered, hosts_base.icon
  • Sort Method: quicksort Memory: 406kB
5. 0.198 141.279 ↓ 1,346.0 1,346 1

Nested Loop Left Join (cost=10.87..275.22 rows=1 width=319) (actual time=0.099..141.279 rows=1,346 loops=1)

6. 7.599 138.389 ↓ 1,346.0 1,346 1

Nested Loop (cost=10.72..275.00 rows=1 width=293) (actual time=0.089..138.389 rows=1,346 loops=1)

  • Join Filter: (hb.host_id = ho.host_id)
  • Rows Removed by Join Filter: 138638
7. 0.062 0.062 ↓ 104.0 104 1

Seq Scan on hosts_os ho (cost=0.00..70.84 rows=1 width=4) (actual time=0.012..0.062 rows=104 loops=1)

  • Filter: ((valid_until IS NULL) AND (os_id = ANY ('{1026,1027,1030,1031,1000,1032,1033,1001,1034,1002,1035,1003,1036,1004,1037,1038,1039,1040,1041,1013,1019,1022,1023}'::int
8. 40.608 130.728 ↓ 673.0 1,346 104

Hash Join (cost=10.72..204.14 rows=2 width=297) (actual time=0.005..1.257 rows=1,346 loops=104)

  • Hash Cond: (((t.sensor)::text = (hb.sensor)::text) AND (t.ip_addr = hb.ip_addr))
9. 13.000 90.064 ↓ 2.8 2,084 104

Append (cost=0.00..189.50 rows=747 width=293) (actual time=0.002..0.866 rows=2,084 loops=104)

10. 0.000 0.000 ↓ 0.0 0 104

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

  • 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) AND ((sum_da
11. 77.064 77.064 ↓ 2.8 2,084 104

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

  • 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) AND ((sum_da
  • Rows Removed by Filter: 172
12. 0.017 0.056 ↑ 1.0 104 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.039 0.039 ↑ 1.0 104 1

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

14. 2.692 2.692 ↑ 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.002..0.002 rows=1 loops=1,346)

  • Index Cond: (((sensor)::text = (t.sensor)::text) AND (ip_addr = t.ip_addr))
Planning time : 0.604 ms
Execution time : 145.018 ms