explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eCeh

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 32,718.774 ↑ 2.0 1 1

Append (cost=1,613,773.74..1,613,779.35 rows=2 width=7) (actual time=32,718.767..32,718.774 rows=1 loops=1)

2.          

CTE task

3. 0.011 0.011 ↑ 1.0 1 1

Index Scan using device_virtual_tasks_pkey on device_virtual_tasks (cost=0.28..8.29 rows=1 width=44) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (id = 1168052)
4.          

CTE device_list

5. 0.653 1.150 ↓ 2.0 1,081 1

Nested Loop (cost=0.45..61.86 rows=531 width=4) (actual time=0.019..1.150 rows=1,081 loops=1)

6. 0.003 0.004 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Group Key: task_2.sequence_id
7. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on task task_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

8. 0.493 0.493 ↓ 2.0 1,081 1

Index Only Scan using device_filter_cache_pkey on device_filter_cache (cost=0.42..56.52 rows=531 width=8) (actual time=0.013..0.493 rows=1,081 loops=1)

  • Index Cond: (cache_key_id = task_2.sequence_id)
  • Heap Fetches: 0
9.          

CTE times

10. 15.142 14,756.682 ↓ 5.4 1,081 1

HashAggregate (cost=787,234.97..787,236.97 rows=200 width=36) (actual time=14,756.287..14,756.682 rows=1,081 loops=1)

  • Group Key: d.device_id, tk.type_id, tk.nr, tk.execution_time, (max(dc_1.recorded_at))
11. 1,055.736 14,741.540 ↓ 141.3 28,264 1

Hash Left Join (cost=738,787.44..787,231.97 rows=200 width=36) (actual time=13,695.836..14,741.540 rows=28,264 loops=1)

  • Hash Cond: ((d.device_id = dc.device_id) AND (tk.type_id = dc.type_id) AND (tk.nr = dc.nr))
  • Join Filter: (dc.recorded_at >= tk.execution_time)
12. 1.521 7,554.092 ↓ 5.4 1,081 1

HashAggregate (cost=402,672.26..402,674.26 rows=200 width=28) (actual time=7,553.692..7,554.092 rows=1,081 loops=1)

  • Group Key: d.device_id, tk.type_id, tk.nr, tk.execution_time
13. 1,483.336 7,552.571 ↓ 2.0 1,081 1

Hash Left Join (cost=336,119.35..402,665.63 rows=531 width=28) (actual time=6,056.435..7,552.571 rows=1,081 loops=1)

  • Hash Cond: ((d.device_id = dc_1.device_id) AND (tk.type_id = dc_1.type_id) AND (tk.nr = dc_1.nr))
  • Join Filter: (dc_1.recorded_at <= tk.execution_time)
  • Rows Removed by Join Filter: 28263
14. 0.604 22.539 ↓ 2.0 1,081 1

Append (cost=4.17..8,739.41 rows=531 width=20) (actual time=0.037..22.539 rows=1,081 loops=1)

15. 0.705 15.787 ↓ 2.0 1,081 1

Nested Loop (cost=4.17..4,369.70 rows=530 width=20) (actual time=0.036..15.787 rows=1,081 loops=1)

16. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on task tk (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=1)

17. 2.206 15.080 ↓ 2.0 1,081 1

Nested Loop (cost=4.17..4,364.38 rows=530 width=4) (actual time=0.035..15.080 rows=1,081 loops=1)

18. 2.064 2.064 ↓ 2.0 1,081 1

CTE Scan on device_list d (cost=0.00..10.62 rows=531 width=4) (actual time=0.020..2.064 rows=1,081 loops=1)

19. 7.567 10.810 ↑ 1.0 1 1,081

Bitmap Heap Scan on devices (cost=4.17..8.19 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1,081)

  • Recheck Cond: (id = d.device_id)
  • Filter: ((category)::text <> 'Virtual'::text)
  • Heap Blocks: exact=1081
20. 3.243 3.243 ↑ 1.0 1 1,081

Bitmap Index Scan on idx_device_id (cost=0.00..4.17 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1,081)

  • Index Cond: (id = d.device_id)
21. 0.002 6.148 ↓ 0.0 0 1

Nested Loop (cost=4.17..4,364.41 rows=1 width=12) (actual time=6.148..6.148 rows=0 loops=1)

22. 1.390 6.146 ↓ 0.0 0 1

Nested Loop (cost=4.17..4,364.38 rows=1 width=4) (actual time=6.146..6.146 rows=0 loops=1)

23. 0.432 0.432 ↓ 2.0 1,081 1

CTE Scan on device_list d_1 (cost=0.00..10.62 rows=531 width=4) (actual time=0.001..0.432 rows=1,081 loops=1)

24. 2.162 4.324 ↓ 0.0 0 1,081

Bitmap Heap Scan on devices devices_1 (cost=4.17..8.19 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1,081)

  • Recheck Cond: (id = d_1.device_id)
  • Filter: ((category)::text = 'Virtual'::text)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=1081
25. 2.162 2.162 ↑ 1.0 1 1,081

Bitmap Index Scan on idx_device_id (cost=0.00..4.17 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1,081)

  • Index Cond: (id = d_1.device_id)
26. 0.000 0.000 ↓ 0.0 0

CTE Scan on task tk_1 (cost=0.00..0.02 rows=1 width=8) (never executed)

27. 3,140.510 6,046.696 ↓ 1.0 7,534,017 1

Hash (cost=165,579.33..165,579.33 rows=7,300,506 width=20) (actual time=6,046.696..6,046.696 rows=7,534,017 loops=1)

  • Buckets: 2097152 Batches: 8 Memory Usage: 64296kB
28. 2,906.186 2,906.186 ↓ 1.0 7,534,017 1

Seq Scan on device_data_cache dc_1 (cost=0.00..165,579.33 rows=7,300,506 width=20) (actual time=0.016..2,906.186 rows=7,534,017 loops=1)

  • Filter: (value_type = ANY ('{0,-1}'::integer[]))
29. 3,147.939 6,131.712 ↓ 1.0 7,534,017 1

Hash (cost=165,579.33..165,579.33 rows=7,300,506 width=20) (actual time=6,131.712..6,131.712 rows=7,534,017 loops=1)

  • Buckets: 2097152 Batches: 8 Memory Usage: 64296kB
30. 2,983.773 2,983.773 ↓ 1.0 7,534,017 1

Seq Scan on device_data_cache dc (cost=0.00..165,579.33 rows=7,300,506 width=20) (actual time=0.016..2,983.773 rows=7,534,017 loops=1)

  • Filter: (value_type = ANY ('{0,-1}'::integer[]))
31.          

CTE ranges

32. 1,358.860 32,707.038 ↓ 5.4 1,081 1

Hash Left Join (cost=722,990.89..824,765.91 rows=200 width=60) (actual time=29,963.463..32,707.038 rows=1,081 loops=1)

  • Hash Cond: ((a.device_id = dmax.device_id) AND (a.type_id = dmax.type_id) AND (a.nr = dmax.nr) AND (a.max_before = dmax.recorded_at))
33. 1,403.670 23,970.546 ↓ 5.4 1,081 1

Hash Left Join (cost=361,495.45..412,384.46 rows=200 width=48) (actual time=22,574.678..23,970.546 rows=1,081 loops=1)

  • Hash Cond: ((a.device_id = dmin.device_id) AND (a.type_id = dmin.type_id) AND (a.nr = dmin.nr) AND (a.min_after = dmin.recorded_at))
34. 14,757.360 14,757.360 ↓ 5.4 1,081 1

CTE Scan on times a (cost=0.00..4.00 rows=200 width=36) (actual time=14,756.289..14,757.360 rows=1,081 loops=1)

35. 4,221.509 7,809.516 ↓ 1.0 7,534,017 1

Hash (cost=165,579.33..165,579.33 rows=7,300,506 width=32) (actual time=7,809.516..7,809.516 rows=7,534,017 loops=1)

  • Buckets: 2097152 Batches: 8 Memory Usage: 75207kB
36. 3,588.007 3,588.007 ↓ 1.0 7,534,017 1

Seq Scan on device_data_cache dmin (cost=0.00..165,579.33 rows=7,300,506 width=32) (actual time=0.016..3,588.007 rows=7,534,017 loops=1)

  • Filter: (value_type = ANY ('{0,-1}'::integer[]))
37. 4,050.534 7,377.632 ↓ 1.0 7,534,017 1

Hash (cost=165,579.33..165,579.33 rows=7,300,506 width=32) (actual time=7,377.632..7,377.632 rows=7,534,017 loops=1)

  • Buckets: 2097152 Batches: 8 Memory Usage: 75207kB
38. 3,327.098 3,327.098 ↓ 1.0 7,534,017 1

Seq Scan on device_data_cache dmax (cost=0.00..165,579.33 rows=7,300,506 width=32) (actual time=0.015..3,327.098 rows=7,534,017 loops=1)

  • Filter: (value_type = ANY ('{0,-1}'::integer[]))
39.          

CTE values

40. 2.931 32,716.268 ↓ 5.4 1,081 1

Nested Loop (cost=4.34..1,687.05 rows=200 width=53) (actual time=29,963.535..32,716.268 rows=1,081 loops=1)

41. 32,707.932 32,707.932 ↓ 5.4 1,081 1

CTE Scan on ranges (cost=0.00..4.00 rows=200 width=46) (actual time=29,963.465..32,707.932 rows=1,081 loops=1)

42. 2.162 5.405 ↑ 1.0 1 1,081

Bitmap Heap Scan on devices devices_2 (cost=4.34..8.36 rows=1 width=11) (actual time=0.005..0.005 rows=1 loops=1,081)

  • Recheck Cond: (id = ranges.device_id)
  • Heap Blocks: exact=1081
43. 3.243 3.243 ↑ 1.0 1 1,081

Bitmap Index Scan on idx_device_id (cost=0.00..4.34 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1,081)

  • Index Cond: (id = ranges.device_id)
44. 0.003 32,718.768 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=13.66..19.22 rows=1 width=14) (actual time=32,718.767..32,718.768 rows=1 loops=1)

45. 0.615 32,718.765 ↑ 1.0 1 1

GroupAggregate (cost=13.66..19.21 rows=1 width=14) (actual time=32,718.765..32,718.765 rows=1 loops=1)

  • Group Key: task.aggregation
46. 0.622 32,718.150 ↓ 5.4 1,081 1

Sort (cost=13.66..14.16 rows=200 width=14) (actual time=32,717.938..32,718.150 rows=1,081 loops=1)

  • Sort Key: task.aggregation
  • Sort Method: quicksort Memory: 99kB
47. 0.546 32,717.528 ↓ 5.4 1,081 1

Nested Loop (cost=0.00..6.02 rows=200 width=14) (actual time=29,963.551..32,717.528 rows=1,081 loops=1)

48. 0.013 0.013 ↑ 1.0 1 1

CTE Scan on task (cost=0.00..0.02 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

49. 32,716.969 32,716.969 ↓ 5.4 1,081 1

CTE Scan on "values" (cost=0.00..4.00 rows=200 width=10) (actual time=29,963.537..32,716.969 rows=1,081 loops=1)

50. 0.001 0.005 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.02..0.05 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1)

51. 0.003 0.004 ↓ 0.0 0 1

Result (cost=0.02..0.04 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)

  • One-Time Filter: (NOT $9)
52.          

Initplan (for Result)

53. 0.001 0.001 ↑ 531.0 1 1

CTE Scan on device_list (cost=0.00..10.62 rows=531 width=0) (actual time=0.001..0.001 rows=1 loops=1)

54. 0.000 0.000 ↓ 0.0 0

CTE Scan on task task_1 (cost=0.00..0.02 rows=1 width=0) (never executed)

Planning time : 1.334 ms
Execution time : 32,747.957 ms