explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uVJQ : Optimization for: plan #iLaV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 92.286 364.018 ↓ 20,496.0 20,496 1

WindowAgg (cost=1,211.23..1,211.26 rows=1 width=76) (actual time=270.581..364.018 rows=20,496 loops=1)

  • Output: h.id, h.gateway_id, h.sensor_id, h.received_at, (first_value(h.received_at) OVER (?) - h.received_at), rank() OVER (?), to_json(ROW(h.id, h.gateway_id, h.bru_id, h.sensor_id, h.sensor_temp_c, h.sensor_ts, h.sensor_ml, h.sensor_vfr, h.sensor_flags, h.sensor_error, h.sensor_sig, h.sensor_cdtof, h.bru_ts, h.bru_temp_c, h.bru_baro, h.bru_hum, h.received_at, h.processed_window_at, h.bru_addr, h.sensor_addr, h.processed_missing_at))
  • Buffers: shared hit=317,487
2. 11.833 271.732 ↓ 20,496.0 20,496 1

Sort (cost=1,211.23..1,211.23 rows=1 width=96) (actual time=270.559..271.732 rows=20,496 loops=1)

  • Output: h.sensor_id, h.received_at, h.id, h.gateway_id, h.bru_id, h.sensor_temp_c, h.sensor_ts, h.sensor_ml, h.sensor_vfr, h.sensor_flags, h.sensor_error, h.sensor_sig, h.sensor_cdtof, h.bru_ts, h.bru_temp_c, h.bru_baro, h.bru_hum, h.processed_window_at, h.bru_addr, h.sensor_addr, h.processed_missing_at
  • Sort Key: h.sensor_id, h.received_at DESC
  • Sort Method: quicksort Memory: 3,468kB
  • Buffers: shared hit=317,487
3. 78.014 259.899 ↓ 20,496.0 20,496 1

Nested Loop (cost=14.96..1,211.22 rows=1 width=96) (actual time=0.100..259.899 rows=20,496 loops=1)

  • Output: h.sensor_id, h.received_at, h.id, h.gateway_id, h.bru_id, h.sensor_temp_c, h.sensor_ts, h.sensor_ml, h.sensor_vfr, h.sensor_flags, h.sensor_error, h.sensor_sig, h.sensor_cdtof, h.bru_ts, h.bru_temp_c, h.bru_baro, h.bru_hum, h.processed_window_at, h.bru_addr, h.sensor_addr, h.processed_missing_at
  • Join Filter: (h.received_at <@ il.connected)
  • Rows Removed by Join Filter: 606,645
  • Buffers: shared hit=317,487
4. 3.819 17.389 ↓ 2,937.4 20,562 1

Nested Loop (cost=14.68..1,201.65 rows=7 width=100) (actual time=0.060..17.389 rows=20,562 loops=1)

  • Output: h.id, h.gateway_id, h.sensor_id, h.received_at, h.bru_id, h.sensor_temp_c, h.sensor_ts, h.sensor_ml, h.sensor_vfr, h.sensor_flags, h.sensor_error, h.sensor_sig, h.sensor_cdtof, h.bru_ts, h.bru_temp_c, h.bru_baro, h.bru_hum, h.processed_window_at, h.bru_addr, h.sensor_addr, h.processed_missing_at, ls.line_id
  • Join Filter: (h.id <= h_1.id)
  • Buffers: shared hit=19,338
5. 0.063 0.108 ↑ 1.0 2 1

Hash Join (cost=14.26..21.31 rows=2 width=32) (actual time=0.048..0.108 rows=2 loops=1)

  • Output: ls.sensor_id, ls.connected, ls.line_id, h_1.sensor_id, h_1.id
  • Hash Cond: (ls.sensor_id = h_1.sensor_id)
  • Buffers: shared hit=11
6. 0.031 0.031 ↑ 1.0 269 1

Seq Scan on public.line_sensors ls (cost=0.00..5.69 rows=269 width=24) (actual time=0.007..0.031 rows=269 loops=1)

  • Output: ls.id, ls.sensor_id, ls.line_id, ls.connected_from, ls.connected_to, ls.archived, ls.connected
  • Buffers: shared hit=3
7. 0.003 0.014 ↑ 1.0 2 1

Hash (cost=14.23..14.23 rows=2 width=8) (actual time=0.013..0.014 rows=2 loops=1)

  • Output: h_1.sensor_id, h_1.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=8
8. 0.011 0.011 ↑ 1.0 2 1

Index Scan using heartbeats_pkey on public.heartbeats h_1 (cost=0.42..14.23 rows=2 width=8) (actual time=0.008..0.011 rows=2 loops=1)

  • Output: h_1.sensor_id, h_1.id
  • Index Cond: (h_1.id = ANY ('{826217,826218}'::integer[]))
  • Buffers: shared hit=8
9. 13.462 13.462 ↓ 277.9 10,281 2

Index Scan using heartbeats_sensor_receivedat on public.heartbeats h (cost=0.42..589.71 rows=37 width=96) (actual time=0.008..6.731 rows=10,281 loops=2)

  • Output: h.id, h.gateway_id, h.bru_id, h.sensor_id, h.sensor_temp_c, h.sensor_ts, h.sensor_ml, h.sensor_vfr, h.sensor_flags, h.sensor_error, h.sensor_sig, h.sensor_cdtof, h.bru_ts, h.bru_temp_c, h.bru_baro, h.bru_hum, h.received_at, h.processed_window_at, h.bru_addr, h.sensor_addr, h.processed_missing_at
  • Index Cond: ((h.sensor_id = ls.sensor_id) AND (h.sensor_id IS NOT NULL))
  • Filter: (h.received_at <@ ls.connected)
  • Buffers: shared hit=19,327
10. 164.496 164.496 ↓ 2.0 30 20,562

Index Scan using item_lines_line on public.item_lines il (cost=0.28..1.18 rows=15 width=25) (actual time=0.001..0.008 rows=30 loops=20,562)

  • Output: il.id, il.item_id, il.line_id, il.connected_from, il.connected_to, il.queue_index, il.archived, il.connected
  • Index Cond: (il.line_id = ls.line_id)
  • Filter: ((il.connected_to IS NOT NULL) OR (il.queue_index = '0'::double precision))
  • Buffers: shared hit=298,149
Planning time : 0.753 ms
Execution time : 365.108 ms