explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qtb6

Settings
# exclusive inclusive rows x rows loops node
1. 26,368.639 200,855.117 ↑ 200.0 1 1

HashAggregate (cost=4,242,601.15..4,242,604.15 rows=200 width=40) (actual time=200,855.103..200,855.117 rows=1 loops=1)

  • Group Key: time_bucket('50 days'::interval, tick."time")
  • Buffers: shared hit=62 read=1598269
2. 48,851.157 174,486.478 ↓ 1.0 3,352,851 1

Custom Scan (ConstraintAwareAppend) (cost=0.43..4,217,717.08 rows=3,317,876 width=12) (actual time=4.165..174,486.478 rows=3,352,851 loops=1)

  • Hypertable: tick
  • Chunks left after exclusion: 27
  • Buffers: shared hit=62 read=1598269
3. 48,461.959 125,635.321 ↓ 1.0 3,352,851 1

Append (cost=0.43..4,209,422.39 rows=3,317,876 width=12) (actual time=4.148..125,635.321 rows=3,352,851 loops=1)

  • Buffers: shared hit=62 read=1598269
4. 3,544.414 3,544.414 ↑ 1.0 118,817 1

Index Scan using _hyper_2_197_chunk_tick_time_symbol_idx on _hyper_2_197_chunk (cost=0.56..179,095.65 rows=119,287 width=12) (actual time=4.133..3,544.414 rows=118,817 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=2 read=67799
5. 7.387 7.387 ↓ 0.0 0 1

Index Scan using _hyper_2_198_chunk_tick_time_symbol_idx on _hyper_2_198_chunk (cost=0.42..866.36 rows=1 width=12) (actual time=7.381..7.387 rows=0 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=4 read=235
6. 157.342 225.481 ↑ 1.0 11,878 1

Bitmap Heap Scan on _hyper_2_199_chunk (cost=8,137.29..11,770.36 rows=12,083 width=12) (actual time=68.374..225.481 rows=11,878 loops=1)

  • Recheck Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Heap Blocks: exact=1461
  • Buffers: shared hit=5 read=3689
7. 68.139 68.139 ↑ 1.0 11,878 1

Bitmap Index Scan on _hyper_2_199_chunk_tick_time_symbol_idx (cost=0.00..8,134.27 rows=12,083 width=0) (actual time=68.132..68.139 rows=11,878 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=4 read=2229
8. 5,405.719 5,405.719 ↑ 1.0 150,878 1

Index Scan using _hyper_2_200_chunk_tick_time_symbol_idx on _hyper_2_200_chunk (cost=0.56..202,951.83 rows=151,287 width=12) (actual time=0.082..5,405.719 rows=150,878 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=6 read=76434
9. 6,005.737 6,005.737 ↓ 1.1 180,677 1

Index Scan using _hyper_2_201_chunk_tick_time_symbol_idx on _hyper_2_201_chunk (cost=0.57..238,320.99 rows=169,394 width=12) (actual time=0.063..6,005.737 rows=180,677 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=6 read=93527
10. 4,294.226 4,294.226 ↑ 1.0 169,605 1

Index Scan using _hyper_2_202_chunk_tick_time_symbol_idx on _hyper_2_202_chunk (cost=0.57..247,918.98 rows=173,371 width=12) (actual time=0.120..4,294.226 rows=169,605 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=5 read=95274
11. 4,786.214 4,786.214 ↓ 1.0 194,367 1

Index Scan using _hyper_2_203_chunk_tick_time_symbol_idx on _hyper_2_203_chunk (cost=0.57..271,892.14 rows=192,312 width=12) (actual time=0.114..4,786.214 rows=194,367 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=5 read=106331
12. 3,637.885 3,637.885 ↓ 1.0 158,520 1

Index Scan using _hyper_2_204_chunk_tick_time_symbol_idx on _hyper_2_204_chunk (cost=0.57..216,838.74 rows=158,469 width=12) (actual time=0.109..3,637.885 rows=158,520 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=4 read=81292
13. 8.692 8.692 ↓ 0.0 0 1

Index Scan using _hyper_2_205_chunk_tick_time_symbol_idx on _hyper_2_205_chunk (cost=0.42..947.44 rows=1 width=12) (actual time=8.685..8.692 rows=0 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=1 read=260
14. 164.760 227.542 ↓ 1.0 10,959 1

Bitmap Heap Scan on _hyper_2_206_chunk (cost=7,363.59..10,641.47 rows=10,555 width=12) (actual time=63.045..227.542 rows=10,959 loops=1)

  • Recheck Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Heap Blocks: exact=1517
  • Buffers: shared hit=1 read=3537
15. 62.782 62.782 ↓ 1.0 10,959 1

Bitmap Index Scan on _hyper_2_206_chunk_tick_time_symbol_idx (cost=0.00..7,360.95 rows=10,555 width=0) (actual time=62.775..62.782 rows=10,959 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=1 read=2020
16. 3,154.095 3,154.095 ↓ 1.0 142,828 1

Index Scan using _hyper_2_207_chunk_tick_time_symbol_idx on _hyper_2_207_chunk (cost=0.56..183,889.85 rows=140,810 width=12) (actual time=0.134..3,154.095 rows=142,828 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=1 read=66661
17. 3,299.089 3,299.089 ↓ 1.0 147,390 1

Index Scan using _hyper_2_208_chunk_tick_time_symbol_idx on _hyper_2_208_chunk (cost=0.56..191,787.94 rows=144,371 width=12) (actual time=0.101..3,299.089 rows=147,390 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=1 read=71196
18. 2,662.339 2,662.339 ↓ 1.0 128,782 1

Index Scan using _hyper_2_209_chunk_tick_time_symbol_idx on _hyper_2_209_chunk (cost=0.56..156,174.06 rows=123,129 width=12) (actual time=0.106..2,662.339 rows=128,782 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=1 read=58977
19. 2,257.454 3,360.675 ↓ 1.0 168,794 1

Bitmap Heap Scan on _hyper_2_210_chunk (cost=136,374.84..196,386.04 rows=167,008 width=12) (actual time=1,111.294..3,360.675 rows=168,794 loops=1)

  • Recheck Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Heap Blocks: exact=37205
  • Buffers: shared read=74615
20. 1,103.221 1,103.221 ↓ 1.0 168,794 1

Bitmap Index Scan on _hyper_2_210_chunk_tick_time_symbol_idx (cost=0.00..136,333.09 rows=167,008 width=0) (actual time=1,103.214..1,103.221 rows=168,794 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=37410
21. 3,606.100 3,606.100 ↓ 1.0 174,426 1

Index Scan using _hyper_2_211_chunk_tick_time_symbol_idx on _hyper_2_211_chunk (cost=0.57..222,908.95 rows=171,802 width=12) (actual time=0.148..3,606.100 rows=174,426 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=87078
22. 420.886 420.886 ↑ 1.0 19,027 1

Index Scan using _hyper_2_212_chunk_tick_time_symbol_idx on _hyper_2_212_chunk (cost=0.43..24,398.30 rows=19,772 width=12) (actual time=0.110..420.886 rows=19,027 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=8648
23. 4,496.476 4,496.476 ↑ 1.0 191,124 1

Index Scan using _hyper_2_213_chunk_tick_time_symbol_idx on _hyper_2_213_chunk (cost=0.57..256,751.85 rows=195,244 width=12) (actual time=0.128..4,496.476 rows=191,124 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=99249
24. 3,835.338 3,835.338 ↓ 1.0 160,226 1

Index Scan using _hyper_2_214_chunk_tick_time_symbol_idx on _hyper_2_214_chunk (cost=0.57..215,231.03 rows=159,935 width=12) (actual time=0.114..3,835.338 rows=160,226 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=80979
25. 2,177.196 3,233.535 ↑ 1.0 159,204 1

Bitmap Heap Scan on _hyper_2_215_chunk (cost=128,638.69..185,333.52 rows=161,113 width=12) (actual time=1,064.003..3,233.535 rows=159,204 loops=1)

  • Recheck Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Heap Blocks: exact=35024
  • Buffers: shared read=70312
26. 1,056.339 1,056.339 ↑ 1.0 159,204 1

Bitmap Index Scan on _hyper_2_215_chunk_tick_time_symbol_idx (cost=0.00..128,598.42 rows=161,113 width=0) (actual time=1,056.332..1,056.339 rows=159,204 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=35288
27. 3,267.375 3,267.375 ↑ 1.0 152,545 1

Index Scan using _hyper_2_216_chunk_tick_time_symbol_idx on _hyper_2_216_chunk (cost=0.56..189,512.33 rows=156,972 width=12) (actual time=3.791..3,267.375 rows=152,545 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=70775
28. 3,017.206 3,017.206 ↓ 1.0 131,305 1

Index Scan using _hyper_2_217_chunk_tick_time_symbol_idx on _hyper_2_217_chunk (cost=0.56..167,469.36 rows=129,693 width=12) (actual time=3.998..3,017.206 rows=131,305 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=64166
29. 228.557 228.557 ↑ 1.0 7,862 1

Index Scan using _hyper_2_218_chunk_tick_time_symbol_idx on _hyper_2_218_chunk (cost=0.43..11,122.37 rows=7,886 width=12) (actual time=2.062..228.557 rows=7,862 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=3696
30. 1,553.429 2,198.913 ↑ 1.0 129,122 1

Bitmap Heap Scan on _hyper_2_219_chunk (cost=77,661.82..112,691.52 rows=129,428 width=12) (actual time=650.477..2,198.913 rows=129,122 loops=1)

  • Recheck Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Heap Blocks: exact=21871
  • Buffers: shared read=43173
31. 645.484 645.484 ↑ 1.0 129,122 1

Bitmap Index Scan on _hyper_2_219_chunk_tick_time_symbol_idx (cost=0.00..77,629.46 rows=129,428 width=0) (actual time=645.478..645.484 rows=129,122 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=21302
32. 2,085.142 3,113.206 ↓ 1.0 161,105 1

Bitmap Heap Scan on _hyper_2_220_chunk (cost=127,894.67..184,194.55 rows=157,475 width=12) (actual time=1,036.789..3,113.206 rows=161,105 loops=1)

  • Recheck Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Heap Blocks: exact=36180
  • Buffers: shared read=71264
33. 1,028.064 1,028.064 ↓ 1.0 161,105 1

Bitmap Index Scan on _hyper_2_220_chunk_tick_time_symbol_idx (cost=0.00..127,855.30 rows=157,475 width=0) (actual time=1,028.058..1,028.064 rows=161,105 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=35084
34. 2,180.769 3,131.865 ↓ 1.0 174,785 1

Bitmap Heap Scan on _hyper_2_221_chunk (cost=117,044.91..169,136.93 rows=166,961 width=12) (actual time=962.201..3,131.865 rows=174,785 loops=1)

  • Recheck Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Heap Blocks: exact=34394
  • Buffers: shared read=66500
35. 951.096 951.096 ↓ 1.0 174,785 1

Bitmap Index Scan on _hyper_2_221_chunk_tick_time_symbol_idx (cost=0.00..117,003.17 rows=166,961 width=0) (actual time=951.089..951.096 rows=174,785 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=32106
36. 2,103.406 3,107.602 ↓ 1.1 162,506 1

Bitmap Heap Scan on _hyper_2_222_chunk (cost=123,375.93..177,738.23 rows=153,972 width=12) (actual time=1,012.535..3,107.602 rows=162,506 loops=1)

  • Recheck Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Heap Blocks: exact=34616
  • Buffers: shared read=68460
37. 1,004.196 1,004.196 ↓ 1.1 162,506 1

Bitmap Index Scan on _hyper_2_222_chunk_tick_time_symbol_idx (cost=0.00..123,337.44 rows=153,972 width=0) (actual time=1,004.189..1,004.196 rows=162,506 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared read=33844
38. 1,924.132 2,896.808 ↓ 1.0 146,119 1

Bitmap Heap Scan on _hyper_2_223_chunk (cost=115,608.99..166,577.91 rows=145,437 width=12) (actual time=980.383..2,896.808 rows=146,119 loops=1)

  • Recheck Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Heap Blocks: exact=32448
  • Buffers: shared hit=20 read=64142
39. 972.676 972.676 ↓ 1.0 146,119 1

Bitmap Index Scan on _hyper_2_223_chunk_tick_time_symbol_idx (cost=0.00..115,572.63 rows=145,437 width=0) (actual time=972.670..972.676 rows=146,119 loops=1)

  • Index Cond: (("time" >= (CURRENT_TIMESTAMP - '30 days'::interval)) AND ("time" <= now()) AND (symbol = 'GBPUSD'::bpchar))
  • Buffers: shared hit=18 read=31696
Planning time : 971.247 ms
Execution time : 200,863.757 ms