explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YcAh : Query 1 corore

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,286.062 26,015.627 ↓ 29.0 1,158,531 1

Sort (cost=742,586.06..742,786.06 rows=40,000 width=28) (actual time=25,500.324..26,015.627 rows=1,158,531 loops=1)

  • Sort Key: (time_bucket_gapfill('00:05:00'::interval, "timestamp")), bucket DESC
  • Sort Method: external merge Disk: 45752kB
2. 242.814 24,729.565 ↓ 29.0 1,158,531 1

Custom Scan (GapFill) (cost=739,428.51..739,528.51 rows=40,000 width=0) (actual time=23,855.843..24,729.565 rows=1,158,531 loops=1)

3. 2,297.279 24,486.751 ↓ 26.2 1,046,169 1

Sort (cost=739,428.51..739,528.51 rows=40,000 width=0) (actual time=23,855.837..24,486.751 rows=1,046,169 loops=1)

  • Sort Key: _hyper_1_14_chunk.bucket DESC, (time_bucket_gapfill('00:05:00'::interval, _hyper_1_14_chunk."timestamp"))
  • Sort Method: external merge Disk: 42912kB
4. 1,965.983 22,189.472 ↓ 26.2 1,046,169 1

Finalize GroupAggregate (cost=734,270.97..736,370.97 rows=40,000 width=0) (actual time=17,517.036..22,189.472 rows=1,046,169 loops=1)

  • Group Key: (time_bucket_gapfill('00:05:00'::interval, _hyper_1_14_chunk."timestamp")), _hyper_1_14_chunk.bucket
5. 9,802.109 20,223.489 ↓ 30.4 3,645,057 1

Sort (cost=734,270.97..734,570.97 rows=120,000 width=52) (actual time=17,517.005..20,223.489 rows=3,645,057 loops=1)

  • Sort Key: (time_bucket_gapfill('00:05:00'::interval, _hyper_1_14_chunk."timestamp")), _hyper_1_14_chunk.bucket DESC
  • Sort Method: external merge Disk: 292192kB
6. 1,466.996 10,421.380 ↓ 30.4 3,645,057 1

Gather (cost=674,062.13..721,744.77 rows=120,000 width=52) (actual time=6,691.658..10,421.380 rows=3,645,057 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
7. 1,175.266 8,954.384 ↓ 22.8 911,264 4

Partial GroupAggregate (cost=673,062.13..708,744.77 rows=40,000 width=52) (actual time=6,553.285..8,954.384 rows=911,264 loops=4)

  • Group Key: (time_bucket_gapfill('00:05:00'::interval, _hyper_1_14_chunk."timestamp")), _hyper_1_14_chunk.bucket
8. 5,043.918 7,779.118 ↑ 1.3 2,158,310 4

Sort (cost=673,062.13..680,098.66 rows=2,814,611 width=20) (actual time=6,553.258..7,779.118 rows=2,158,310 loops=4)

  • Sort Key: (time_bucket_gapfill('00:05:00'::interval, _hyper_1_14_chunk."timestamp")), _hyper_1_14_chunk.bucket DESC
  • Sort Method: external merge Disk: 76344kB
9. 731.667 2,735.200 ↑ 1.3 2,158,310 4

Result (cost=0.00..303,936.70 rows=2,814,611 width=20) (actual time=0.083..2,735.200 rows=2,158,310 loops=4)

10. 342.311 2,003.533 ↑ 1.3 2,158,310 4

Append (cost=0.00..268,754.07 rows=2,814,611 width=20) (actual time=0.031..2,003.533 rows=2,158,310 loops=4)

11. 58.402 58.402 ↑ 1.3 39,784 4

Parallel Seq Scan on _hyper_1_14_chunk (cost=0.00..15,571.15 rows=51,282 width=20) (actual time=0.031..58.402 rows=39,784 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
  • Rows Removed by Filter: 90734
12. 78.534 78.534 ↑ 1.3 130,515 4

Parallel Seq Scan on _hyper_1_17_chunk (cost=0.00..15,570.95 rows=168,464 width=20) (actual time=0.025..78.534 rows=130,515 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
13. 91.480 91.480 ↑ 1.3 130,735 4

Parallel Seq Scan on _hyper_1_12_chunk (cost=0.00..15,594.96 rows=168,731 width=20) (actual time=0.034..91.480 rows=130,735 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
14. 111.995 111.995 ↑ 1.3 130,628 4

Parallel Seq Scan on _hyper_1_8_chunk (cost=0.00..15,584.11 rows=168,607 width=20) (actual time=0.039..111.995 rows=130,628 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
15. 117.595 117.595 ↑ 1.3 130,778 4

Parallel Seq Scan on _hyper_1_1_chunk (cost=0.00..15,601.81 rows=168,787 width=20) (actual time=0.036..117.595 rows=130,778 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
16. 85.535 85.535 ↑ 1.3 130,805 4

Parallel Seq Scan on _hyper_1_15_chunk (cost=0.00..15,604.71 rows=168,781 width=20) (actual time=0.028..85.535 rows=130,805 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
17. 88.495 88.495 ↑ 1.3 130,534 4

Parallel Seq Scan on _hyper_1_10_chunk (cost=0.00..15,572.46 rows=168,431 width=20) (actual time=0.024..88.495 rows=130,534 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
18. 101.431 101.431 ↑ 1.3 130,474 4

Parallel Seq Scan on _hyper_1_4_chunk (cost=0.00..15,566.18 rows=168,412 width=20) (actual time=0.041..101.431 rows=130,474 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
19. 114.003 114.003 ↑ 1.3 130,932 4

Parallel Seq Scan on _hyper_1_18_chunk (cost=0.00..15,619.97 rows=168,998 width=20) (actual time=0.058..114.003 rows=130,932 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
20. 109.817 109.817 ↑ 1.3 130,729 4

Parallel Seq Scan on _hyper_1_16_chunk (cost=0.00..15,598.24 rows=168,683 width=20) (actual time=0.065..109.817 rows=130,729 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
21. 89.869 89.869 ↑ 1.3 130,884 4

Parallel Seq Scan on _hyper_1_6_chunk (cost=0.00..15,615.24 rows=168,883 width=20) (actual time=0.048..89.869 rows=130,884 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
22. 18.700 18.700 ↑ 2.4 27,550 4

Parallel Seq Scan on _hyper_1_19_chunk (cost=0.00..3,726.64 rows=64,843 width=20) (actual time=0.027..18.700 rows=27,550 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
23. 105.637 105.637 ↑ 1.3 130,764 4

Parallel Seq Scan on _hyper_1_21_chunk (cost=0.00..15,601.71 rows=168,781 width=20) (actual time=0.035..105.637 rows=130,764 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
24. 91.589 91.589 ↑ 1.3 130,630 4

Parallel Seq Scan on _hyper_1_2_chunk (cost=0.00..15,583.96 rows=168,597 width=20) (actual time=0.028..91.589 rows=130,630 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
25. 86.661 86.661 ↑ 1.3 130,637 4

Parallel Seq Scan on _hyper_1_3_chunk (cost=0.00..15,585.22 rows=168,615 width=20) (actual time=0.025..86.661 rows=130,637 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
26. 121.137 121.137 ↑ 1.3 130,862 4

Parallel Seq Scan on _hyper_1_5_chunk (cost=0.00..15,611.81 rows=168,854 width=20) (actual time=0.036..121.137 rows=130,862 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
27. 95.406 95.406 ↑ 1.3 130,314 4

Parallel Seq Scan on _hyper_1_13_chunk (cost=0.00..15,545.21 rows=168,147 width=20) (actual time=0.022..95.406 rows=130,314 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
28. 94.936 94.936 ↑ 1.3 130,754 4

Parallel Seq Scan on _hyper_1_7_chunk (cost=0.00..15,599.72 rows=168,715 width=20) (actual time=0.026..94.936 rows=130,754 loops=4)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
Planning time : 8.229 ms
Execution time : 26,110.488 ms