explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KmF6

Settings
# exclusive inclusive rows x rows loops node
1. 3.133 909.494 ↓ 18.0 3,601 1

Hash Left Join (cost=32,183.61..32,239.61 rows=200 width=64) (actual time=896.397..909.494 rows=3,601 loops=1)

  • Hash Cond: (a."timestamp" = g."timestamp")
2.          

CTE cte_a

3. 3.477 186.417 ↓ 18.0 3,601 1

Finalize GroupAggregate (cost=5,373.73..5,381.73 rows=200 width=76) (actual time=182.378..186.417 rows=3,601 loops=1)

  • Group Key: x."timestamp
4. 0.525 182.940 ↓ 9.0 3,601 1

Sort (cost=5,373.73..5,374.73 rows=400 width=76) (actual time=182.341..182.940 rows=3,601 loops=1)

  • Sort Key: x."timestamp
  • Sort Method: quicksort Memory: 603kB
5. 165.993 182.415 ↓ 9.0 3,601 1

Gather (cost=5,314.44..5,356.44 rows=400 width=76) (actual time=138.489..182.415 rows=3,601 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 1.377 16.422 ↓ 6.0 1,200 3 / 3

Partial HashAggregate (cost=4,314.44..4,316.44 rows=200 width=76) (actual time=16.034..16.422 rows=1,200 loops=3)

  • Group Key: x."timestamp
7. 0.167 15.045 ↑ 1.2 1,200 3 / 3

Custom Scan (ChunkAppend) on component_loads x (cost=0.42..4,300.26 rows=1,418 width=20) (actual time=0.030..15.045 rows=1,200 loops=3)

  • Chunks excluded during startup: 256
8. 14.878 14.878 ↓ 1.0 1,200 3 / 3

Parallel Index Scan Backward using _hyper_4_3244_chunk_component_loads_timestamp_idx on _hyper_4_3244_chunk x_1 (cost=0.43..3,626.75 rows=1,162 width=20) (actual time=0.029..14.878 rows=1,200 loops=3)

  • Index Cond: (("timestamp" >= to_timestamp('2019-09-25 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)) AND ("timestamp" <= to_timestamp('2019-09-25 01:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)))
  • Filter: ((core_number = '-1'::integer) AND ((component_name)::text = 'CPU'::text) AND (domain_id = 61))
  • Rows Removed by Filter: 45,587
9.          

CTE cte_b

10. 3.127 173.574 ↓ 18.0 3,601 1

Finalize GroupAggregate (cost=5,373.73..5,381.73 rows=200 width=76) (actual time=170.016..173.574 rows=3,601 loops=1)

  • Group Key: component_loads."timestamp
11. 0.431 170.447 ↓ 9.0 3,601 1

Sort (cost=5,373.73..5,374.73 rows=400 width=76) (actual time=170.006..170.447 rows=3,601 loops=1)

  • Sort Key: component_loads."timestamp
  • Sort Method: quicksort Memory: 603kB
12. 155.773 170.016 ↓ 9.0 3,601 1

Gather (cost=5,314.44..5,356.44 rows=400 width=76) (actual time=126.633..170.016 rows=3,601 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 1.215 14.243 ↓ 6.0 1,200 3 / 3

Partial HashAggregate (cost=4,314.44..4,316.44 rows=200 width=76) (actual time=13.880..14.243 rows=1,200 loops=3)

  • Group Key: component_loads."timestamp
14. 0.162 13.028 ↑ 1.2 1,200 3 / 3

Custom Scan (ChunkAppend) on component_loads (cost=0.42..4,300.26 rows=1,418 width=20) (actual time=0.017..13.028 rows=1,200 loops=3)

  • Chunks excluded during startup: 256
15. 12.866 12.866 ↓ 1.0 1,200 3 / 3

Parallel Index Scan Backward using _hyper_4_3244_chunk_component_loads_timestamp_idx on _hyper_4_3244_chunk (cost=0.43..3,626.75 rows=1,162 width=20) (actual time=0.016..12.866 rows=1,200 loops=3)

  • Index Cond: (("timestamp" >= to_timestamp('2019-09-25 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)) AND ("timestamp" <= to_timestamp('2019-09-25 01:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)))
  • Filter: ((core_number = 0) AND ((component_name)::text = 'CPU'::text) AND (domain_id = 61))
  • Rows Removed by Filter: 45,587
16.          

CTE cte_c

17. 3.017 162.212 ↓ 18.0 3,601 1

Finalize GroupAggregate (cost=5,371.52..5,379.52 rows=200 width=76) (actual time=158.827..162.212 rows=3,601 loops=1)

  • Group Key: component_loads_1."timestamp
18. 0.522 159.195 ↓ 9.0 3,601 1

Sort (cost=5,371.52..5,372.52 rows=400 width=76) (actual time=158.814..159.195 rows=3,601 loops=1)

  • Sort Key: component_loads_1."timestamp
  • Sort Method: quicksort Memory: 603kB
19. 145.891 158.673 ↓ 9.0 3,601 1

Gather (cost=5,312.23..5,354.23 rows=400 width=76) (actual time=119.262..158.673 rows=3,601 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 1.121 12.782 ↓ 6.0 1,200 3 / 3

Partial HashAggregate (cost=4,312.23..4,314.23 rows=200 width=76) (actual time=12.451..12.782 rows=1,200 loops=3)

  • Group Key: component_loads_1."timestamp
21. 0.160 11.661 ↓ 1.0 1,200 3 / 3

Custom Scan (ChunkAppend) on component_loads component_loads_1 (cost=0.42..4,300.26 rows=1,197 width=20) (actual time=0.029..11.661 rows=1,200 loops=3)

  • Chunks excluded during startup: 256
22. 11.501 11.501 ↓ 1.3 1,200 3 / 3

Parallel Index Scan Backward using _hyper_4_3244_chunk_component_loads_timestamp_idx on _hyper_4_3244_chunk _hyper_4_3244_chunk_1 (cost=0.43..3,626.75 rows=941 width=20) (actual time=0.028..11.501 rows=1,200 loops=3)

  • Index Cond: (("timestamp" >= to_timestamp('2019-09-25 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)) AND ("timestamp" <= to_timestamp('2019-09-25 01:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)))
  • Filter: ((core_number = 1) AND ((component_name)::text = 'CPU'::text) AND (domain_id = 61))
  • Rows Removed by Filter: 45,587
23.          

CTE cte_d

24. 0.000 167.930 ↓ 0.0 0 1

Finalize GroupAggregate (cost=5,312.16..5,373.39 rows=200 width=76) (actual time=167.930..167.930 rows=0 loops=1)

  • Group Key: component_loads_2."timestamp
25. 157.951 168.984 ↓ 0.0 0 1

Gather Merge (cost=5,312.16..5,366.39 rows=400 width=76) (actual time=167.928..168.984 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
26. 0.001 11.033 ↓ 0.0 0 3 / 3

Partial GroupAggregate (cost=4,312.13..4,320.20 rows=200 width=76) (actual time=11.033..11.033 rows=0 loops=3)

  • Group Key: component_loads_2."timestamp
27. 0.038 11.032 ↓ 0.0 0 3 / 3

Sort (cost=4,312.13..4,313.35 rows=485 width=20) (actual time=11.032..11.032 rows=0 loops=3)

  • Sort Key: component_loads_2."timestamp
  • Sort Method: quicksort Memory: 25kB
28. 0.001 10.994 ↓ 0.0 0 3 / 3

Custom Scan (ChunkAppend) on component_loads component_loads_2 (cost=0.42..4,290.50 rows=485 width=20) (actual time=10.994..10.994 rows=0 loops=3)

  • Chunks excluded during startup: 256
29. 10.993 10.993 ↓ 0.0 0 3 / 3

Parallel Index Scan Backward using _hyper_4_3244_chunk_component_loads_timestamp_idx on _hyper_4_3244_chunk _hyper_4_3244_chunk_2 (cost=0.43..3,626.75 rows=229 width=20) (actual time=10.993..10.993 rows=0 loops=3)

  • Index Cond: (("timestamp" >= to_timestamp('2019-09-25 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)) AND ("timestamp" <= to_timestamp('2019-09-25 01:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)))
  • Filter: ((core_number = 2) AND ((component_name)::text = 'CPU'::text) AND (domain_id = 61))
  • Rows Removed by Filter: 46,787
30.          

CTE cte_e

31. 0.000 183.864 ↓ 0.0 0 1

Finalize GroupAggregate (cost=5,312.26..5,373.52 rows=200 width=76) (actual time=183.864..183.864 rows=0 loops=1)

  • Group Key: component_loads_3."timestamp
32. 173.530 184.777 ↓ 0.0 0 1

Gather Merge (cost=5,312.26..5,366.52 rows=400 width=76) (actual time=183.862..184.777 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
33. 0.001 11.247 ↓ 0.0 0 3 / 3

Partial GroupAggregate (cost=4,312.24..4,320.32 rows=200 width=76) (actual time=11.247..11.247 rows=0 loops=3)

  • Group Key: component_loads_3."timestamp
34. 0.030 11.246 ↓ 0.0 0 3 / 3

Sort (cost=4,312.24..4,313.45 rows=487 width=20) (actual time=11.246..11.246 rows=0 loops=3)

  • Sort Key: component_loads_3."timestamp
  • Sort Method: quicksort Memory: 25kB
35. 0.001 11.216 ↓ 0.0 0 3 / 3

Custom Scan (ChunkAppend) on component_loads component_loads_3 (cost=0.42..4,290.50 rows=487 width=20) (actual time=11.216..11.216 rows=0 loops=3)

  • Chunks excluded during startup: 256
36. 11.215 11.215 ↓ 0.0 0 3 / 3

Parallel Index Scan Backward using _hyper_4_3244_chunk_component_loads_timestamp_idx on _hyper_4_3244_chunk _hyper_4_3244_chunk_3 (cost=0.43..3,626.75 rows=231 width=20) (actual time=11.215..11.215 rows=0 loops=3)

  • Index Cond: (("timestamp" >= to_timestamp('2019-09-25 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)) AND ("timestamp" <= to_timestamp('2019-09-25 01:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)))
  • Filter: ((core_number = 3) AND ((component_name)::text = 'CPU'::text) AND (domain_id = 61))
  • Rows Removed by Filter: 46,787
37.          

CTE cte_f

38. 0.002 12.117 ↓ 0.0 0 1

GroupAggregate (cost=2,610.02..2,616.46 rows=200 width=76) (actual time=12.117..12.117 rows=0 loops=1)

  • Group Key: component_loads_4."timestamp
39. 0.004 12.115 ↓ 0.0 0 1

Sort (cost=2,610.02..2,610.71 rows=275 width=20) (actual time=12.115..12.115 rows=0 loops=1)

  • Sort Key: component_loads_4."timestamp
  • Sort Method: quicksort Memory: 25kB
40. 0.002 12.111 ↓ 0.0 0 1

Custom Scan (ChunkAppend) on component_loads component_loads_4 (cost=0.42..2,598.88 rows=275 width=20) (actual time=12.111..12.111 rows=0 loops=1)

  • Chunks excluded during startup: 256
41. 2.771 12.109 ↓ 0.0 0 1

Bitmap Heap Scan on _hyper_4_3244_chunk _hyper_4_3244_chunk_4 (cost=1,672.56..1,935.25 rows=19 width=20) (actual time=12.109..12.109 rows=0 loops=1)

  • Recheck Cond: ((core_number = 4) AND ("timestamp" >= to_timestamp('2019-09-25 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)) AND ("timestamp" <= to_timestamp('2019-09-25 01:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)))
  • Filter: (((component_name)::text = 'CPU'::text) AND (domain_id = 61))
  • Rows Removed by Filter: 3,598
  • Heap Blocks: exact=1,102
42. 0.140 9.338 ↓ 0.0 0 1

BitmapAnd (cost=1,672.56..1,672.56 rows=236 width=0) (actual time=9.338..9.338 rows=0 loops=1)

43. 1.122 1.122 ↓ 1.7 10,158 1

Bitmap Index Scan on _hyper_4_3244_chunk_component_loads_core_number (cost=0.00..68.28 rows=5,967 width=0) (actual time=1.122..1.122 rows=10,158 loops=1)

  • Index Cond: (core_number = 4)
44. 8.076 8.076 ↓ 1.2 140,361 1

Bitmap Index Scan on _hyper_4_3244_chunk_component_loads_timestamp_idx (cost=0.00..1,604.02 rows=113,608 width=0) (actual time=8.076..8.076 rows=140,361 loops=1)

  • Index Cond: (("timestamp" >= to_timestamp('2019-09-25 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)) AND ("timestamp" <= to_timestamp('2019-09-25 01:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)))
45.          

CTE cte_g

46. 0.001 10.227 ↓ 0.0 0 1

GroupAggregate (cost=2,631.83..2,638.28 rows=200 width=76) (actual time=10.227..10.227 rows=0 loops=1)

  • Group Key: component_loads_5."timestamp
47. 0.003 10.226 ↓ 0.0 0 1

Sort (cost=2,631.83..2,632.52 rows=276 width=20) (actual time=10.226..10.226 rows=0 loops=1)

  • Sort Key: component_loads_5."timestamp
  • Sort Method: quicksort Memory: 25kB
48. 0.001 10.223 ↓ 0.0 0 1

Custom Scan (ChunkAppend) on component_loads component_loads_5 (cost=0.42..2,620.64 rows=276 width=20) (actual time=10.223..10.223 rows=0 loops=1)

  • Chunks excluded during startup: 256
49. 2.170 10.222 ↓ 0.0 0 1

Bitmap Heap Scan on _hyper_4_3244_chunk _hyper_4_3244_chunk_5 (cost=1,677.62..1,957.01 rows=20 width=20) (actual time=10.222..10.222 rows=0 loops=1)

  • Recheck Cond: ((core_number = 5) AND ("timestamp" >= to_timestamp('2019-09-25 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)) AND ("timestamp" <= to_timestamp('2019-09-25 01:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)))
  • Filter: (((component_name)::text = 'CPU'::text) AND (domain_id = 61))
  • Rows Removed by Filter: 3,598
  • Heap Blocks: exact=1,102
50. 0.158 8.052 ↓ 0.0 0 1

BitmapAnd (cost=1,677.62..1,677.62 rows=251 width=0) (actual time=8.052..8.052 rows=0 loops=1)

51. 0.950 0.950 ↓ 1.6 10,158 1

Bitmap Index Scan on _hyper_4_3244_chunk_component_loads_core_number (cost=0.00..73.35 rows=6,349 width=0) (actual time=0.950..0.950 rows=10,158 loops=1)

  • Index Cond: (core_number = 5)
52. 6.944 6.944 ↓ 1.2 140,361 1

Bitmap Index Scan on _hyper_4_3244_chunk_component_loads_timestamp_idx (cost=0.00..1,604.02 rows=113,608 width=0) (actual time=6.944..6.944 rows=140,361 loops=1)

  • Index Cond: (("timestamp" >= to_timestamp('2019-09-25 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)) AND ("timestamp" <= to_timestamp('2019-09-25 01:00:00'::text, 'YYYY-MM-DD HH24:MI:SS:MS'::text)))
53. 0.680 896.133 ↓ 18.0 3,601 1

Hash Left Join (cost=32.50..74.00 rows=200 width=392) (actual time=886.154..896.133 rows=3,601 loops=1)

  • Hash Cond: (a."timestamp" = f."timestamp")
54. 0.713 883.334 ↓ 18.0 3,601 1

Hash Left Join (cost=26.00..60.00 rows=200 width=328) (actual time=874.022..883.334 rows=3,601 loops=1)

  • Hash Cond: (a."timestamp" = e."timestamp")
55. 0.728 698.754 ↓ 18.0 3,601 1

Hash Left Join (cost=19.50..46.00 rows=200 width=264) (actual time=690.123..698.754 rows=3,601 loops=1)

  • Hash Cond: (a."timestamp" = d."timestamp")
56. 1.375 530.094 ↓ 18.0 3,601 1

Hash Left Join (cost=13.00..32.00 rows=200 width=200) (actual time=522.178..530.094 rows=3,601 loops=1)

  • Hash Cond: (a."timestamp" = c."timestamp")
57. 1.351 364.523 ↓ 18.0 3,601 1

Hash Left Join (cost=6.50..18.00 rows=200 width=136) (actual time=357.971..364.523 rows=3,601 loops=1)

  • Hash Cond: (a."timestamp" = b."timestamp")
58. 187.593 187.593 ↓ 18.0 3,601 1

CTE Scan on cte_a a (cost=0.00..4.00 rows=200 width=72) (actual time=182.381..187.593 rows=3,601 loops=1)

59. 0.582 175.579 ↓ 18.0 3,601 1

Hash (cost=4.00..4.00 rows=200 width=72) (actual time=175.579..175.579 rows=3,601 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 201kB
60. 174.997 174.997 ↓ 18.0 3,601 1

CTE Scan on cte_b b (cost=0.00..4.00 rows=200 width=72) (actual time=170.019..174.997 rows=3,601 loops=1)

61. 0.717 164.196 ↓ 18.0 3,601 1

Hash (cost=4.00..4.00 rows=200 width=72) (actual time=164.196..164.196 rows=3,601 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 201kB
62. 163.479 163.479 ↓ 18.0 3,601 1

CTE Scan on cte_c c (cost=0.00..4.00 rows=200 width=72) (actual time=158.831..163.479 rows=3,601 loops=1)

63. 0.001 167.932 ↓ 0.0 0 1

Hash (cost=4.00..4.00 rows=200 width=72) (actual time=167.932..167.932 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
64. 167.931 167.931 ↓ 0.0 0 1

CTE Scan on cte_d d (cost=0.00..4.00 rows=200 width=72) (actual time=167.931..167.931 rows=0 loops=1)

65. 0.001 183.867 ↓ 0.0 0 1

Hash (cost=4.00..4.00 rows=200 width=72) (actual time=183.867..183.867 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
66. 183.866 183.866 ↓ 0.0 0 1

CTE Scan on cte_e e (cost=0.00..4.00 rows=200 width=72) (actual time=183.866..183.866 rows=0 loops=1)

67. 0.001 12.119 ↓ 0.0 0 1

Hash (cost=4.00..4.00 rows=200 width=72) (actual time=12.119..12.119 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
68. 12.118 12.118 ↓ 0.0 0 1

CTE Scan on cte_f f (cost=0.00..4.00 rows=200 width=72) (actual time=12.118..12.118 rows=0 loops=1)

69. 0.000 10.228 ↓ 0.0 0 1

Hash (cost=4.00..4.00 rows=200 width=72) (actual time=10.228..10.228 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
70. 10.228 10.228 ↓ 0.0 0 1

CTE Scan on cte_g g (cost=0.00..4.00 rows=200 width=72) (actual time=10.228..10.228 rows=0 loops=1)

Planning time : 341.886 ms
Execution time : 955.072 ms