explain.depesz.com

PostgreSQL's explain analyze made readable

Result: seoL : Need help to improve this

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.430 168,621.837 ↑ 5.4 7,392 1

Sort (cost=3,676,154.71..3,676,354.71 rows=40,000 width=28) (actual time=168,621.411..168,621.837 rows=7,392 loops=1)

  • Sort Key: (time_bucket_gapfill('1 day'::interval, "timestamp")), bucket DESC
  • Sort Method: quicksort Memory: 757kB
  • Buffers: shared hit=26 read=158811, temp read=63576 written=63707
2. 1.253 168,618.407 ↑ 5.4 7,392 1

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

  • Buffers: shared hit=26 read=158811, temp read=63576 written=63707
3. 8.430 168,617.154 ↑ 5.7 6,963 1

Sort (cost=3,672,997.17..3,673,097.17 rows=40,000 width=0) (actual time=168,616.687..168,617.154 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=26 read=158811, temp read=63576 written=63707
4. 11.158 168,608.724 ↑ 5.7 6,963 1

Finalize GroupAggregate (cost=3,489,727.36..3,669,939.62 rows=40,000 width=0) (actual time=161,303.872..168,608.724 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=26 read=158811, temp read=63576 written=63707
5. 0.000 168,597.566 ↑ 19.3 8,283 1

Gather Merge (cost=3,489,727.36..3,667,739.62 rows=160,000 width=52) (actual time=161,303.458..168,597.566 rows=8,283 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=168 read=1266628, temp read=569672 written=570715
6. 10,339.970 756,232.465 ↑ 24.1 1,657 5

Partial GroupAggregate (cost=3,488,727.30..3,647,682.02 rows=40,000 width=52) (actual time=145,440.898..151,246.493 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=168 read=1266628, temp read=569672 written=570715
7. 315,699.440 745,892.495 ↑ 1.3 10,139,000 5

Sort (cost=3,488,727.30..3,520,418.24 rows=12,676,378 width=20) (actual time=145,434.797..149,178.499 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: 211608kB
  • Worker 0: Sort Method: external merge Disk: 358288kB
  • Worker 1: Sort Method: external merge Disk: 389808kB
  • Worker 2: Sort Method: external merge Disk: 345280kB
  • Worker 3: Sort Method: external merge Disk: 382008kB
  • Buffers: shared hit=168 read=1266628, temp read=569672 written=570715
8. 11,530.985 430,193.055 ↑ 1.3 10,139,000 5

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

  • Buffers: shared hit=36 read=1266620
9. 6,056.800 418,662.070 ↑ 1.3 10,139,000 5

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

  • Buffers: shared hit=36 read=1266620
10. 30,191.427 30,191.427 ↓ 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=81.235..30,191.427 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. 27,726.258 27,726.258 ↓ 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=17.144..27,726.258 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. 25,139.399 25,139.399 ↓ 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=58.222..25,139.399 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. 22,762.071 22,762.071 ↓ 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=14.671..22,762.071 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. 29,387.428 29,387.428 ↓ 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=8.944..29,387.428 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. 25,892.294 25,892.294 ↓ 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=17.517..25,892.294 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. 38,535.816 38,535.816 ↓ 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=5.976..38,535.816 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 hit=1 read=82342
17. 28,065.694 28,065.694 ↓ 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=19.165..28,065.694 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. 25,864.253 25,864.253 ↓ 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=28.847..25,864.253 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. 17,331.414 17,331.414 ↓ 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=80.430..17,331.414 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. 13,020.053 13,020.053 ↓ 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=54.096..13,020.053 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. 18,281.774 18,281.774 ↓ 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=42.509..9,140.887 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. 12,172.510 12,172.510 ↓ 2.0 861,009 2

Parallel Seq Scan on _hyper_1_30_chunk (cost=0.00..49,471.67 rows=430,511 width=20) (actual time=62.948..6,086.255 rows=861,009 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=43014
23. 20,344.630 20,344.630 ↑ 1.3 344,284 5

Parallel Seq Scan on _hyper_1_32_chunk (cost=0.00..49,454.43 rows=430,362 width=20) (actual time=3.787..4,068.926 rows=344,284 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=42999
24. 22,665.108 22,665.108 ↓ 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=37.174..22,665.108 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,499.929 5,499.929 ↓ 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=14.690..5,499.929 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. 3,451.483 3,451.483 ↓ 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=10.011..3,451.483 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. 1,505.074 1,505.074 ↓ 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=46.687..1,505.074 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. 6,640.009 6,640.009 ↓ 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=17.702..6,640.009 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. 11,877.967 11,877.967 ↓ 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=4.818..11,877.967 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 read=5683
30. 2,462.747 2,462.747 ↓ 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=0.227..2,462.747 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. 838.777 838.777 ↓ 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=23.453..838.777 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,113.849 1,113.849 ↓ 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=28.747..1,113.849 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,149.418 1,149.418 ↓ 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=9.079..1,149.418 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. 578.088 578.088 ↓ 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=19.793..578.088 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. 923.535 923.535 ↓ 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.763..923.535 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. 3,238.089 3,238.089 ↓ 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=12.366..3,238.089 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. 2,634.577 2,634.577 ↓ 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=28.205..2,634.577 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. 2,452.407 2,452.407 ↓ 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=16.168..2,452.407 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. 3,028.728 3,028.728 ↓ 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=12.623..3,028.728 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. 4,440.287 4,440.287 ↓ 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=10.741..4,440.287 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. 1,832.593 1,832.593 ↓ 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=15.936..1,832.593 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,557.584 1,557.584 ↓ 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=5.728..1,557.584 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,386.640 ms
Execution time : 168,625.919 ms