explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HUoZ

Settings
# exclusive inclusive rows x rows loops node
1. 1.041 32,747.018 ↓ 630.0 630 1

Nested Loop (cost=47,413.36..47,414.42 rows=1 width=316) (actual time=9,045.557..32,747.018 rows=630 loops=1)

  • Output: st.id, st.alert_caption, g.establishment_id, h.sensor_id, st_1.ids, u1.measure, (max(h.id)), (min(h.id)), (max(h.received_at)), (min(h.received_at)), ((sum(CASE WHEN ((rank() OVER (?)) = 1) THEN ((((to_json(h.*)) -> u1.measure))::text)::numeric ELSE '0'::numeric END))::double precision), ((max((rank() OVER (?))))::double precision), (sum(((((to_json(h.*)) -> u1.measure))::text)::numeric)), (sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '2'::numeric))), (sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '3'::numeric))), (sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '4'::numeric))), d.mean_x, d.stdevp_x, d.z_x, d.skewp_x, d.ekurtosis_x
  • Buffers: shared hit=15,359 read=9,876, temp read=150,848 written=53,665
2.          

CTE st

3. 0.027 0.027 ↓ 8.0 8 1

Values Scan on "*VALUES*" (cost=0.00..0.18 rows=1 width=125) (actual time=0.017..0.027 rows=8 loops=1)

  • Output: "*VALUES*".column1, "*VALUES*".column2, "*VALUES*".column3, "*VALUES*".column4, "*VALUES*".column5, "*VALUES*".column6, "*VALUES*".column7, "*VALUES*".column8
  • Filter: (("*VALUES*".column8 = 'heartbeat'::text) AND ((now())::timestamp without time zone <@ "*VALUES*".column4))
4. 2.121 32,719.517 ↓ 630.0 630 1

Nested Loop (cost=47,412.92..47,413.96 rows=1 width=276) (actual time=9,045.261..32,719.517 rows=630 loops=1)

  • Output: g.establishment_id, h.sensor_id, st_1.ids, u1.measure, (max(h.id)), (min(h.id)), (max(h.received_at)), (min(h.received_at)), ((sum(CASE WHEN ((rank() OVER (?)) = 1) THEN ((((to_json(h.*)) -> u1.measure))::text)::numeric ELSE '0'::numeric END))::double precision), ((max((rank() OVER (?))))::double precision), (sum(((((to_json(h.*)) -> u1.measure))::text)::numeric)), (sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '2'::numeric))), (sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '3'::numeric))), (sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '4'::numeric))), st.id, st.alert_caption
  • Join Filter: (st.id = ANY (st_1.ids))
  • Rows Removed by Join Filter: 4,410
  • Buffers: shared hit=15,359 read=9,876, temp read=150,848 written=53,665
5. 0.028 0.028 ↓ 8.0 8 1

CTE Scan on st (cost=0.00..0.02 rows=1 width=36) (actual time=0.019..0.028 rows=8 loops=1)

  • Output: st.id, st.establishment_id, st.alert_caption, st.effective, st.window_count, st.window_duration, st.window_and, st.type
6. 23,456.840 32,717.368 ↓ 105.0 630 8

GroupAggregate (cost=47,412.92..47,413.74 rows=6 width=240) (actual time=1,103.992..4,089.671 rows=630 loops=8)

  • Output: g.establishment_id, h.sensor_id, st_1.ids, u1.measure, max(h.id), min(h.id), max(h.received_at), min(h.received_at), (sum(CASE WHEN ((rank() OVER (?)) = 1) THEN ((((to_json(h.*)) -> u1.measure))::text)::numeric ELSE '0'::numeric END))::double precision, (max((rank() OVER (?))))::double precision, sum(((((to_json(h.*)) -> u1.measure))::text)::numeric), sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '2'::numeric)), sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '3'::numeric)), sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '4'::numeric))
  • Group Key: h.sensor_id, st_1.ids, u1.measure, g.establishment_id
  • Buffers: shared hit=15,359 read=9,876, temp read=150,841 written=53,665
7. 1,075.480 9,260.528 ↓ 34,153.0 204,918 8

Sort (cost=47,412.92..47,412.93 rows=6 width=124) (actual time=1,102.543..1,157.566 rows=204,918 loops=8)

  • Output: g.establishment_id, h.sensor_id, st_1.ids, u1.measure, h.id, h.received_at, (rank() OVER (?)), (to_json(h.*))
  • Sort Key: h.sensor_id, st_1.ids, u1.measure, g.establishment_id
  • Sort Method: external sort Disk: 111,208kB
  • Buffers: shared hit=15,359 read=9,876, temp read=150,841 written=53,665
8. 49.815 8,185.048 ↓ 34,153.0 204,918 1

Nested Loop (cost=47,412.55..47,412.84 rows=6 width=124) (actual time=4,218.465..8,185.048 rows=204,918 loops=1)

  • Output: g.establishment_id, h.sensor_id, st_1.ids, u1.measure, h.id, h.received_at, (rank() OVER (?)), (to_json(h.*))
  • Buffers: shared hit=15,359 read=9,876, temp read=25,827 written=25,853
9. 148.597 8,135.233 ↓ 34,153.0 34,153 1

Hash Join (cost=47,412.54..47,412.72 rows=1 width=92) (actual time=4,218.458..8,135.233 rows=34,153 loops=1)

  • Output: h.sensor_id, h.id, h.received_at, (rank() OVER (?)), (to_json(h.*)), g.establishment_id, st_1.ids
  • Hash Cond: (h.gateway_id = g.id)
  • Join Filter: CASE WHEN st_1.window_and THEN (((rank() OVER (?)) <= st_1.window_count) AND (((h0.received_at - h.received_at)) <= st_1.window_duration)) ELSE (((rank() OVER (?)) <= st_1.window_count) OR (((h0.received_at - h.received_at)) <= st_1.window_duration)) END
  • Rows Removed by Join Filter: 691,412
  • Buffers: shared hit=15,359 read=9,876, temp read=25,827 written=25,853
10. 3,631.938 7,986.576 ↓ 181,391.2 725,565 1

WindowAgg (cost=47,408.11..47,408.21 rows=4 width=76) (actual time=4,218.387..7,986.576 rows=725,565 loops=1)

  • Output: h.id, h.gateway_id, h.sensor_id, h.received_at, (h0.received_at - h.received_at), rank() OVER (?), to_json(h.*)
  • Buffers: shared hit=15,355 read=9,876, temp read=25,827 written=25,853
11.          

CTE h

12. 1,387.446 1,858.017 ↓ 65,960.5 725,565 1

Hash Join (cost=104.80..47,407.11 rows=11 width=96) (actual time=19.398..1,858.017 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
  • Hash Cond: (ls.line_id = il.line_id)
  • Join Filter: (h_2.received_at <@ il.connected)
  • Rows Removed by Join Filter: 11,851,650
  • Buffers: shared hit=15,355 read=9,876
13. 322.582 469.599 ↓ 998.9 824,102 1

Hash Join (cost=9.05..46,871.39 rows=825 width=100) (actual time=18.418..469.599 rows=824,102 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, 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: 1,164
  • Buffers: shared hit=15,326 read=9,876
14. 146.910 146.910 ↑ 1.0 825,270 1

Seq Scan on public.heartbeats h_2 (cost=0.00..33,451.70 rows=825,270 width=96) (actual time=18.285..146.910 rows=825,270 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
  • Filter: (h_2.sensor_id IS NOT NULL)
  • Buffers: shared hit=15,323 read=9,876
15. 0.052 0.107 ↑ 1.0 269 1

Hash (cost=5.69..5.69 rows=269 width=24) (actual time=0.106..0.107 rows=269 loops=1)

  • Output: ls.sensor_id, ls.connected, ls.line_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=3
16. 0.055 0.055 ↑ 1.0 269 1

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

  • Output: ls.sensor_id, ls.connected, ls.line_id
  • Buffers: shared hit=3
17. 0.443 0.972 ↓ 1.1 2,715 1

Hash (cost=63.59..63.59 rows=2,573 width=25) (actual time=0.972..0.972 rows=2,715 loops=1)

  • Output: il.connected, il.line_id
  • Buckets: 4,096 Batches: 1 Memory Usage: 190kB
  • Buffers: shared hit=29
18. 0.529 0.529 ↓ 1.1 2,715 1

Seq Scan on public.item_lines il (cost=0.00..63.59 rows=2,573 width=25) (actual time=0.007..0.529 rows=2,715 loops=1)

  • 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=29
19. 785.374 4,354.638 ↓ 181,391.2 725,565 1

Sort (cost=1.00..1.01 rows=4 width=72) (actual time=4,218.362..4,354.638 rows=725,565 loops=1)

  • Output: h.id, h.sensor_id, h.gateway_id, h.received_at, h0.received_at, h.*
  • Sort Key: h.sensor_id, h.id DESC
  • Sort Method: external merge Disk: 113,032kB
  • Buffers: shared hit=15,355 read=9,876, temp read=25,827 written=25,853
20. 193.148 3,569.264 ↓ 181,391.2 725,565 1

Hash Join (cost=0.71..0.96 rows=4 width=72) (actual time=2,936.293..3,569.264 rows=725,565 loops=1)

  • Output: h.id, h.sensor_id, h.gateway_id, h.received_at, h0.received_at, h.*
  • Inner Unique: true
  • Hash Cond: (h.sensor_id = h0.sensor_id)
  • Join Filter: (h.id <= h0.id)
  • Buffers: shared hit=15,355 read=9,876, temp read=11,698 written=11,703
21. 459.239 459.239 ↓ 65,960.5 725,565 1

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

  • Output: h.id, h.gateway_id, h.sensor_id, h.received_at, h.*
  • Buffers: shared hit=8,139 read=4,590, temp read=9,389 written=1
22. 0.050 2,916.877 ↓ 9.5 105 1

Hash (cost=0.58..0.58 rows=11 width=16) (actual time=2,916.876..2,916.877 rows=105 loops=1)

  • Output: h0.received_at, h0.sensor_id, h0.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=7,216 read=5,286, temp read=2,309 written=11,702
23. 0.032 2,916.827 ↓ 9.5 105 1

Subquery Scan on h0 (cost=0.41..0.58 rows=11 width=16) (actual time=2,764.150..2,916.827 rows=105 loops=1)

  • Output: h0.received_at, h0.sensor_id, h0.id
  • Buffers: shared hit=7,216 read=5,286, temp read=2,309 written=11,702
24. 61.339 2,916.795 ↓ 9.5 105 1

Unique (cost=0.41..0.47 rows=11 width=16) (actual time=2,764.148..2,916.795 rows=105 loops=1)

  • Output: h_1.id, h_1.sensor_id, h_1.received_at
  • Buffers: shared hit=7,216 read=5,286, temp read=2,309 written=11,702
25. 469.010 2,855.456 ↓ 65,960.5 725,565 1

Sort (cost=0.41..0.44 rows=11 width=16) (actual time=2,764.147..2,855.456 rows=725,565 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: external merge Disk: 18,472kB
  • Buffers: shared hit=7,216 read=5,286, temp read=2,309 written=11,702
26. 2,386.446 2,386.446 ↓ 65,960.5 725,565 1

CTE Scan on h h_1 (cost=0.00..0.22 rows=11 width=16) (actual time=0.001..2,386.446 rows=725,565 loops=1)

  • Output: h_1.id, h_1.sensor_id, h_1.received_at
  • Buffers: shared hit=7,216 read=5,286, temp written=9,388
27. 0.003 0.060 ↓ 9.0 9 1

Hash (cost=4.42..4.42 rows=1 width=61) (actual time=0.058..0.060 rows=9 loops=1)

  • Output: g.establishment_id, g.id, st_1.ids, st_1.window_and, st_1.window_count, st_1.window_duration
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4
28. 0.014 0.057 ↓ 9.0 9 1

Hash Join (cost=0.07..4.42 rows=1 width=61) (actual time=0.046..0.057 rows=9 loops=1)

  • Output: g.establishment_id, g.id, st_1.ids, st_1.window_and, st_1.window_count, st_1.window_duration
  • Hash Cond: (g.establishment_id = st_1.establishment_id)
  • Buffers: shared hit=4
29. 0.011 0.011 ↑ 1.0 25 1

Seq Scan on public.gateways g (cost=0.00..4.25 rows=25 width=8) (actual time=0.006..0.011 rows=25 loops=1)

  • Output: g.id, g.establishment_id, g.identifier, g.certificate, g.last_data_at, g.archived, g.certificate_fingerprint256
  • Buffers: shared hit=4
30. 0.004 0.032 ↓ 8.0 8 1

Hash (cost=0.06..0.06 rows=1 width=57) (actual time=0.031..0.032 rows=8 loops=1)

  • Output: st_1.ids, st_1.establishment_id, st_1.window_and, st_1.window_count, st_1.window_duration
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.002 0.028 ↓ 8.0 8 1

Subquery Scan on st_1 (cost=0.03..0.06 rows=1 width=57) (actual time=0.024..0.028 rows=8 loops=1)

  • Output: st_1.ids, st_1.establishment_id, st_1.window_and, st_1.window_count, st_1.window_duration
32. 0.012 0.026 ↓ 8.0 8 1

HashAggregate (cost=0.03..0.05 rows=1 width=57) (actual time=0.023..0.026 rows=8 loops=1)

  • Output: st_2.establishment_id, st_2.window_count, st_2.window_duration, st_2.window_and, array_agg(st_2.id)
  • Group Key: st_2.establishment_id, st_2.window_count, st_2.window_duration, st_2.window_and
33. 0.014 0.014 ↓ 8.0 8 1

CTE Scan on st st_2 (cost=0.00..0.02 rows=1 width=29) (actual time=0.000..0.014 rows=8 loops=1)

  • Output: st_2.id, st_2.establishment_id, st_2.alert_caption, st_2.effective, st_2.window_count, st_2.window_duration, st_2.window_and, st_2.type
34. 0.000 0.000 ↑ 1.0 6 34,153

Function Scan on pg_catalog.unnest u1 (cost=0.00..0.06 rows=6 width=32) (actual time=0.000..0.000 rows=6 loops=34,153)

  • Output: u1.measure
  • Function Call: unnest('{sensor_temp_c,sensor_sig,sensor_cdtof,bru_temp_c,bru_baro,bru_hum}'::text[])
35. 26.460 26.460 ↑ 1.0 1 630

Function Scan on public.distribution_statistics d (cost=0.26..0.27 rows=1 width=40) (actual time=0.042..0.042 rows=1 loops=630)

  • Output: d.mean_x, d.stdevp_x, d.z_x, d.skewp_x, d.ekurtosis_x
  • Function Call: distribution_statistics((((max((rank() OVER (?))))::double precision))::integer, ((sum(CASE WHEN ((rank() OVER (?)) = 1) THEN ((((to_json(h.*)) -> u1.measure))::text)::numeric ELSE '0'::numeric END))::double precision), ((sum(((((to_json(h.*)) -> u1.measure))::text)::numeric)))::double precision, ((sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '2'::numeric))))::double precision, ((sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '3'::numeric))))::double precision, ((sum((((((to_json(h.*)) -> u1.measure))::text)::numeric ^ '4'::numeric))))::double precision)
Planning time : 1.104 ms
Execution time : 32,799.528 ms