explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Scr9 : Need help to improve this.

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 30.732 336,999.213 ↑ 5.4 7,392 1

Sort (cost=3,676,154.71..3,676,354.71 rows=40,000 width=28) (actual time=336,993.685..336,999.213 rows=7,392 loops=1)

  • Sort Key: (time_bucket_gapfill('1 day'::interval, "timestamp")), bucket DESC
  • Sort Method: quicksort Memory: 757kB
  • Buffers: shared hit=27 read=186014, temp read=77965 written=78118
2. 10.342 336,968.481 ↑ 5.4 7,392 1

Custom Scan (GapFill) (cost=3,672,997.17..3,673,097.17 rows=40,000 width=0) (actual time=336,954.188..336,968.481 rows=7,392 loops=1)

  • Buffers: shared hit=27 read=186014, temp read=77965 written=78118
3. 72.335 336,958.139 ↑ 5.7 6,963 1

Sort (cost=3,672,997.17..3,673,097.17 rows=40,000 width=0) (actual time=336,954.137..336,958.139 rows=6,963 loops=1)

  • Sort Key: _hyper_1_4_chunk.bucket DESC, (time_bucket_gapfill('1 day'::interval, _hyper_1_4_chunk."timestamp"))
  • Sort Method: quicksort Memory: 736kB
  • Buffers: shared hit=27 read=186014, temp read=77965 written=78118
4. 109.208 336,885.804 ↑ 5.7 6,963 1

Finalize GroupAggregate (cost=3,489,727.36..3,669,939.62 rows=40,000 width=0) (actual time=291,121.796..336,885.804 rows=6,963 loops=1)

  • Group Key: (time_bucket_gapfill('1 day'::interval, _hyper_1_4_chunk."timestamp")), _hyper_1_4_chunk.bucket
  • Buffers: shared hit=27 read=186014, temp read=77965 written=78118
5. 35,479.899 336,776.596 ↑ 19.3 8,283 1

Gather Merge (cost=3,489,727.36..3,667,739.62 rows=160,000 width=52) (actual time=291,118.450..336,776.596 rows=8,283 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=176 read=1266620, temp read=567484 written=568527
6. 15,207.488 301,296.697 ↑ 24.1 1,657 5 / 5

Partial GroupAggregate (cost=3,488,727.30..3,647,682.02 rows=40,000 width=52) (actual time=264,125.306..301,296.697 rows=1,657 loops=5)

  • Group Key: (time_bucket_gapfill('1 day'::interval, _hyper_1_4_chunk."timestamp")), _hyper_1_4_chunk.bucket
  • Buffers: shared hit=176 read=1266620, temp read=567484 written=568527
7. 173,675.823 286,089.209 ↑ 1.3 10,139,000 5 / 5

Sort (cost=3,488,727.30..3,520,418.24 rows=12,676,378 width=20) (actual time=264,068.931..286,089.209 rows=10,139,000 loops=5)

  • Sort Key: (time_bucket_gapfill('1 day'::interval, _hyper_1_4_chunk."timestamp")), _hyper_1_4_chunk.bucket DESC
  • Sort Method: external merge Disk: 247816kB
  • Worker 0: Sort Method: external merge Disk: 350912kB
  • Worker 1: Sort Method: external merge Disk: 350928kB
  • Worker 2: Sort Method: external merge Disk: 367320kB
  • Worker 3: Sort Method: external merge Disk: 370032kB
  • Buffers: shared hit=176 read=1266620, temp read=567484 written=568527
8. 4,361.513 112,413.386 ↑ 1.3 10,139,000 5 / 5

Result (cost=0.00..1,688,659.49 rows=12,676,378 width=20) (actual time=28.031..112,413.386 rows=10,139,000 loops=5)

  • Buffers: shared hit=36 read=1266620
9. 2,352.306 108,051.873 ↑ 1.3 10,139,000 5 / 5

Parallel Append (cost=0.00..1,530,204.76 rows=12,676,378 width=20) (actual time=28.026..108,051.873 rows=10,139,000 loops=5)

  • Buffers: shared hit=36 read=1266620
10. 7,256.287 7,256.287 ↓ 4.0 4,698,225 1 / 5

Parallel Seq Scan on _hyper_1_4_chunk (cost=0.00..135,012.24 rows=1,174,549 width=20) (actual time=42.583..36,281.433 rows=4,698,225 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=117392
11. 7,938.218 7,938.218 ↓ 4.0 4,305,194 1 / 5

Parallel Seq Scan on _hyper_1_5_chunk (cost=0.00..123,721.87 rows=1,076,258 width=20) (actual time=40.984..39,691.090 rows=4,305,194 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=107576
12. 4,813.099 4,813.099 ↓ 4.0 3,474,669 1 / 5

Parallel Seq Scan on _hyper_1_25_chunk (cost=0.00..99,842.17 rows=868,878 width=20) (actual time=47.732..24,065.493 rows=3,474,669 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=86807
13. 5,450.990 5,450.990 ↓ 4.0 3,473,053 1 / 5

Parallel Seq Scan on _hyper_1_11_chunk (cost=0.00..99,800.12 rows=868,475 width=20) (actual time=5.178..27,254.950 rows=3,473,053 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=86771
14. 6,753.803 6,753.803 ↓ 4.0 3,472,032 1 / 5

Parallel Seq Scan on _hyper_1_20_chunk (cost=0.00..99,770.43 rows=868,228 width=20) (actual time=5.895..33,769.014 rows=3,472,032 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=1 read=86746
15. 6,471.884 6,471.884 ↓ 4.0 3,424,649 1 / 5

Parallel Seq Scan on _hyper_1_2_chunk (cost=0.00..98,409.33 rows=856,155 width=20) (actual time=37.900..32,359.419 rows=3,424,649 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=85567
16. 8,131.647 8,131.647 ↓ 4.0 3,295,735 1 / 5

Parallel Seq Scan on _hyper_1_26_chunk (cost=0.00..94,705.61 rows=824,174 width=20) (actual time=14.756..40,658.234 rows=3,295,735 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=82343
17. 4,942.589 4,942.589 ↓ 4.0 3,262,285 1 / 5

Parallel Seq Scan on _hyper_1_7_chunk (cost=0.00..93,748.27 rows=815,751 width=20) (actual time=13.399..24,712.944 rows=3,262,285 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=81512
18. 5,585.647 5,585.647 ↓ 4.0 3,249,450 1 / 5

Parallel Seq Scan on _hyper_1_29_chunk (cost=0.00..93,374.59 rows=812,573 width=20) (actual time=58.883..27,928.237 rows=3,249,450 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=81186
19. 6,064.393 6,064.393 ↓ 4.0 2,983,371 1 / 5

Parallel Seq Scan on _hyper_1_3_chunk (cost=0.00..85,757.30 rows=746,287 width=20) (actual time=58.954..30,321.964 rows=2,983,371 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=74563
20. 4,590.711 4,590.711 ↓ 4.0 2,980,434 1 / 5

Parallel Seq Scan on _hyper_1_1_chunk (cost=0.00..85,671.57 rows=745,571 width=20) (actual time=95.333..22,953.556 rows=2,980,434 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=74488
21. 6,963.816 6,963.816 ↓ 2.0 1,490,118 2 / 5

Parallel Seq Scan on _hyper_1_6_chunk (cost=0.00..85,668.51 rows=745,501 width=20) (actual time=20.950..17,409.541 rows=1,490,118 loops=2)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=74486
22. 9,449.814 9,449.814 ↑ 1.3 344,404 5 / 5

Parallel Seq Scan on _hyper_1_30_chunk (cost=0.00..49,471.67 rows=430,511 width=20) (actual time=14.010..9,449.814 rows=344,404 loops=5)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=43014
23. 6,141.502 6,141.502 ↓ 2.0 860,711 2 / 5

Parallel Seq Scan on _hyper_1_32_chunk (cost=0.00..49,454.43 rows=430,362 width=20) (actual time=35.804..15,353.756 rows=860,711 loops=2)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=42999
24. 4,616.779 4,616.779 ↓ 4.0 1,719,190 1 / 5

Parallel Seq Scan on _hyper_1_31_chunk (cost=0.00..49,391.11 rows=429,808 width=20) (actual time=20.712..23,083.893 rows=1,719,190 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=42944
25. 1,113.143 1,113.143 ↓ 2.4 317,228 1 / 5

Parallel Seq Scan on _hyper_1_22_chunk (cost=0.00..9,909.14 rows=132,210 width=20) (actual time=22.335..5,565.713 rows=317,228 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=7926
26. 129.375 129.375 ↓ 2.4 228,121 1 / 5

Parallel Seq Scan on _hyper_1_18_chunk (cost=0.00..7,123.91 rows=95,060 width=20) (actual time=27.316..646.877 rows=228,121 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=5698
27. 492.044 492.044 ↓ 2.4 228,042 1 / 5

Parallel Seq Scan on _hyper_1_12_chunk (cost=0.00..7,122.44 rows=95,030 width=20) (actual time=6.516..2,460.218 rows=228,042 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=5697
28. 1,003.319 1,003.319 ↓ 2.4 227,725 1 / 5

Parallel Seq Scan on _hyper_1_19_chunk (cost=0.00..7,112.56 rows=94,904 width=20) (actual time=6.686..5,016.597 rows=227,725 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared read=5689
29. 1,416.643 1,416.643 ↓ 2.4 227,434 1 / 5

Parallel Seq Scan on _hyper_1_27_chunk (cost=0.00..7,104.75 rows=94,783 width=20) (actual time=43.287..7,083.216 rows=227,434 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=1 read=5682
30. 654.836 654.836 ↓ 2.4 227,389 1 / 5

Parallel Seq Scan on _hyper_1_15_chunk (cost=0.00..7,101.18 rows=94,745 width=20) (actual time=8.604..3,274.179 rows=227,389 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5678
31. 559.838 559.838 ↓ 2.4 227,361 1 / 5

Parallel Seq Scan on _hyper_1_23_chunk (cost=0.00..7,101.09 rows=94,740 width=20) (actual time=19.671..2,799.188 rows=227,361 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5678
32. 277.581 277.581 ↓ 2.4 227,330 1 / 5

Parallel Seq Scan on _hyper_1_13_chunk (cost=0.00..7,099.79 rows=94,719 width=20) (actual time=8.492..1,387.903 rows=227,330 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5677
33. 332.366 332.366 ↓ 2.4 227,195 1 / 5

Parallel Seq Scan on _hyper_1_9_chunk (cost=0.00..7,095.08 rows=94,672 width=20) (actual time=7.866..1,661.832 rows=227,195 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5673
34. 669.376 669.376 ↓ 2.4 227,186 1 / 5

Parallel Seq Scan on _hyper_1_24_chunk (cost=0.00..7,095.01 rows=94,668 width=20) (actual time=14.814..3,346.878 rows=227,186 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5673
35. 584.860 584.860 ↓ 2.4 227,161 1 / 5

Parallel Seq Scan on _hyper_1_10_chunk (cost=0.00..7,094.82 rows=94,655 width=20) (actual time=4.182..2,924.301 rows=227,161 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5673
36. 328.828 328.828 ↓ 2.4 226,962 1 / 5

Parallel Seq Scan on _hyper_1_28_chunk (cost=0.00..7,089.19 rows=94,546 width=20) (actual time=29.557..1,644.138 rows=226,962 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5669
37. 663.165 663.165 ↓ 2.4 226,996 1 / 5

Parallel Seq Scan on _hyper_1_17_chunk (cost=0.00..7,088.80 rows=94,587 width=20) (actual time=4.821..3,315.825 rows=226,996 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5668
38. 396.167 396.167 ↓ 2.4 226,968 1 / 5

Parallel Seq Scan on _hyper_1_14_chunk (cost=0.00..7,087.80 rows=94,520 width=20) (actual time=6.177..1,980.836 rows=226,968 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5668
39. 803.729 803.729 ↓ 2.4 226,775 1 / 5

Parallel Seq Scan on _hyper_1_8_chunk (cost=0.00..7,082.34 rows=94,490 width=20) (actual time=5.066..4,018.647 rows=226,775 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5663
40. 286.768 286.768 ↓ 2.4 226,551 1 / 5

Parallel Seq Scan on _hyper_1_21_chunk (cost=0.00..7,075.12 rows=94,408 width=20) (actual time=3.622..1,433.841 rows=226,551 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=5657
41. 480.013 480.013 ↓ 2.4 133,493 1 / 5

Parallel Seq Scan on _hyper_1_16_chunk (cost=0.00..4,168.33 rows=55,622 width=20) (actual time=6.278..2,400.065 rows=133,493 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=3332
42. 336.338 336.338 ↓ 1.7 73,120 1 / 5

Parallel Seq Scan on _hyper_1_33_chunk (cost=0.00..2,472.26 rows=43,017 width=20) (actual time=3.640..1,681.691 rows=73,120 loops=1)

  • Filter: (("timestamp" >= '2018-06-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2020-04-20 14:40:00'::timestamp without time zone))
  • Buffers: shared hit=2 read=1825
Planning time : 1,688.305 ms
Execution time : 337,042.154 ms