explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iLaV

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 60.662 1,379.667 ↓ 10,248.0 10,248 1

WindowAgg (cost=35,515.29..35,515.32 rows=1 width=76) (actual time=1,318.051..1,379.667 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(h.*)
  • Buffers: shared hit=16,129 read=9,336, temp read=9,389 written=9,389
2.          

CTE h

3. 0.000 568.043 ↓ 65,960.5 725,565 1

Gather (cost=1,104.80..35,514.74 rows=11 width=96) (actual time=16.876..568.043 rows=725,565 loops=1)

  • Output: h_2.id, h_2.gateway_id, h_2.bru_id, h_2.sensor_id, h_2.sensor_temp_c, h_2.sensor_ts, h_2.sensor_ml, h_2.sensor_vfr, h_2.sensor_flags, h_2.sensor_error, h_2.sensor_sig, h_2.sensor_cdtof, h_2.bru_ts, h_2.bru_temp_c, h_2.bru_baro, h_2.bru_hum, h_2.received_at, h_2.processed_window_at, h_2.bru_addr, h_2.sensor_addr, h_2.processed_missing_at
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=16,129 read=9,336
4. 470.213 647.859 ↓ 48,371.0 241,855 3 / 3

Hash Join (cost=104.80..34,513.64 rows=5 width=96) (actual time=12.402..647.859 rows=241,855 loops=3)

  • Output: h_2.id, h_2.gateway_id, h_2.bru_id, h_2.sensor_id, h_2.sensor_temp_c, h_2.sensor_ts, h_2.sensor_ml, h_2.sensor_vfr, h_2.sensor_flags, h_2.sensor_error, h_2.sensor_sig, h_2.sensor_cdtof, h_2.bru_ts, h_2.bru_temp_c, h_2.bru_baro, h_2.bru_hum, h_2.received_at, h_2.processed_window_at, h_2.bru_addr, h_2.sensor_addr, h_2.processed_missing_at
  • Hash Cond: (ls.line_id = il.line_id)
  • Join Filter: (h_2.received_at <@ il.connected)
  • Rows Removed by Join Filter: 3,950,550
  • Buffers: shared hit=16,129 read=9,336
  • Worker 0: actual time=10.431..744.582 rows=282,201 loops=1
  • Buffers: shared hit=4,149 read=2,204
  • Worker 1: actual time=10.274..745.338 rows=281,490 loops=1
  • Buffers: shared hit=4,147 read=2,172
5. 110.379 176.427 ↓ 798.5 274,701 3 / 3

Hash Join (cost=9.05..34,234.43 rows=344 width=100) (actual time=11.148..176.427 rows=274,701 loops=3)

  • Output: h_2.id, h_2.gateway_id, h_2.bru_id, h_2.sensor_id, h_2.sensor_temp_c, h_2.sensor_ts, h_2.sensor_ml, h_2.sensor_vfr, h_2.sensor_flags, h_2.sensor_error, h_2.sensor_sig, h_2.sensor_cdtof, h_2.bru_ts, h_2.bru_temp_c, h_2.bru_baro, h_2.bru_hum, h_2.received_at, h_2.processed_window_at, h_2.bru_addr, h_2.sensor_addr, h_2.processed_missing_at, ls.line_id
  • Hash Cond: (h_2.sensor_id = ls.sensor_id)
  • Join Filter: (h_2.received_at <@ ls.connected)
  • Rows Removed by Join Filter: 388
  • Buffers: shared hit=16,042 read=9,336
  • Worker 0: actual time=8.974..201.428 rows=320,825 loops=1
  • Buffers: shared hit=4,120 read=2,204
  • Worker 1: actual time=8.978..201.593 rows=319,482 loops=1
  • Buffers: shared hit=4,118 read=2,172
6. 65.876 65.876 ↑ 1.2 275,090 3 / 3

Parallel Seq Scan on public.heartbeats h_2 (cost=0.00..28,637.62 rows=343,862 width=96) (actual time=10.518..65.876 rows=275,090 loops=3)

  • Output: h_2.id, h_2.gateway_id, h_2.bru_id, h_2.sensor_id, h_2.sensor_temp_c, h_2.sensor_ts, h_2.sensor_ml, h_2.sensor_vfr, h_2.sensor_flags, h_2.sensor_error, h_2.sensor_sig, h_2.sensor_cdtof, h_2.bru_ts, h_2.bru_temp_c, h_2.bru_baro, h_2.bru_hum, h_2.received_at, h_2.processed_window_at, h_2.bru_addr, h_2.sensor_addr, h_2.processed_missing_at
  • Filter: (h_2.sensor_id IS NOT NULL)
  • Buffers: shared hit=15,864 read=9,335
  • Worker 0: actual time=8.094..73.303 rows=321,319 loops=1
  • Buffers: shared hit=4,033 read=2,203
  • Worker 1: actual time=8.089..73.862 rows=319,877 loops=1
  • Buffers: shared hit=4,030 read=2,172
7. 0.078 0.172 ↑ 1.0 269 3 / 3

Hash (cost=5.69..5.69 rows=269 width=24) (actual time=0.171..0.172 rows=269 loops=3)

  • Output: ls.sensor_id, ls.connected, ls.line_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=9
  • Worker 0: actual time=0.199..0.199 rows=269 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual time=0.211..0.212 rows=269 loops=1
  • Buffers: shared hit=3
8. 0.094 0.094 ↑ 1.0 269 3 / 3

Seq Scan on public.line_sensors ls (cost=0.00..5.69 rows=269 width=24) (actual time=0.044..0.094 rows=269 loops=3)

  • Output: ls.sensor_id, ls.connected, ls.line_id
  • Buffers: shared hit=9
  • Worker 0: actual time=0.056..0.109 rows=269 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual time=0.066..0.118 rows=269 loops=1
  • Buffers: shared hit=3
9. 0.553 1.219 ↓ 1.1 2,715 3 / 3

Hash (cost=63.59..63.59 rows=2,573 width=25) (actual time=1.218..1.219 rows=2,715 loops=3)

  • Output: il.connected, il.line_id
  • Buckets: 4,096 Batches: 1 Memory Usage: 190kB
  • Buffers: shared hit=87
  • Worker 0: actual time=1.415..1.415 rows=2,715 loops=1
  • Buffers: shared hit=29
  • Worker 1: actual time=1.242..1.243 rows=2,715 loops=1
  • Buffers: shared hit=29
10. 0.666 0.666 ↓ 1.1 2,715 3 / 3

Seq Scan on public.item_lines il (cost=0.00..63.59 rows=2,573 width=25) (actual time=0.033..0.666 rows=2,715 loops=3)

  • Output: il.connected, il.line_id
  • Filter: ((il.connected_to IS NOT NULL) OR (il.queue_index = '0'::double precision))
  • Rows Removed by Filter: 52
  • Buffers: shared hit=87
  • Worker 0: actual time=0.045..0.748 rows=2,715 loops=1
  • Buffers: shared hit=29
  • Worker 1: actual time=0.049..0.708 rows=2,715 loops=1
  • Buffers: shared hit=29
11. 6.048 1,319.005 ↓ 10,248.0 10,248 1

Sort (cost=0.55..0.55 rows=1 width=64) (actual time=1,318.026..1,319.005 rows=10,248 loops=1)

  • Output: h.sensor_id, h.received_at, h.id, h.gateway_id, h.*
  • Sort Key: h.sensor_id, h.received_at DESC
  • Sort Method: quicksort Memory: 3,055kB
  • Buffers: shared hit=16,129 read=9,336, temp read=9,389 written=9,389
12. 66.004 1,312.957 ↓ 10,248.0 10,248 1

Hash Join (cost=0.29..0.54 rows=1 width=64) (actual time=868.081..1,312.957 rows=10,248 loops=1)

  • Output: h.sensor_id, h.received_at, h.id, h.gateway_id, h.*
  • Inner Unique: true
  • Hash Cond: (h.sensor_id = h0.sensor_id)
  • Join Filter: (h.id <= h0.id)
  • Buffers: shared hit=16,129 read=9,336, temp read=9,389 written=9,389
13. 395.823 395.823 ↓ 65,960.5 725,565 1

CTE Scan on h (cost=0.00..0.22 rows=11 width=64) (actual time=16.884..395.823 rows=725,565 loops=1)

  • Output: h.id, h.gateway_id, h.sensor_id, h.received_at, h.*
  • Buffers: shared hit=6,095 read=3,910, temp read=9,389 written=1
14. 0.002 851.130 ↑ 1.0 1 1

Hash (cost=0.28..0.28 rows=1 width=8) (actual time=851.128..851.130 rows=1 loops=1)

  • Output: h0.sensor_id, h0.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=10,034 read=5,426, temp written=9,388
15. 0.003 851.128 ↑ 1.0 1 1

Subquery Scan on h0 (cost=0.26..0.28 rows=1 width=8) (actual time=851.125..851.128 rows=1 loops=1)

  • Output: h0.sensor_id, h0.id
  • Buffers: shared hit=10,034 read=5,426, temp written=9,388
16. 0.002 851.125 ↑ 1.0 1 1

Unique (cost=0.26..0.27 rows=1 width=16) (actual time=851.124..851.125 rows=1 loops=1)

  • Output: h_1.id, h_1.sensor_id, h_1.received_at
  • Buffers: shared hit=10,034 read=5,426, temp written=9,388
17. 0.005 851.123 ↑ 1.0 1 1

Sort (cost=0.26..0.26 rows=1 width=16) (actual time=851.123..851.123 rows=1 loops=1)

  • Output: h_1.id, h_1.sensor_id, h_1.received_at
  • Sort Key: h_1.sensor_id, h_1.received_at DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=10,034 read=5,426, temp written=9,388
18. 851.118 851.118 ↑ 1.0 1 1

CTE Scan on h h_1 (cost=0.00..0.25 rows=1 width=16) (actual time=0.165..851.118 rows=1 loops=1)

  • Output: h_1.id, h_1.sensor_id, h_1.received_at
  • Filter: (h_1.id = 826,217)
  • Rows Removed by Filter: 725,564
  • Buffers: shared hit=10,034 read=5,426, temp written=9,388
Planning time : 0.472 ms
Execution time : 1,385.900 ms