explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jD7b : Optimization for: plan #SEFl

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.181 65,905.558 ↓ 630.0 630 1

Nested Loop (cost=47,424.64..47,425.70 rows=1 width=312) (actual time=40,295.226..65,905.558 rows=630 loops=1)

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)

  • Filter: ((column8 = 'heartbeat'::text) AND ((now())::timestamp without time zone <@ column4))
4. 2.214 65,876.657 ↓ 630.0 630 1

Nested Loop (cost=47,424.20..47,425.24 rows=1 width=272) (actual time=40,294.846..65,876.657 rows=630 loops=1)

  • Join Filter: (st.id = ANY ((array_agg(st_1.id))))
  • Rows Removed by Join Filter: 4,410
5. 0.027 0.027 ↓ 8.0 8 1

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

6. 25,198.168 65,874.416 ↓ 105.0 630 8

GroupAggregate (cost=47,424.20..47,425.03 rows=6 width=240) (actual time=5,115.020..8,234.302 rows=630 loops=8)

  • Group Key: g.establishment_id, h.sensor_id, (array_agg(st_1.id)), u1.measure
7. 1,346.575 40,676.248 ↓ 34,153.0 204,918 8

Sort (cost=47,424.20..47,424.22 rows=6 width=124) (actual time=5,023.555..5,084.531 rows=204,918 loops=8)

  • Sort Key: g.establishment_id, h.sensor_id, (array_agg(st_1.id)), u1.measure
  • Sort Method: external sort Disk: 111,184kB
8. 25.734 39,329.673 ↓ 34,153.0 204,918 1

Nested Loop (cost=47,408.70..47,424.12 rows=6 width=124) (actual time=6,204.050..39,329.673 rows=204,918 loops=1)

9. 43.910 39,269.786 ↓ 34,153.0 34,153 1

Nested Loop (cost=47,408.70..47,424.00 rows=1 width=92) (actual time=6,204.042..39,269.786 rows=34,153 loops=1)

  • Join Filter: (st_1.establishment_id = g.establishment_id)
  • Rows Removed by Join Filter: 222,223
10. 33.643 38,969.500 ↓ 128,188.0 256,376 1

Nested Loop (cost=47,408.56..47,423.39 rows=2 width=96) (actual time=4,993.272..38,969.500 rows=256,376 loops=1)

11. 134.550 38,679.481 ↓ 128,188.0 256,376 1

Nested Loop (cost=47,408.42..47,423.01 rows=2 width=96) (actual time=4,993.266..38,679.481 rows=256,376 loops=1)

12. 541.306 38,288.555 ↓ 128,188.0 256,376 1

Nested Loop (cost=47,408.14..47,408.40 rows=2 width=92) (actual time=4,993.249..38,288.555 rows=256,376 loops=1)

  • Join Filter: CASE WHEN st_1.window_flag 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: 5,548,144
13. 0.044 0.073 ↓ 8.0 8 1

GroupAggregate (cost=0.03..0.06 rows=1 width=57) (actual time=0.031..0.073 rows=8 loops=1)

  • Group Key: st_1.establishment_id, st_1.window_count, st_1.window_duration, st_1.window_flag
14. 0.014 0.029 ↓ 8.0 8 1

Sort (cost=0.03..0.04 rows=1 width=29) (actual time=0.025..0.029 rows=8 loops=1)

  • Sort Key: st_1.establishment_id, st_1.window_count, st_1.window_duration, st_1.window_flag
  • Sort Method: quicksort Memory: 25kB
15. 0.015 0.015 ↓ 8.0 8 1

CTE Scan on st st_1 (cost=0.00..0.02 rows=1 width=29) (actual time=0.001..0.015 rows=8 loops=1)

16. 31,758.248 37,747.176 ↓ 181,391.2 725,565 8

WindowAgg (cost=47,408.11..47,408.21 rows=4 width=72) (actual time=624.160..4,718.397 rows=725,565 loops=8)

17.          

CTE h

18. 1,628.083 2,182.053 ↓ 65,960.5 725,565 1

Hash Join (cost=104.80..47,407.11 rows=11 width=96) (actual time=19.694..2,182.053 rows=725,565 loops=1)

  • Hash Cond: (ls.line_id = il.line_id)
  • Join Filter: (h_2.received_at <@ il.connected)
  • Rows Removed by Join Filter: 11,851,650
19. 377.467 552.966 ↓ 998.9 824,102 1

Hash Join (cost=9.05..46,871.39 rows=825 width=100) (actual time=18.682..552.966 rows=824,102 loops=1)

  • Hash Cond: (h_2.sensor_id = ls.sensor_id)
  • Join Filter: (h_2.received_at <@ ls.connected)
  • Rows Removed by Join Filter: 1,164
20. 175.395 175.395 ↑ 1.0 825,270 1

Seq Scan on heartbeats h_2 (cost=0.00..33,451.70 rows=825,270 width=96) (actual time=18.547..175.395 rows=825,270 loops=1)

  • Filter: (sensor_id IS NOT NULL)
21. 0.048 0.104 ↑ 1.0 269 1

Hash (cost=5.69..5.69 rows=269 width=24) (actual time=0.103..0.104 rows=269 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
22. 0.056 0.056 ↑ 1.0 269 1

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

23. 0.450 1.004 ↓ 1.1 2,715 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 190kB
24. 0.554 0.554 ↓ 1.1 2,715 1

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

  • Filter: ((connected_to IS NOT NULL) OR (queue_index = '0'::double precision))
  • Rows Removed by Filter: 52
25. 1,959.097 5,988.928 ↓ 181,391.2 725,565 8

Sort (cost=1.00..1.01 rows=4 width=64) (actual time=624.149..748.616 rows=725,565 loops=8)

  • Sort Key: h.sensor_id, h.id DESC
  • Sort Method: external sort Disk: 110,136kB
26. 195.306 4,029.831 ↓ 181,391.2 725,565 1

Hash Join (cost=0.71..0.96 rows=4 width=64) (actual time=3,375.990..4,029.831 rows=725,565 loops=1)

  • Hash Cond: (h.sensor_id = h0.sensor_id)
  • Join Filter: (h.id <= h0.id)
27. 478.245 478.245 ↓ 65,960.5 725,565 1

CTE Scan on h (cost=0.00..0.22 rows=11 width=56) (actual time=19.702..478.245 rows=725,565 loops=1)

28. 0.098 3,356.280 ↓ 9.5 105 1

Hash (cost=0.58..0.58 rows=11 width=16) (actual time=3,356.279..3,356.280 rows=105 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
29. 0.054 3,356.182 ↓ 9.5 105 1

Subquery Scan on h0 (cost=0.41..0.58 rows=11 width=16) (actual time=3,173.470..3,356.182 rows=105 loops=1)

30. 68.638 3,356.128 ↓ 9.5 105 1

Unique (cost=0.41..0.47 rows=11 width=16) (actual time=3,173.468..3,356.128 rows=105 loops=1)

31. 480.114 3,287.490 ↓ 65,960.5 725,565 1

Sort (cost=0.41..0.44 rows=11 width=16) (actual time=3,173.467..3,287.490 rows=725,565 loops=1)

  • Sort Key: h_1.sensor_id, h_1.received_at DESC
  • Sort Method: external merge Disk: 18,512kB
32. 2,807.376 2,807.376 ↓ 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,807.376 rows=725,565 loops=1)

33. 256.376 256.376 ↑ 1.0 1 256,376

Index Scan using sensors_pkey on sensors s (cost=0.28..7.29 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=256,376)

  • Index Cond: (id = h.sensor_id)
34. 256.376 256.376 ↑ 1.0 1 256,376

Index Scan using brus_pkey on brus b (cost=0.14..0.19 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=256,376)

  • Index Cond: (id = s.bru_id)
35. 256.376 256.376 ↑ 1.0 1 256,376

Index Scan using gateways_pkey on gateways g (cost=0.14..0.29 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=256,376)

  • Index Cond: (id = b.gateway_id)
36. 34.153 34.153 ↑ 1.0 6 34,153

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

37. 27.720 27.720 ↑ 1.0 1 630

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

Planning time : 1.972 ms
Execution time : 65,922.669 ms