explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cfwV : Optimization for: plan #iLaV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 55.881 220.319 ↓ 10,248.0 10,248 1

WindowAgg (cost=609.14..609.17 rows=1 width=76) (actual time=163.783..220.319 rows=10,248 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=87,729 read=4,197
2. 5.452 164.438 ↓ 10,248.0 10,248 1

Sort (cost=609.14..609.15 rows=1 width=96) (actual time=163.751..164.438 rows=10,248 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: 1,826kB
  • Buffers: shared hit=87,729 read=4,197
3. 33.743 158.986 ↓ 10,248.0 10,248 1

Nested Loop (cost=9.16..609.13 rows=1 width=96) (actual time=1.424..158.986 rows=10,248 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: 133,686
  • Buffers: shared hit=87,729 read=4,197
4. 2.826 73.838 ↓ 3,427.0 10,281 1

Nested Loop (cost=8.88..605.03 rows=3 width=100) (actual time=0.079..73.838 rows=10,281 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=5,483 read=4,195
5. 0.054 0.105 ↑ 1.0 1 1

Hash Join (cost=8.46..14.86 rows=1 width=32) (actual time=0.062..0.105 rows=1 loops=1)

  • Output: ls.sensor_id, ls.connected, ls.line_id, h_1.sensor_id, h_1.id
  • Inner Unique: true
  • Hash Cond: (ls.sensor_id = h_1.sensor_id)
  • Buffers: shared hit=7
6. 0.035 0.035 ↑ 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.035 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.016 ↑ 1.0 1 1

Hash (cost=8.44..8.44 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)

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

Index Scan using heartbeats_pkey on public.heartbeats h_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1)

  • Output: h_1.sensor_id, h_1.id
  • Index Cond: (h_1.id = 826,217)
  • Buffers: shared hit=4
9. 70.907 70.907 ↓ 277.9 10,281 1

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

  • 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=5,476 read=4,195
10. 51.405 51.405 ↑ 1.1 14 10,281

Index Scan using item_lines_line on public.item_lines il (cost=0.28..1.18 rows=15 width=25) (actual time=0.002..0.005 rows=14 loops=10,281)

  • 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=82,246 read=2
Planning time : 0.837 ms
Execution time : 221.016 ms