explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Icr7 : Optimization for: plan #SEFl

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.467 35,450.611 ↓ 630.0 630 1

Nested Loop (cost=47,413.36..47,414.42 rows=1 width=316) (actual time=9,594.787..35,450.611 rows=630 loops=1)

2.          

CTE st

3. 0.017 0.017 ↓ 8.0 8 1

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

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

Nested Loop (cost=47,412.92..47,413.96 rows=1 width=276) (actual time=9,594.363..35,420.164 rows=630 loops=1)

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

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

6. 25,425.840 35,417.592 ↓ 105.0 630 8

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

  • Group Key: g.establishment_id, h.sensor_id, st_1.ids, u1.measure
7. 1,271.243 9,991.752 ↓ 34,153.0 204,918 8

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

  • Sort Key: g.establishment_id, h.sensor_id, st_1.ids, u1.measure
  • Sort Method: external sort Disk: 111,184kB
8. 22.445 8,720.509 ↓ 34,153.0 204,918 1

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

9. 153.990 8,663.911 ↓ 34,153.0 34,153 1

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

  • 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
10. 3,804.043 8,509.830 ↓ 181,391.2 725,565 1

WindowAgg (cost=47,408.11..47,408.21 rows=4 width=76) (actual time=4,569.630..8,509.830 rows=725,565 loops=1)

11.          

CTE h

12. 1,635.513 2,192.017 ↓ 65,960.5 725,565 1

Hash Join (cost=104.80..47,407.11 rows=11 width=96) (actual time=18.720..2,192.017 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
13. 377.944 555.335 ↓ 998.9 824,102 1

Hash Join (cost=9.05..46,871.39 rows=825 width=100) (actual time=17.480..555.335 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
14. 177.244 177.244 ↑ 1.0 825,270 1

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

  • Filter: (sensor_id IS NOT NULL)
15. 0.068 0.147 ↑ 1.0 269 1

Hash (cost=5.69..5.69 rows=269 width=24) (actual time=0.146..0.147 rows=269 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
16. 0.079 0.079 ↑ 1.0 269 1

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

17. 0.532 1.169 ↓ 1.1 2,715 1

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

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

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

  • Filter: ((connected_to IS NOT NULL) OR (queue_index = '0'::double precision))
  • Rows Removed by Filter: 52
19. 810.773 4,705.787 ↓ 181,391.2 725,565 1

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

  • Sort Key: h.sensor_id, h.id DESC
  • Sort Method: external merge Disk: 112,920kB
20. 166.286 3,895.014 ↓ 181,391.2 725,565 1

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

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

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

22. 0.041 3,317.148 ↓ 9.5 105 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
23. 0.024 3,317.107 ↓ 9.5 105 1

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

24. 49.922 3,317.083 ↓ 9.5 105 1

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

25. 450.130 3,267.161 ↓ 65,960.5 725,565 1

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

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

27. 0.004 0.091 ↓ 9.0 9 1

Hash (cost=4.42..4.42 rows=1 width=61) (actual time=0.088..0.091 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 0.039 0.087 ↓ 9.0 9 1

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

  • Hash Cond: (g.establishment_id = st_1.establishment_id)
29. 0.016 0.016 ↑ 1.0 25 1

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

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)

  • 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)

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)

  • 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)

34. 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)

35. 28.980 28.980 ↑ 1.0 1 630

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

Planning time : 1.325 ms
Execution time : 35,468.376 ms