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. 0.000 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. 76,037.440 1,506,483.485 ↑ 24.1 1,657 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. 868,379.115 1,430,446.045 ↑ 1.3 10,139,000 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. 21,807.565 562,066.930 ↑ 1.3 10,139,000 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. 11,761.529 540,259.365 ↑ 1.3 10,139,000 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. 36,281.433 36,281.433 ↓ 4.0 4,698,225 1

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. 39,691.090 39,691.090 ↓ 4.0 4,305,194 1

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. 24,065.493 24,065.493 ↓ 4.0 3,474,669 1

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. 27,254.950 27,254.950 ↓ 4.0 3,473,053 1

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. 33,769.014 33,769.014 ↓ 4.0 3,472,032 1

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. 32,359.419 32,359.419 ↓ 4.0 3,424,649 1

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. 40,658.234 40,658.234 ↓ 4.0 3,295,735 1

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. 24,712.944 24,712.944 ↓ 4.0 3,262,285 1

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. 27,928.237 27,928.237 ↓ 4.0 3,249,450 1

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. 30,321.964 30,321.964 ↓ 4.0 2,983,371 1

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. 22,953.556 22,953.556 ↓ 4.0 2,980,434 1

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. 34,819.082 34,819.082 ↓ 2.0 1,490,118 2

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. 47,249.070 47,249.070 ↑ 1.3 344,404 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. 30,707.512 30,707.512 ↓ 2.0 860,711 2

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. 23,083.893 23,083.893 ↓ 4.0 1,719,190 1

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. 5,565.713 5,565.713 ↓ 2.4 317,228 1

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. 646.877 646.877 ↓ 2.4 228,121 1

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. 2,460.218 2,460.218 ↓ 2.4 228,042 1

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. 5,016.597 5,016.597 ↓ 2.4 227,725 1

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. 7,083.216 7,083.216 ↓ 2.4 227,434 1

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. 3,274.179 3,274.179 ↓ 2.4 227,389 1

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. 2,799.188 2,799.188 ↓ 2.4 227,361 1

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. 1,387.903 1,387.903 ↓ 2.4 227,330 1

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. 1,661.832 1,661.832 ↓ 2.4 227,195 1

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. 3,346.878 3,346.878 ↓ 2.4 227,186 1

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. 2,924.301 2,924.301 ↓ 2.4 227,161 1

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. 1,644.138 1,644.138 ↓ 2.4 226,962 1

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. 3,315.825 3,315.825 ↓ 2.4 226,996 1

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. 1,980.836 1,980.836 ↓ 2.4 226,968 1

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. 4,018.647 4,018.647 ↓ 2.4 226,775 1

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. 1,433.841 1,433.841 ↓ 2.4 226,551 1

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. 2,400.065 2,400.065 ↓ 2.4 133,493 1

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. 1,681.691 1,681.691 ↓ 1.7 73,120 1

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