explain.depesz.com

PostgreSQL's explain analyze made readable

Result: crNa : Optimization for: Need help to improve this.; plan #Scr9

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.587 15,352.553 ↑ 9.9 4,037 1

Sort (cost=759,359.17..759,559.17 rows=40,000 width=28) (actual time=15,352.191..15,352.553 rows=4,037 loops=1)

  • Sort Key: (time_bucket_gapfill('1 day'::interval, "timestamp")), bucket DESC
  • Sort Method: quicksort Memory: 412kB
2. 0.897 15,349.966 ↑ 9.9 4,037 1

Custom Scan (GapFill) (cost=756,201.63..756,301.63 rows=40,000 width=0) (actual time=15,348.652..15,349.966 rows=4,037 loops=1)

3. 3.223 15,349.069 ↑ 9.9 4,037 1

Sort (cost=756,201.63..756,301.63 rows=40,000 width=0) (actual time=15,348.645..15,349.069 rows=4,037 loops=1)

  • Sort Key: _hyper_1_21_chunk.bucket DESC, (time_bucket_gapfill('1 day'::interval, _hyper_1_21_chunk."timestamp"))
  • Sort Method: quicksort Memory: 412kB
4. 0.000 15,345.846 ↑ 9.9 4,037 1

Finalize GroupAggregate (cost=731,786.54..753,144.08 rows=40,000 width=0) (actual time=15,341.170..15,345.846 rows=4,037 loops=1)

  • Group Key: (time_bucket_gapfill('1 day'::interval, _hyper_1_21_chunk."timestamp")), _hyper_1_21_chunk.bucket
5. 232.030 15,472.489 ↑ 29.9 5,357 1

Gather Merge (cost=731,786.54..750,944.08 rows=160,000 width=52) (actual time=15,341.152..15,472.489 rows=5,357 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 1.241 15,240.459 ↑ 37.3 1,071 5 / 5

Sort (cost=730,786.48..730,886.48 rows=40,000 width=52) (actual time=15,240.308..15,240.459 rows=1,071 loops=5)

  • Sort Key: (time_bucket_gapfill('1 day'::interval, _hyper_1_21_chunk."timestamp")), _hyper_1_21_chunk.bucket DESC
  • Sort Method: quicksort Memory: 468kB
  • Worker 0: Sort Method: quicksort Memory: 86kB
  • Worker 1: Sort Method: quicksort Memory: 97kB
  • Worker 2: Sort Method: quicksort Memory: 148kB
  • Worker 3: Sort Method: quicksort Memory: 148kB
7. 1,539.822 15,239.218 ↑ 37.3 1,071 5 / 5

Partial HashAggregate (cost=727,228.94..727,728.94 rows=40,000 width=52) (actual time=15,237.051..15,239.218 rows=1,071 loops=5)

  • Group Key: time_bucket_gapfill('1 day'::interval, _hyper_1_21_chunk."timestamp"), _hyper_1_21_chunk.bucket
8. 821.313 13,699.396 ↑ 1.3 4,002,980 5 / 5

Result (cost=0.00..677,189.15 rows=5,003,979 width=20) (actual time=105.527..13,699.396 rows=4,002,980 loops=5)

9. 440.156 12,878.083 ↑ 1.3 4,002,980 5 / 5

Parallel Append (cost=0.00..614,639.41 rows=5,003,979 width=20) (actual time=105.521..12,878.083 rows=4,002,980 loops=5)

10. 47.462 148.149 ↓ 2.4 68,706 1 / 5

Parallel Bitmap Heap Scan on _hyper_1_21_chunk (cost=975.88..7,064.01 rows=28,608 width=20) (actual time=506.023..740.747 rows=68,706 loops=1)

  • Recheck Cond: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
11. 100.688 100.688 ↓ 1.0 68,706 1 / 5

Bitmap Index Scan on _hyper_1_21_chunk_ueba_anomalies_timestamp_idx (cost=0.00..958.72 rows=68,660 width=0) (actual time=503.438..503.438 rows=68,706 loops=1)

  • Index Cond: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
12. 762.135 762.135 ↓ 4.0 3,474,669 1 / 5

Parallel Seq Scan on _hyper_1_25_chunk (cost=0.00..99,839.01 rows=868,667 width=20) (actual time=0.491..3,810.674 rows=3,474,669 loops=1)

  • 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. 1,646.378 1,646.378 ↓ 4.0 3,473,053 1 / 5

Parallel Seq Scan on _hyper_1_11_chunk (cost=0.00..99,796.95 rows=868,263 width=20) (actual time=16.737..8,231.888 rows=3,473,053 loops=1)

  • 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. 1,625.424 1,625.424 ↓ 4.0 3,472,032 1 / 5

Parallel Seq Scan on _hyper_1_20_chunk (cost=0.00..99,767.12 rows=868,008 width=20) (actual time=0.371..8,127.118 rows=3,472,032 loops=1)

  • 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. 2,738.157 2,738.157 ↓ 2.0 1,647,868 2 / 5

Parallel Seq Scan on _hyper_1_26_chunk (cost=0.00..94,702.01 rows=823,934 width=20) (actual time=68.997..6,845.392 rows=1,647,868 loops=2)

  • 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. 2,545.741 2,545.741 ↑ 1.3 637,277 5 / 5

Parallel Seq Scan on _hyper_1_29_chunk (cost=0.00..93,371.44 rows=796,861 width=20) (actual time=8.394..2,545.741 rows=637,277 loops=5)

  • 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: 12,613
17. 81.784 81.784 ↓ 2.4 317,228 1 / 5

Parallel Seq Scan on _hyper_1_22_chunk (cost=0.00..9,908.67 rows=132,178 width=20) (actual time=0.320..408.921 rows=317,228 loops=1)

  • 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. 178.192 178.192 ↓ 2.4 228,042 1 / 5

Parallel Seq Scan on _hyper_1_12_chunk (cost=0.00..7,122.26 rows=95,018 width=20) (actual time=26.395..890.961 rows=228,042 loops=1)

  • 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. 776.587 776.587 ↓ 1.2 113,862 2 / 5

Parallel Seq Scan on _hyper_1_19_chunk (cost=0.00..7,112.28 rows=94,885 width=20) (actual time=10.955..1,941.467 rows=113,862 loops=2)

  • 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. 748.923 748.923 ↓ 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=7.366..3,744.613 rows=227,389 loops=1)

  • 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. 173.341 173.341 ↓ 2.4 227,361 1 / 5

Parallel Seq Scan on _hyper_1_23_chunk (cost=0.00..7,101.01 rows=94,734 width=20) (actual time=6.698..866.707 rows=227,361 loops=1)

  • 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. 221.848 221.848 ↓ 2.4 227,330 1 / 5

Parallel Seq Scan on _hyper_1_13_chunk (cost=0.00..7,099.81 rows=94,721 width=20) (actual time=6.106..1,109.240 rows=227,330 loops=1)

  • 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. 180.700 180.700 ↓ 2.4 227,195 1 / 5

Parallel Seq Scan on _hyper_1_9_chunk (cost=0.00..7,094.97 rows=94,665 width=20) (actual time=4.326..903.500 rows=227,195 loops=1)

  • 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. 52.642 52.642 ↓ 2.4 227,186 1 / 5

Parallel Seq Scan on _hyper_1_24_chunk (cost=0.00..7,094.91 rows=94,661 width=20) (actual time=0.353..263.208 rows=227,186 loops=1)

  • 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. 131.188 131.188 ↓ 2.4 227,161 1 / 5

Parallel Seq Scan on _hyper_1_10_chunk (cost=0.00..7,094.76 rows=94,650 width=20) (actual time=43.038..655.938 rows=227,161 loops=1)

  • 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. 184.121 184.121 ↓ 2.4 226,962 1 / 5

Parallel Seq Scan on _hyper_1_28_chunk (cost=0.00..7,089.51 rows=94,568 width=20) (actual time=8.980..920.604 rows=226,962 loops=1)

  • 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. 84.129 84.129 ↓ 2.4 226,996 1 / 5

Parallel Seq Scan on _hyper_1_17_chunk (cost=0.00..7,088.73 rows=94,582 width=20) (actual time=2.508..420.644 rows=226,996 loops=1)

  • 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. 70.310 70.310 ↓ 2.4 226,968 1 / 5

Parallel Seq Scan on _hyper_1_14_chunk (cost=0.00..7,088.55 rows=94,570 width=20) (actual time=33.812..351.549 rows=226,968 loops=1)

  • Filter: (("timestamp" >= '2018-09-19 22:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-09-20 14:40:00'::timestamp without time zone))
29. 88.180 88.180 ↓ 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=3.970..440.899 rows=226,775 loops=1)

  • 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 : 1,788.886 ms
Execution time : 15,484.632 ms