explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oYYg

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3,225.709 ↓ 0.0 0 1

Nested Loop (cost=15,830.04..15,838.28 rows=1 width=180) (actual time=3,225.709..3,225.709 rows=0 loops=1)

2. 0.015 0.015 ↑ 1.0 1 1

Index Scan using pours_pkey on pours p (cost=0.42..8.44 rows=1 width=603) (actual time=0.013..0.015 rows=1 loops=1)

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

Nested Loop (cost=15,829.62..15,829.77 rows=1 width=216) (actual time=3,225.692..3,225.692 rows=0 loops=1)

4. 0.005 3,225.692 ↓ 0.0 0 1

Hash Join (cost=15,829.33..15,829.47 rows=1 width=192) (actual time=3,225.691..3,225.692 rows=0 loops=1)

  • Hash Cond: (u1_1.measure = t.measure)
5. 0.005 0.005 ↑ 9.0 1 1

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

6. 0.000 3,225.682 ↓ 0.0 0 1

Hash (cost=15,829.32..15,829.32 rows=1 width=160) (actual time=3,225.682..3,225.682 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
7. 0.002 3,225.682 ↓ 0.0 0 1

Hash Join (cost=15,829.25..15,829.32 rows=1 width=160) (actual time=3,225.682..3,225.682 rows=0 loops=1)

  • Hash Cond: (u1.statistic = t.statistic)
8. 0.002 0.002 ↑ 4.0 1 1

Function Scan on unnest u1 (cost=0.00..0.04 rows=4 width=32) (actual time=0.002..0.002 rows=1 loops=1)

9. 0.001 3,225.678 ↓ 0.0 0 1

Hash (cost=15,829.24..15,829.24 rows=1 width=128) (actual time=3,225.678..3,225.678 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
10. 0.002 3,225.677 ↓ 0.0 0 1

GroupAggregate (cost=15,829.11..15,829.23 rows=1 width=128) (actual time=3,225.677..3,225.677 rows=0 loops=1)

  • Group Key: t.establishment_id, t.statistic_test_id, t.measure, t.statistic
11. 0.005 3,225.675 ↓ 0.0 0 1

Sort (cost=15,829.11..15,829.12 rows=1 width=132) (actual time=3,225.675..3,225.675 rows=0 loops=1)

  • Sort Key: t.establishment_id, t.statistic_test_id, t.measure, t.statistic
  • Sort Method: quicksort Memory: 25kB
12. 0.000 3,225.670 ↓ 0.0 0 1

Subquery Scan on t (cost=15,810.26..15,829.10 rows=1 width=132) (actual time=3,225.670..3,225.670 rows=0 loops=1)

13. 0.002 3,225.670 ↓ 0.0 0 1

WindowAgg (cost=15,810.26..15,829.09 rows=1 width=290) (actual time=3,225.670..3,225.670 rows=0 loops=1)

14. 0.001 3,225.668 ↓ 0.0 0 1

Nested Loop (cost=15,810.26..15,829.00 rows=1 width=766) (actual time=3,225.668..3,225.668 rows=0 loops=1)

  • Join Filter: (((((('/pour/'::text || (p_1.id)::text) || '/'::text) || u1_3.measure) || '_'::text) || u1_2.statistic) ~~ (st.path)::text)
15. 0.000 3,225.667 ↓ 0.0 0 1

Nested Loop (cost=15,810.26..15,828.84 rows=1 width=749) (actual time=3,225.667..3,225.667 rows=0 loops=1)

  • Join Filter: ((row_to_json(ROW(p_2.rank, p_2.age, g.establishment_id, p_2.identifier, 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, d_1.mean_x, d_1.stdevp_x, d_1.z_x, d_1.skewp_x, d_1.ekurtosis_x)) -> 'n'::text) IS NOT NULL)
16. 0.001 3,225.667 ↓ 0.0 0 1

Nested Loop (cost=15,809.97..15,828.48 rows=1 width=709) (actual time=3,225.667..3,225.667 rows=0 loops=1)

17. 1.160 3,225.666 ↓ 0.0 0 1

Nested Loop (cost=15,809.97..15,828.29 rows=1 width=677) (actual time=3,225.666..3,225.666 rows=0 loops=1)

  • Join Filter: ((g.establishment_id = st.establishment_id) AND CASE WHEN st.window_flag THEN ((p_2.rank <= st.window_count) AND (p_2.age <= st.window_duration)) ELSE ((p_2.rank <= st.window_count) OR (p_2.age <= st.window_duration)) END)
  • Rows Removed by Join Filter: 1,957
18. 0.848 3,165.796 ↓ 1,957.0 1,957 1

Nested Loop (cost=15,809.97..15,823.25 rows=1 width=658) (actual time=3,156.434..3,165.796 rows=1,957 loops=1)

19. 0.714 3,157.120 ↓ 1,957.0 1,957 1

Hash Join (cost=15,809.55..15,814.81 rows=1 width=39) (actual time=3,156.411..3,157.120 rows=1,957 loops=1)

  • Hash Cond: (g.id = p_2.gateway_id)
20. 0.018 0.018 ↓ 1.5 27 1

Seq Scan on gateways g (cost=0.00..5.18 rows=18 width=8) (actual time=0.006..0.018 rows=27 loops=1)

21. 0.601 3,156.388 ↓ 1,957.0 1,957 1

Hash (cost=15,809.54..15,809.54 rows=1 width=39) (actual time=3,156.388..3,156.388 rows=1,957 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 154kB
22. 0.344 3,155.787 ↓ 1,957.0 1,957 1

Subquery Scan on p_2 (cost=15,809.51..15,809.54 rows=1 width=39) (actual time=3,153.275..3,155.787 rows=1,957 loops=1)

23. 1.944 3,155.443 ↓ 1,957.0 1,957 1

WindowAgg (cost=15,809.51..15,809.53 rows=1 width=47) (actual time=3,153.273..3,155.443 rows=1,957 loops=1)

24. 1.755 3,153.499 ↓ 1,957.0 1,957 1

Sort (cost=15,809.51..15,809.51 rows=1 width=31) (actual time=3,153.259..3,153.499 rows=1,957 loops=1)

  • Sort Key: p_3.id DESC
  • Sort Method: quicksort Memory: 201kB
25. 57.701 3,151.744 ↓ 1,957.0 1,957 1

Nested Loop (cost=8.87..15,809.50 rows=1 width=31) (actual time=40.178..3,151.744 rows=1,957 loops=1)

  • Join Filter: (ls.line_id = l.id)
  • Rows Removed by Join Filter: 422,712
26. 1,477.047 3,043.161 ↓ 1,957.0 1,957 1

Nested Loop (cost=8.87..15,800.60 rows=1 width=32) (actual time=40.129..3,043.161 rows=1,957 loops=1)

  • Join Filter: ((p_3.poured_at <@ il.connected) AND (ls.line_id = il.line_id))
  • Rows Removed by Join Filter: 6,008,773
27. 0.982 151.582 ↓ 2,062.0 2,062 1

Nested Loop (cost=8.87..15,690.46 rows=1 width=28) (actual time=39.764..151.582 rows=2,062 loops=1)

  • Join Filter: (p_3.poured_at <@ ls.connected)
28. 0.050 0.097 ↑ 1.0 1 1

Hash Join (cost=8.45..17.61 rows=1 width=39) (actual time=0.048..0.097 rows=1 loops=1)

  • Hash Cond: (ls.sensor_id = p0.sensor_id)
29. 0.041 0.041 ↑ 1.3 310 1

Seq Scan on line_sensors ls (cost=0.00..8.08 rows=408 width=23) (actual time=0.005..0.041 rows=310 loops=1)

30. 0.002 0.006 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.004 0.004 ↑ 1.0 1 1

Index Scan using pours_pkey on pours p0 (cost=0.42..8.44 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (id = 247,878)
32. 150.503 150.503 ↓ 12.8 2,062 1

Index Scan using pours_pkey on pours p_3 (cost=0.42..15,670.83 rows=161 width=20) (actual time=39.708..150.503 rows=2,062 loops=1)

  • Index Cond: (id <= p0.id)
  • Filter: (p0.sensor_id = sensor_id)
  • Rows Removed by Filter: 238,608
33. 1,414.532 1,414.532 ↓ 1.1 2,915 2,062

Seq Scan on item_lines il (cost=0.00..69.22 rows=2,728 width=25) (actual time=0.003..0.686 rows=2,915 loops=2,062)

  • Filter: ((connected_to IS NOT NULL) OR (queue_index = '0'::double precision))
  • Rows Removed by Filter: 71
34. 50.882 50.882 ↑ 1.2 217 1,957

Seq Scan on lines l (cost=0.00..5.62 rows=262 width=11) (actual time=0.004..0.026 rows=217 loops=1,957)

35. 7.828 7.828 ↑ 1.0 1 1,957

Index Scan using pours_pkey on pours p_1 (cost=0.42..8.44 rows=1 width=623) (actual time=0.004..0.004 rows=1 loops=1,957)

  • Index Cond: (id = p_2.id)
36. 58.710 58.710 ↑ 1.0 1 1,957

Seq Scan on statistic_tests st (cost=0.00..5.03 rows=1 width=44) (actual time=0.003..0.030 rows=1 loops=1,957)

  • Filter: (active AND (NOT archived) AND ((condition_variable)::text = 'z_x'::text) AND ((type)::text = 'pourwindow'::text) AND ((now())::timestamp without time zone <@ effective))
  • Rows Removed by Filter: 89
37. 0.000 0.000 ↓ 0.0 0

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

38. 0.000 0.000 ↓ 0.0 0

Function Scan on distribution_statistics d_1 (cost=0.29..0.30 rows=1 width=40) (never executed)

39. 0.000 0.000 ↓ 0.0 0

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

40. 0.000 0.000 ↓ 0.0 0

Function Scan on distribution_statistics d (cost=0.29..0.30 rows=1 width=24) (never executed)

Planning time : 4.652 ms
Execution time : 3,225.953 ms