explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iqRa : heartbeat window

Settings
# exclusive inclusive rows x rows loops node
1. 0.221 329.125 ↑ 1.0 24 1

Nested Loop (cost=408.77..424.77 rows=24 width=133) (actual time=328.993..329.125 rows=24 loops=1)

  • Join Filter: (st.id = ANY ((array_agg(st_1.id))))
  • Rows Removed by Join Filter: 948
2. 0.044 0.044 ↑ 1.0 81 1

Seq Scan on statistic_tests st (cost=0.00..2.81 rows=81 width=29) (actual time=0.024..0.044 rows=81 loops=1)

3. 0.085 328.860 ↓ 2.0 12 81

Materialize (cost=408.77..409.82 rows=6 width=136) (actual time=2.811..4.060 rows=12 loops=81)

4. 0.036 328.775 ↓ 2.0 12 1

Nested Loop (cost=408.77..409.79 rows=6 width=136) (actual time=227.715..328.775 rows=12 loops=1)

5. 115.413 327.743 ↓ 2.0 12 1

GroupAggregate (cost=408.51..409.35 rows=6 width=244) (actual time=227.310..327.743 rows=12 loops=1)

  • Group Key: h.sensor_id, (array_agg(st_1.id)), u1.measure, h.anchor, h.establishment_id
6. 23.265 212.330 ↓ 1,100.0 6,600 1

Sort (cost=408.51..408.52 rows=6 width=128) (actual time=208.770..212.330 rows=6,600 loops=1)

  • Sort Key: h.sensor_id, (array_agg(st_1.id)), u1.measure, h.anchor, h.establishment_id
  • Sort Method: external merge Disk: 3,760kB
7. 0.784 189.065 ↓ 1,100.0 6,600 1

Nested Loop (cost=407.82..408.43 rows=6 width=128) (actual time=184.338..189.065 rows=6,600 loops=1)

8. 10.543 187.181 ↓ 1,100.0 1,100 1

Hash Join (cost=407.82..408.31 rows=1 width=96) (actual time=184.327..187.181 rows=1,100 loops=1)

  • Hash Cond: (st_1.establishment_id = h.establishment_id)
  • Join Filter: CASE WHEN st_1.window_and THEN ((h.rank <= st_1.window_count) AND (h.age <= st_1.window_duration)) ELSE ((h.rank <= st_1.window_count) OR (h.age <= st_1.window_duration)) END
  • Rows Removed by Join Filter: 29,644
9. 0.058 0.217 ↓ 1.5 27 1

HashAggregate (cost=4.18..4.41 rows=18 width=57) (actual time=0.204..0.217 rows=27 loops=1)

  • Group Key: st_1.establishment_id, st_1.window_count, st_1.window_duration, st_1.window_and
10. 0.159 0.159 ↑ 1.0 45 1

Seq Scan on statistic_tests st_1 (cost=0.00..3.62 rows=45 width=29) (actual time=0.102..0.159 rows=45 loops=1)

  • Filter: (((type)::text = 'heartbeat'::text) AND ((now())::timestamp without time zone <@ effective))
  • Rows Removed by Filter: 36
11. 13.165 176.421 ↓ 10,248.0 10,248 1

Hash (cost=403.62..403.62 rows=1 width=80) (actual time=176.416..176.421 rows=10,248 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 2 (originally 1) Memory Usage: 5,797kB
12. 1.261 163.256 ↓ 10,248.0 10,248 1

Subquery Scan on h (cost=403.58..403.62 rows=1 width=80) (actual time=109.770..163.256 rows=10,248 loops=1)

13. 51.524 161.995 ↓ 10,248.0 10,248 1

WindowAgg (cost=403.58..403.61 rows=1 width=84) (actual time=109.769..161.995 rows=10,248 loops=1)

14. 3.469 110.471 ↓ 10,248.0 10,248 1

Sort (cost=403.58..403.59 rows=1 width=148) (actual time=109.745..110.471 rows=10,248 loops=1)

  • Sort Key: h_2.sensor_id, h_1.received_at DESC
  • Sort Method: quicksort Memory: 3,055kB
15. 0.620 107.002 ↓ 10,248.0 10,248 1

Nested Loop (cost=9.30..403.57 rows=1 width=148) (actual time=0.085..107.002 rows=10,248 loops=1)

16. 27.357 96.134 ↓ 10,248.0 10,248 1

Nested Loop (cost=9.16..403.42 rows=1 width=148) (actual time=0.081..96.134 rows=10,248 loops=1)

  • Join Filter: (h_1.received_at <@ il.connected)
  • Rows Removed by Join Filter: 133,686
17. 1.784 17.372 ↓ 3,427.0 10,281 1

Nested Loop (cost=8.88..399.31 rows=3 width=152) (actual time=0.063..17.372 rows=10,281 loops=1)

  • Join Filter: (h_1.received_at <= h_2.received_at)
18. 0.038 0.084 ↑ 1.0 1 1

Hash Join (cost=8.46..14.86 rows=1 width=40) (actual time=0.048..0.084 rows=1 loops=1)

  • Hash Cond: (ls.sensor_id = h_2.sensor_id)
19. 0.034 0.034 ↑ 1.0 269 1

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

20. 0.002 0.012 ↑ 1.0 1 1

Hash (cost=8.44..8.44 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.010 0.010 ↑ 1.0 1 1

Index Scan using heartbeats_pkey on heartbeats h_2 (cost=0.42..8.44 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (id = 826,217)
22. 15.504 15.504 ↓ 277.9 10,281 1

Index Scan using heartbeats_sensor_receivedat on heartbeats h_1 (cost=0.42..383.99 rows=37 width=140) (actual time=0.013..15.504 rows=10,281 loops=1)

  • Index Cond: (sensor_id = ls.sensor_id)
  • Filter: (received_at <@ ls.connected)
23. 51.405 51.405 ↑ 1.1 14 10,281

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

  • Index Cond: (line_id = ls.line_id)
  • Filter: ((connected_to IS NOT NULL) OR (queue_index = '0'::double precision))
24. 10.248 10.248 ↑ 1.0 1 10,248

Index Scan using gateways_pkey on gateways g (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=10,248)

  • Index Cond: (id = h_1.gateway_id)
25. 1.100 1.100 ↑ 1.0 6 1,100

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

26. 0.996 0.996 ↑ 1.0 1 12

Function Scan on distribution_statistics d (cost=0.26..0.27 rows=1 width=24) (actual time=0.083..0.083 rows=1 loops=12)

Planning time : 1.486 ms
Execution time : 332.303 ms