explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aiTI : Optimization for: plan #rrX5

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.402 551.630 ↓ 36.0 72 1

Nested Loop (cost=28,379.81..28,396.53 rows=2 width=180) (actual time=548.437..551.630 rows=72 loops=1)

  • Output: p.id, t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic, (min(t.min_id)), (max(t.max_id)), (min(t.min_ts)), (max(t.max_ts)), ((count(t.n))::double precision), ((sum(t.n))::double precision), (((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric))::double precision), (((GREATEST(((sum(((t.n)::numeric * (t.statistic_value ^ '2'::numeric))) / (sum(t.n))::numeric) - ((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric) ^ '2'::numeric)), '0'::numeric) ^ 0.5))::double precision), (NULLIF(((row_to_json(d.*) -> u1_1.statistic))::text, 'null'::text))::double precision, CASE WHEN ((((GREATEST(((sum(((t.n)::numeric * (t.statistic_value ^ '2'::numeric))) / (sum(t.n))::numeric) - ((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric) ^ '2'::numeric)), '0'::numeric) ^ 0.5))::double precision) = '0'::double precision) THEN '0'::double precision WHEN ((((GREATEST(((sum(((t.n)::numeric * (t.statistic_value ^ '2'::numeric))) / (sum(t.n))::numeric) - ((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric) ^ '2'::numeric)), '0'::numeric) ^ 0.5))::double precision) IS NULL) THEN '0'::double precision ELSE (((NULLIF(((row_to_json(d.*) -> u1_1.statistic))::text, 'null'::text))::double precision - (((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric))::double precision)) / (((GREATEST(((sum(((t.n)::numeric * (t.statistic_value ^ '2'::numeric))) / (sum(t.n))::numeric) - ((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric) ^ '2'::numeric)), '0'::numeric) ^ 0.5))::double precision)) END
  • Buffers: shared hit=1,569 read=56,712 written=4, temp read=751 written=667
2. 0.077 548.276 ↓ 36.0 72 1

Nested Loop (cost=28,379.52..28,396.12 rows=2 width=797) (actual time=548.107..548.276 rows=72 loops=1)

  • Output: p.id, p.sensor_statistics, t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic, (min(t.min_id)), (max(t.max_id)), (min(t.min_ts)), (max(t.max_ts)), ((count(t.n))::double precision), ((sum(t.n))::double precision), (((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric))::double precision), (((GREATEST(((sum(((t.n)::numeric * (t.statistic_value ^ '2'::numeric))) / (sum(t.n))::numeric) - ((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric) ^ '2'::numeric)), '0'::numeric) ^ 0.5))::double precision), u1.measure, u1_1.statistic
  • Inner Unique: true
  • Buffers: shared hit=1,569 read=56,712 written=4, temp read=751 written=667
3. 0.038 548.127 ↓ 36.0 72 1

Hash Join (cost=28,379.10..28,379.24 rows=2 width=224) (actual time=548.091..548.127 rows=72 loops=1)

  • Output: u1.measure, t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic, (min(t.min_id)), (max(t.max_id)), (min(t.min_ts)), (max(t.max_ts)), ((count(t.n))::double precision), ((sum(t.n))::double precision), (((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric))::double precision), (((GREATEST(((sum(((t.n)::numeric * (t.statistic_value ^ '2'::numeric))) / (sum(t.n))::numeric) - ((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric) ^ '2'::numeric)), '0'::numeric) ^ 0.5))::double precision), u1_1.statistic
  • Hash Cond: (u1.measure = t.measure)
  • Buffers: shared hit=1,281 read=56,712 written=4, temp read=751 written=667
4. 0.005 0.005 ↑ 1.0 9 1

Function Scan on pg_catalog.unnest u1 (cost=0.00..0.09 rows=9 width=32) (actual time=0.004..0.005 rows=9 loops=1)

  • Output: u1.measure
  • Function Call: unnest('{ms,mlperms,sensor_ml,sensor_sig,sensor_vfr,sensor_cdtof,sensor_ml_ma,sensor_temp_c,diff_temp_c}'::text[])
5. 0.032 548.084 ↓ 18.0 72 1

Hash (cost=28,379.05..28,379.05 rows=4 width=192) (actual time=548.075..548.084 rows=72 loops=1)

  • Output: u1_1.statistic, t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic, (min(t.min_id)), (max(t.max_id)), (min(t.min_ts)), (max(t.max_ts)), ((count(t.n))::double precision), ((sum(t.n))::double precision), (((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric))::double precision), (((GREATEST(((sum(((t.n)::numeric * (t.statistic_value ^ '2'::numeric))) / (sum(t.n))::numeric) - ((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric) ^ '2'::numeric)), '0'::numeric) ^ 0.5))::double precision)
  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=1,281 read=56,712 written=4, temp read=751 written=667
6. 0.038 548.052 ↓ 18.0 72 1

Hash Join (cost=28,376.95..28,379.05 rows=4 width=192) (actual time=538.993..548.052 rows=72 loops=1)

  • Output: u1_1.statistic, t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic, (min(t.min_id)), (max(t.max_id)), (min(t.min_ts)), (max(t.max_ts)), ((count(t.n))::double precision), ((sum(t.n))::double precision), (((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric))::double precision), (((GREATEST(((sum(((t.n)::numeric * (t.statistic_value ^ '2'::numeric))) / (sum(t.n))::numeric) - ((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric) ^ '2'::numeric)), '0'::numeric) ^ 0.5))::double precision)
  • Hash Cond: (t.statistic = u1_1.statistic)
  • Buffers: shared hit=1,281 read=56,712 written=4, temp read=751 written=667
7. 8.808 548.008 ↓ 4.5 72 1

GroupAggregate (cost=28,376.86..28,378.70 rows=16 width=160) (actual time=538.977..548.008 rows=72 loops=1)

  • Output: t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic, min(t.min_id), max(t.max_id), min(t.min_ts), max(t.max_ts), (count(t.n))::double precision, (sum(t.n))::double precision, ((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric))::double precision, ((GREATEST(((sum(((t.n)::numeric * (t.statistic_value ^ '2'::numeric))) / (sum(t.n))::numeric) - ((sum(((t.n)::numeric * t.statistic_value)) / (sum(t.n))::numeric) ^ '2'::numeric)), '0'::numeric) ^ 0.5))::double precision
  • Group Key: t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic
  • Buffers: shared hit=1,281 read=56,712 written=4, temp read=751 written=667
8. 11.440 539.200 ↓ 285.8 4,572 1

Sort (cost=28,376.86..28,376.90 rows=16 width=164) (actual time=538.849..539.200 rows=4,572 loops=1)

  • Output: t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic, t.min_id, t.max_id, t.min_ts, t.max_ts, t.n, t.statistic_value
  • Sort Key: t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic
  • Sort Method: quicksort Memory: 835kB
  • Buffers: shared hit=1,281 read=56,712 written=4, temp read=751 written=667
9. 0.884 527.760 ↓ 285.8 4,572 1

Subquery Scan on t (cost=28,365.46..28,376.54 rows=16 width=164) (actual time=311.930..527.760 rows=4,572 loops=1)

  • Output: t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic, t.min_id, t.max_id, t.min_ts, t.max_ts, t.n, t.statistic_value
  • Buffers: shared hit=1,281 read=56,712 written=4, temp read=751 written=667
10. 176.439 526.876 ↓ 285.8 4,572 1

WindowAgg (cost=28,365.46..28,376.38 rows=16 width=334) (actual time=311.929..526.876 rows=4,572 loops=1)

  • Output: s.establishment_id, s.anchor, NULL::integer, (array_agg("*VALUES*".column1)), NULL::bigint, NULL::interval, u1_3.measure, ((p_1.sensor_statistics -> 'n'::text))::integer, u1_2.statistic, (NULLIF((((((row_to_json(s.*))::jsonb || (row_to_json(ROW(p_1.id, p_1.gateway_id, p_1.bru_id, p_1.sensor_id, p_1.poured_at, u1_3.measure, ((p_1.sensor_statistics -> 'n'::text))::integer, (((p_1.sensor_statistics -> u1_3.measure) -> 'max_x'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'min_x'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x_squared'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x_cubed'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x_fourth'::text))::double precision)))::jsonb) || (row_to_json(d_1.*))::jsonb) -> u1_2.statistic))::text, 'null'::text))::numeric, min(s.id) OVER (?), min(s.poured_at) OVER (?), max(s.id) OVER (?), max(s.poured_at) OVER (?), NULL::character varying(40), NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, s.anchor
  • Buffers: shared hit=1,281 read=56,712 written=4, temp read=751 written=667
11. 1.501 350.437 ↓ 285.8 4,572 1

Nested Loop (cost=28,365.46..28,374.46 rows=16 width=808) (actual time=251.631..350.437 rows=4,572 loops=1)

  • Output: s.anchor, s.establishment_id, (array_agg("*VALUES*".column1)), u1_3.measure, p_1.sensor_statistics, u1_2.statistic, s.*, p_1.id, p_1.gateway_id, p_1.bru_id, p_1.sensor_id, p_1.poured_at, d_1.*, s.id, s.poured_at
  • Buffers: shared hit=1,281 read=56,712 written=4
12. 44.799 348.936 ↓ 285.8 1,143 1

Nested Loop (cost=28,365.45..28,374.13 rows=4 width=776) (actual time=251.624..348.936 rows=1,143 loops=1)

  • Output: s.establishment_id, s.anchor, s.*, s.id, s.poured_at, p_1.sensor_statistics, p_1.id, p_1.gateway_id, p_1.bru_id, p_1.sensor_id, p_1.poured_at, u1_3.measure, d_1.*, (array_agg("*VALUES*".column1))
  • Join Filter: (((((row_to_json(s.*))::jsonb || (row_to_json(ROW(p_1.id, p_1.gateway_id, p_1.bru_id, p_1.sensor_id, p_1.poured_at, u1_3.measure, ((p_1.sensor_statistics -> 'n'::text))::integer, (((p_1.sensor_statistics -> u1_3.measure) -> 'max_x'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'min_x'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x_squared'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x_cubed'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x_fourth'::text))::double precision)))::jsonb) || (row_to_json(d_1.*))::jsonb) -> 'n'::text) IS NOT NULL)
  • Buffers: shared hit=1,281 read=56,712 written=4
13. 0.276 252.702 ↓ 285.8 1,143 1

Nested Loop (cost=28,365.17..28,373.45 rows=4 width=752) (actual time=250.853..252.702 rows=1,143 loops=1)

  • Output: s.establishment_id, s.anchor, s.*, s.id, s.poured_at, p_1.sensor_statistics, p_1.id, p_1.gateway_id, p_1.bru_id, p_1.sensor_id, p_1.poured_at, u1_3.measure, (array_agg("*VALUES*".column1))
  • Buffers: shared hit=1,281 read=56,712 written=4
14. 0.208 252.299 ↓ 127.0 127 1

Nested Loop (cost=28,365.17..28,373.27 rows=1 width=720) (actual time=250.847..252.299 rows=127 loops=1)

  • Output: s.establishment_id, s.anchor, s.*, s.id, s.poured_at, p_1.sensor_statistics, p_1.id, p_1.gateway_id, p_1.bru_id, p_1.sensor_id, p_1.poured_at, (array_agg("*VALUES*".column1))
  • Join Filter: ((s.establishment_id = "*VALUES*".column2) AND CASE WHEN "*VALUES*".column7 THEN ((s.rank <= "*VALUES*".column5) AND (s.age <= "*VALUES*".column6)) ELSE ((s.rank <= "*VALUES*".column5) OR (s.age <= "*VALUES*".column6)) END)
  • Rows Removed by Join Filter: 1,016
  • Buffers: shared hit=1,281 read=56,712 written=4
15. 0.087 251.710 ↓ 127.0 127 1

Nested Loop (cost=28,364.95..28,373.01 rows=1 width=712) (actual time=250.807..251.710 rows=127 loops=1)

  • Output: s.establishment_id, s.anchor, s.*, s.id, s.poured_at, s.rank, s.age, p_1.sensor_statistics, p_1.id, p_1.gateway_id, p_1.bru_id, p_1.sensor_id, p_1.poured_at
  • Inner Unique: true
  • Buffers: shared hit=1,281 read=56,712 written=4
16. 0.085 251.115 ↓ 127.0 127 1

Subquery Scan on s (cost=28,364.53..28,364.57 rows=1 width=119) (actual time=250.791..251.115 rows=127 loops=1)

  • Output: s.establishment_id, s.anchor, s.*, s.id, s.poured_at, s.rank, s.age
  • Buffers: shared hit=783 read=56,702 written=4
17. 0.226 251.030 ↓ 127.0 127 1

WindowAgg (cost=28,364.53..28,364.56 rows=1 width=51) (actual time=250.787..251.030 rows=127 loops=1)

  • Output: g.establishment_id, p_3.id, p_2.id, p_2.poured_at, rank() OVER (?), (first_value(p_2.poured_at) OVER (?) - p_2.poured_at), l.identifier
  • Buffers: shared hit=783 read=56,702 written=4
18. 0.084 250.804 ↓ 127.0 127 1

Sort (cost=28,364.53..28,364.54 rows=1 width=27) (actual time=250.777..250.804 rows=127 loops=1)

  • Output: p_3.id, p_2.id, g.establishment_id, p_2.poured_at, l.identifier
  • Sort Key: p_3.id, p_2.id DESC
  • Sort Method: quicksort Memory: 34kB
  • Buffers: shared hit=783 read=56,702 written=4
19. 0.026 250.720 ↓ 127.0 127 1

Nested Loop (cost=1,133.47..28,364.52 rows=1 width=27) (actual time=248.643..250.720 rows=127 loops=1)

  • Output: p_3.id, p_2.id, g.establishment_id, p_2.poured_at, l.identifier
  • Inner Unique: true
  • Buffers: shared hit=783 read=56,702 written=4
20. 0.107 250.567 ↓ 127.0 127 1

Nested Loop (cost=1,133.33..28,364.36 rows=1 width=27) (actual time=248.621..250.567 rows=127 loops=1)

  • Output: p_2.id, p_2.poured_at, p_2.gateway_id, l.identifier, p_3.id
  • Join Filter: (p_2.poured_at <@ il.connected)
  • Buffers: shared hit=531 read=56,700 written=4
21. 3.220 250.333 ↓ 127.0 127 1

Nested Loop (cost=1,133.05..28,363.00 rows=1 width=35) (actual time=248.597..250.333 rows=127 loops=1)

  • Output: p_2.id, p_2.poured_at, p_2.gateway_id, ls.line_id, l.identifier, l.id, p_3.id
  • Join Filter: (ls.line_id = l.id)
  • Rows Removed by Join Filter: 28,448
  • Buffers: shared hit=153 read=56,697 written=4
22. 0.063 0.063 ↑ 1.0 225 1

Index Scan using lines_pkey on public.lines l (cost=0.14..18.71 rows=225 width=11) (actual time=0.009..0.063 rows=225 loops=1)

  • Output: l.id, l.establishment_id, l.length_ft, l.inner_diameter_in, l.archived, l.metadata, l.identifier, l.group_label, l.status_code, l.last_connected_item_id
  • Buffers: shared hit=34 read=1
23. 2.104 247.050 ↓ 127.0 127 225

Materialize (cost=1,132.91..28,340.92 rows=1 width=24) (actual time=0.483..1.098 rows=127 loops=225)

  • Output: p_2.id, p_2.poured_at, p_2.gateway_id, ls.line_id, p_3.id
  • Buffers: shared hit=119 read=56,696 written=4
24. 0.391 244.946 ↓ 127.0 127 1

Nested Loop (cost=1,132.91..28,340.91 rows=1 width=24) (actual time=108.597..244.946 rows=127 loops=1)

  • Output: p_2.id, p_2.poured_at, p_2.gateway_id, ls.line_id, p_3.id
  • Join Filter: ((p_2.poured_at <@ ls.connected) AND (ls.sensor_id = p_2.sensor_id))
  • Rows Removed by Join Filter: 176
  • Buffers: shared hit=119 read=56,696 written=4
25. 0.053 0.111 ↓ 2.0 2 1

Hash Join (cost=12.91..19.96 rows=1 width=32) (actual time=0.082..0.111 rows=2 loops=1)

  • Output: ls.connected, ls.sensor_id, ls.line_id, p_3.id, p_3.sensor_id
  • Hash Cond: (ls.sensor_id = p_3.sensor_id)
  • Buffers: shared hit=9 read=2
26. 0.044 0.044 ↑ 1.0 269 1

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

  • Output: ls.id, ls.sensor_id, ls.line_id, ls.connected_from, ls.connected_to, ls.archived, ls.connected
  • Buffers: shared hit=1 read=2
27. 0.001 0.014 ↑ 1.0 2 1

Hash (cost=12.89..12.89 rows=2 width=8) (actual time=0.014..0.014 rows=2 loops=1)

  • Output: p_3.id, p_3.sensor_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=8
28. 0.013 0.013 ↑ 1.0 2 1

Index Scan using pours_pkey on public.pours p_3 (cost=0.42..12.89 rows=2 width=8) (actual time=0.009..0.013 rows=2 loops=1)

  • Output: p_3.id, p_3.sensor_id
  • Index Cond: (p_3.id = ANY ('{218538,218519}'::integer[]))
  • Buffers: shared hit=8
29. 217.964 244.444 ↓ 1.0 152 2

Bitmap Heap Scan on public.pours p_2 (cost=1,120.00..28,318.74 rows=148 width=20) (actual time=107.112..122.222 rows=152 loops=2)

  • Output: p_2.id, p_2.gateway_id, p_2.bru_id, p_2.sensor_id, p_2.sensor_temp_c_start, p_2.sensor_temp_c_stop, p_2.sensor_total_samples, p_2.sensor_total_ml, p_2.received_at, p_2.archived, p_2.bru_temp_c, p_2.bru_barometric, p_2.bru_humidity, p_2.ts_start, p_2.ts_duration, p_2.sensor_samples, p_2.poured_at, p_2.corrected_total_ml, p_2.sensor_ma_zero, p_2.sensor_statistics, p_2.corrected_using, p_2.test_mass_grams, p_2.conditions, p_2.conditions_set_at, p_2.matched_pos_sales_id, p_2.matched_at
  • Recheck Cond: (p_2.id <= p_3.id)
  • Filter: (p_3.sensor_id = p_2.sensor_id)
  • Rows Removed by Filter: 212,443
  • Heap Blocks: exact=55,638
  • Buffers: shared hit=110 read=56,694 written=4
30. 26.480 26.480 ↓ 3.0 212,594 2

Bitmap Index Scan on pours_pkey (cost=0.00..1,119.96 rows=70,872 width=0) (actual time=13.240..13.240 rows=212,594 loops=2)

  • Index Cond: (p_2.id <= p_3.id)
  • Buffers: shared hit=48 read=1,118
31. 0.127 0.127 ↑ 15.0 1 127

Index Scan using item_lines_line on public.item_lines il (cost=0.28..1.18 rows=15 width=25) (actual time=0.001..0.001 rows=1 loops=127)

  • Output: il.id, il.item_id, il.line_id, il.connected_from, il.connected_to, il.queue_index, il.archived, il.connected
  • Index Cond: (il.line_id = ls.line_id)
  • Filter: ((il.connected_to IS NOT NULL) OR (il.queue_index = '0'::double precision))
  • Buffers: shared hit=378 read=3
32. 0.127 0.127 ↑ 1.0 1 127

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

  • Output: g.id, g.establishment_id, g.identifier, g.certificate, g.last_data_at, g.archived, g.certificate_fingerprint256
  • Index Cond: (g.id = p_2.gateway_id)
  • Buffers: shared hit=252 read=2
33. 0.508 0.508 ↑ 1.0 1 127

Index Scan using pours_pkey on public.pours p_1 (cost=0.42..8.44 rows=1 width=593) (actual time=0.004..0.004 rows=1 loops=127)

  • Output: p_1.id, p_1.gateway_id, p_1.bru_id, p_1.sensor_id, p_1.sensor_temp_c_start, p_1.sensor_temp_c_stop, p_1.sensor_total_samples, p_1.sensor_total_ml, p_1.received_at, p_1.archived, p_1.bru_temp_c, p_1.bru_barometric, p_1.bru_humidity, p_1.ts_start, p_1.ts_duration, p_1.sensor_samples, p_1.poured_at, p_1.corrected_total_ml, p_1.sensor_ma_zero, p_1.sensor_statistics, p_1.corrected_using, p_1.test_mass_grams, p_1.conditions, p_1.conditions_set_at, p_1.matched_pos_sales_id, p_1.matched_at
  • Index Cond: (p_1.id = s.id)
  • Buffers: shared hit=498 read=10
34. 0.358 0.381 ↓ 9.0 9 127

HashAggregate (cost=0.21..0.23 rows=1 width=57) (actual time=0.001..0.003 rows=9 loops=127)

  • Output: "*VALUES*".column2, "*VALUES*".column5, "*VALUES*".column6, "*VALUES*".column7, array_agg("*VALUES*".column1)
  • Group Key: "*VALUES*".column2, "*VALUES*".column5, "*VALUES*".column6, "*VALUES*".column7
35. 0.023 0.023 ↓ 9.0 9 1

Values Scan on "*VALUES*" (cost=0.00..0.20 rows=1 width=29) (actual time=0.010..0.023 rows=9 loops=1)

  • Output: "*VALUES*".column1, "*VALUES*".column2, "*VALUES*".column3, "*VALUES*".column4, "*VALUES*".column5, "*VALUES*".column6, "*VALUES*".column7, "*VALUES*".column8
  • Filter: (("*VALUES*".column8 = 'pour'::text) AND ((now())::timestamp without time zone <@ "*VALUES*".column4))
36. 0.127 0.127 ↑ 1.0 9 127

Function Scan on pg_catalog.unnest u1_3 (cost=0.00..0.09 rows=9 width=32) (actual time=0.000..0.001 rows=9 loops=127)

  • Output: u1_3.measure
  • Function Call: unnest('{ms,mlperms,sensor_ml,sensor_sig,sensor_vfr,sensor_cdtof,sensor_ml_ma,sensor_temp_c,diff_temp_c}'::text[])
37. 51.435 51.435 ↑ 1.0 1 1,143

Function Scan on public.distribution_statistics d_1 (cost=0.29..0.30 rows=1 width=24) (actual time=0.044..0.045 rows=1 loops=1,143)

  • Output: d_1.*
  • Function Call: distribution_statistics(((p_1.sensor_statistics -> 'n'::text))::integer, NULL::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x_squared'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x_cubed'::text))::double precision, (((p_1.sensor_statistics -> u1_3.measure) -> 'sum_x_fourth'::text))::double precision)
38. 0.000 0.000 ↑ 1.0 4 1,143

Function Scan on pg_catalog.unnest u1_2 (cost=0.00..0.04 rows=4 width=32) (actual time=0.000..0.000 rows=4 loops=1,143)

  • Output: u1_2.statistic
  • Function Call: unnest('{mean_x,stdevp_x,skewp_x,ekurtosis_x}'::text[])
39. 0.002 0.006 ↑ 1.0 4 1

Hash (cost=0.04..0.04 rows=4 width=32) (actual time=0.006..0.006 rows=4 loops=1)

  • Output: u1_1.statistic
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 0.004 0.004 ↑ 1.0 4 1

Function Scan on pg_catalog.unnest u1_1 (cost=0.00..0.04 rows=4 width=32) (actual time=0.003..0.004 rows=4 loops=1)

  • Output: u1_1.statistic
  • Function Call: unnest('{mean_x,stdevp_x,skewp_x,ekurtosis_x}'::text[])
41. 0.072 0.072 ↑ 1.0 1 72

Index Scan using pours_pkey on public.pours p (cost=0.42..8.44 rows=1 width=573) (actual time=0.001..0.001 rows=1 loops=72)

  • Output: p.id, p.gateway_id, p.bru_id, p.sensor_id, p.sensor_temp_c_start, p.sensor_temp_c_stop, p.sensor_total_samples, p.sensor_total_ml, p.received_at, p.archived, p.bru_temp_c, p.bru_barometric, p.bru_humidity, p.ts_start, p.ts_duration, p.sensor_samples, p.poured_at, p.corrected_total_ml, p.sensor_ma_zero, p.sensor_statistics, p.corrected_using, p.test_mass_grams, p.conditions, p.conditions_set_at, p.matched_pos_sales_id, p.matched_at
  • Index Cond: (p.id = t.anchor)
  • Buffers: shared hit=288
42. 2.952 2.952 ↑ 1.0 1 72

Function Scan on public.distribution_statistics d (cost=0.29..0.30 rows=1 width=24) (actual time=0.041..0.041 rows=1 loops=72)

  • Output: d.*
  • Function Call: distribution_statistics(((p.sensor_statistics -> 'n'::text))::integer, NULL::double precision, (((p.sensor_statistics -> u1.measure) -> 'sum_x'::text))::double precision, (((p.sensor_statistics -> u1.measure) -> 'sum_x_squared'::text))::double precision, (((p.sensor_statistics -> u1.measure) -> 'sum_x_cubed'::text))::double precision, (((p.sensor_statistics -> u1.measure) -> 'sum_x_fourth'::text))::double precision)
Planning time : 3.703 ms
Execution time : 552.571 ms