explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oZ9o : Optimization for: plan #abMr

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 0.097 ↓ 0.0 0 1

Nested Loop (cost=11,928.92..11,948.88 rows=2 width=180) (actual time=0.091..0.097 rows=0 loops=1)

2.          

CTE u1

3. 0.004 0.004 ↑ 1.0 4 1

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

4. 0.010 0.096 ↓ 0.0 0 1

Hash Join (cost=11,928.46..11,928.60 rows=2 width=224) (actual time=0.091..0.096 rows=0 loops=1)

  • Hash Cond: (u1.measure = t.measure)
5. 0.007 0.007 ↑ 9.0 1 1

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

6. 0.001 0.079 ↓ 0.0 0 1

Hash (cost=11,928.41..11,928.41 rows=4 width=192) (actual time=0.074..0.079 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
7. 0.009 0.078 ↓ 0.0 0 1

Hash Join (cost=11,926.31..11,928.41 rows=4 width=192) (actual time=0.073..0.078 rows=0 loops=1)

  • Hash Cond: (t.statistic = u1_1.statistic)
8. 0.001 0.059 ↓ 0.0 0 1

GroupAggregate (cost=11,926.18..11,928.02 rows=16 width=160) (actual time=0.055..0.059 rows=0 loops=1)

  • Group Key: t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic
9. 0.006 0.058 ↓ 0.0 0 1

Sort (cost=11,926.18..11,926.22 rows=16 width=164) (actual time=0.054..0.058 rows=0 loops=1)

  • Sort Key: t.establishment_id, t.anchor, t.statistic_test_ids, t.measure, t.statistic
  • Sort Method: quicksort Memory: 25kB
10. 0.001 0.052 ↓ 0.0 0 1

Subquery Scan on t (cost=11,910.66..11,925.86 rows=16 width=164) (actual time=0.048..0.052 rows=0 loops=1)

11. 0.001 0.051 ↓ 0.0 0 1

WindowAgg (cost=11,910.66..11,925.70 rows=16 width=334) (actual time=0.048..0.051 rows=0 loops=1)

12. 0.004 0.050 ↓ 0.0 0 1

Sort (cost=11,910.66..11,910.70 rows=16 width=784) (actual time=0.047..0.050 rows=0 loops=1)

  • Sort Key: s.anchor
  • Sort Method: quicksort Memory: 25kB
13. 0.001 0.046 ↓ 0.0 0 1

Nested Loop (cost=11,893.69..11,910.34 rows=16 width=784) (actual time=0.043..0.046 rows=0 loops=1)

14. 0.001 0.001 ↑ 1.0 4 1

CTE Scan on u1 u1_3 (cost=0.00..0.08 rows=4 width=32) (actual time=0.000..0.001 rows=4 loops=1)

15. 0.003 0.044 ↓ 0.0 0 4

Materialize (cost=11,893.69..11,910.07 rows=4 width=752) (actual time=0.010..0.011 rows=0 loops=4)

16. 0.001 0.041 ↓ 0.0 0 1

Nested Loop (cost=11,893.69..11,910.05 rows=4 width=752) (actual time=0.038..0.041 rows=0 loops=1)

  • 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_2.measure, ((p_1.sensor_statistics -> 'n'::text))::integer, (((p_1.sensor_statistics -> u1_2.measure) -> 'max_x'::text))::double precision, (((p_1.sensor_statistics -> u1_2.measure) -> 'min_x'::text))::double precision, (((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision, (((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision, (((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_cubed'::text))::double precision, (((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_fourth'::text))::double precision)))::jsonb) || (row_to_json(ROW(((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision), CASE WHEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) >= '0'::double precision) THEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) ^ '0.5'::double precision) ELSE '0'::double precision END, CASE WHEN (CASE WHEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) >= '0'::double precision) THEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) ^ '0.5'::double precision) ELSE '0'::double precision END <> '0'::double precision) THEN NULL::double precision ELSE NULL::double precision END, CASE WHEN (CASE WHEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) >= '0'::double precision) THEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) ^ '0.5'::double precision) ELSE '0'::double precision END <> '0'::double precision) THEN ((((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_cubed'::text))::double precision - (('3'::double precision * ((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision)) * (((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision)) + (('3'::double precision * (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) * (((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision)) - ((((p_1.sensor_statistics -> 'n'::text))::integer)::double precision * (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '3'::double precision))) / (CASE WHEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) >= '0'::double precision) THEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) ^ '0.5'::double precision) ELSE '0'::double precision END ^ '3'::double precision)) / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ELSE NULL::double precision END, CASE WHEN (CASE WHEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) >= '0'::double precision) THEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) ^ '0.5'::double precision) ELSE '0'::double precision END <> '0'::double precision) THEN ((((((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_fourth'::text))::double precision - (('4'::double precision * ((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision)) * (((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_cubed'::text))::double precision)) + (('6'::double precision * (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) * (((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision)) - (('4'::double precision * (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '3'::double precision)) * (((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision)) + ((((p_1.sensor_statistics -> 'n'::text))::integer)::double precision * (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '4'::double precision))) / (CASE WHEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) >= '0'::double precision) THEN ((((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x_squared'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - (((((p_1.sensor_statistics -> u1_2.measure) -> 'sum_x'::text))::double precision / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) ^ '2'::double precision)) ^ '0.5'::double precision) ELSE '0'::double precision END ^ '4'::double precision)) / (((p_1.sensor_statistics -> 'n'::text))::integer)::double precision) - '3'::double precision) ELSE NULL::double precision END)))::jsonb) -> 'n'::text) IS NOT NULL)
17. 0.001 0.040 ↓ 0.0 0 1

Nested Loop (cost=11,893.69..11,901.79 rows=1 width=720) (actual time=0.037..0.040 rows=0 loops=1)

  • 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)
18. 0.000 0.039 ↓ 0.0 0 1

Nested Loop (cost=11,893.47..11,901.53 rows=1 width=712) (actual time=0.037..0.039 rows=0 loops=1)

19. 0.002 0.039 ↓ 0.0 0 1

Subquery Scan on s (cost=11,893.05..11,893.09 rows=1 width=119) (actual time=0.037..0.039 rows=0 loops=1)

20. 0.001 0.037 ↓ 0.0 0 1

WindowAgg (cost=11,893.05..11,893.08 rows=1 width=51) (actual time=0.035..0.037 rows=0 loops=1)

21. 0.004 0.036 ↓ 0.0 0 1

Sort (cost=11,893.05..11,893.06 rows=1 width=27) (actual time=0.034..0.036 rows=0 loops=1)

  • Sort Key: p_2.id DESC
  • Sort Method: quicksort Memory: 25kB
22. 0.001 0.032 ↓ 0.0 0 1

Nested Loop (cost=8.87..11,893.04 rows=1 width=27) (actual time=0.030..0.032 rows=0 loops=1)

  • Join Filter: (p_2.gateway_id = g.id)
23. 0.000 0.031 ↓ 0.0 0 1

Nested Loop (cost=8.87..11,888.48 rows=1 width=27) (actual time=0.030..0.031 rows=0 loops=1)

  • Join Filter: (ls.line_id = l.id)
24. 0.000 0.031 ↓ 0.0 0 1

Nested Loop (cost=8.87..11,881.42 rows=1 width=28) (actual time=0.030..0.031 rows=0 loops=1)

  • Join Filter: ((p_2.poured_at <@ il.connected) AND (ls.line_id = il.line_id))
25. 0.001 0.031 ↓ 0.0 0 1

Nested Loop (cost=8.87..11,779.23 rows=1 width=24) (actual time=0.030..0.031 rows=0 loops=1)

  • Join Filter: ((p_2.poured_at <@ ls.connected) AND (ls.sensor_id = p_2.sensor_id))
26. 0.012 0.030 ↓ 0.0 0 1

Hash Join (cost=8.45..14.86 rows=1 width=32) (actual time=0.029..0.030 rows=0 loops=1)

  • Hash Cond: (ls.sensor_id = p_3.sensor_id)
27. 0.013 0.013 ↑ 269.0 1 1

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

28. 0.000 0.005 ↓ 0.0 0 1

Hash (cost=8.44..8.44 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
29. 0.005 0.005 ↓ 0.0 0 1

Index Scan using pours_pkey on pours p_3 (cost=0.42..8.44 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (id = 247,878)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using pours_pkey on pours p_2 (cost=0.42..11,762.16 rows=148 width=20) (never executed)

  • Index Cond: (id <= p_3.id)
  • Filter: (p_3.sensor_id = sensor_id)
31. 0.000 0.000 ↓ 0.0 0

Seq Scan on item_lines il (cost=0.00..63.59 rows=2,573 width=25) (never executed)

  • Filter: ((connected_to IS NOT NULL) OR (queue_index = '0'::double precision))
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on lines l (cost=0.00..4.25 rows=225 width=11) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Seq Scan on gateways g (cost=0.00..4.25 rows=25 width=8) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Index Scan using pours_pkey on pours p_1 (cost=0.42..8.44 rows=1 width=593) (never executed)

  • Index Cond: (id = s.id)
35. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.21..0.23 rows=1 width=57) (never executed)

  • Group Key: "*VALUES*".column2, "*VALUES*".column5, "*VALUES*".column6, "*VALUES*".column7
36. 0.000 0.000 ↓ 0.0 0

Values Scan on "*VALUES*" (cost=0.00..0.20 rows=1 width=29) (never executed)

  • Filter: ((column8 = 'pour'::text) AND ((now())::timestamp without time zone <@ column4))
37. 0.000 0.000 ↓ 0.0 0

Function Scan on unnest u1_2 (cost=0.00..0.09 rows=9 width=32) (never executed)

38. 0.005 0.010 ↑ 1.0 4 1

Hash (cost=0.08..0.08 rows=4 width=32) (actual time=0.010..0.010 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.005 0.005 ↑ 1.0 4 1

CTE Scan on u1 u1_1 (cost=0.00..0.08 rows=4 width=32) (actual time=0.004..0.005 rows=4 loops=1)

40. 0.000 0.000 ↓ 0.0 0

Index Scan using pours_pkey on pours p (cost=0.42..8.44 rows=1 width=573) (never executed)

  • Index Cond: (id = t.anchor)
Planning time : 11.029 ms
Execution time : 0.864 ms