explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eIS5

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.025 9,228.312 ↑ 47.9 206 1

Limit (cost=1,759,899.20..1,760,146.02 rows=9,873 width=1,668) (actual time=9,228.248..9,228.312 rows=206 loops=1)

2. 0.113 9,228.287 ↑ 47.9 206 1

WindowAgg (cost=1,759,899.20..1,760,146.02 rows=9,873 width=1,668) (actual time=9,228.246..9,228.287 rows=206 loops=1)

3. 0.041 9,228.174 ↑ 47.9 206 1

Subquery Scan on inner (cost=1,759,899.20..1,760,022.61 rows=9,873 width=1,660) (actual time=9,228.118..9,228.174 rows=206 loops=1)

4. 0.104 9,228.133 ↑ 47.9 206 1

Sort (cost=1,759,899.20..1,759,923.88 rows=9,873 width=1,668) (actual time=9,228.117..9,228.133 rows=206 loops=1)

  • Sort Key: (COALESCE(orders_pocet_zakazniku_metric_1.kod_orp)), (COALESCE(orders_pocet_zakazniku_metric_1.x_min)), (COALESCE(orders_pocet_zakazniku_metric_1.x_max)), (COALESCE(orders_pocet_zakazniku_metric_1.y_min)), (COALESCE(orders_pocet_zakazniku_metric_1.y_max)), (COALESCE(orders_pocet_zakazniku_metric_1.nazev)), (COALESCE(orders_pocet_zakazniku_metric_1.upper_name)), orders_pocet_zakazniku_metric_1.orders_pocet_zakazniku_metric_1
  • Sort Method: quicksort Memory: 53kB
5.          

CTE orders_pocet_zakazniku_metric_1

6. 0.242 9,227.877 ↑ 3,882.0 206 1

Sort (cost=1,733,204.90..1,735,204.13 rows=799,689 width=74) (actual time=9,227.862..9,227.877 rows=206 loops=1)

  • Sort Key: spad_orp_dwh.kod_orp, spad_orp_dwh.x_min, spad_orp_dwh.x_max, spad_orp_dwh.y_min, spad_orp_dwh.y_max, spad_orp_dwh.nazev, spad_orp_dwh.upper_name
  • Sort Method: quicksort Memory: 53kB
7. 2,612.791 9,227.635 ↑ 3,882.0 206 1

HashAggregate (cost=1,646,802.19..1,654,799.08 rows=799,689 width=74) (actual time=9,224.524..9,227.635 rows=206 loops=1)

  • Group Key: spad_orp_dwh.kod_orp, spad_orp_dwh.x_min, spad_orp_dwh.x_max, spad_orp_dwh.y_min, spad_orp_dwh.y_max, spad_orp_dwh.nazev, spad_orp_dwh.upper_name
8. 898.768 6,614.844 ↑ 3.0 2,688,752 1

Group (cost=384,998.68..1,406,895.46 rows=7,996,891 width=70) (actual time=3,390.837..6,614.844 rows=2,688,752 loops=1)

  • Group Key: spad_orp_dwh.kod_orp, alza_orders_dwh.company_id
9. 2,087.683 5,716.076 ↑ 1.5 5,176,851 1

Gather Merge (cost=384,998.68..1,366,911.01 rows=7,996,890 width=70) (actual time=3,390.833..5,716.076 rows=5,176,851 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
10. 325.958 3,628.393 ↑ 1.8 739,550 7 / 7

Group (cost=383,998.58..393,994.69 rows=1,332,815 width=70) (actual time=3,123.137..3,628.393 rows=739,550 loops=7)

  • Group Key: spad_orp_dwh.kod_orp, alza_orders_dwh.company_id
11. 2,294.524 3,302.435 ↑ 1.2 1,133,626 7 / 7

Sort (cost=383,998.58..387,330.62 rows=1,332,815 width=70) (actual time=3,123.131..3,302.435 rows=1,133,626 loops=7)

  • Sort Key: spad_orp_dwh.kod_orp, alza_orders_dwh.company_id
  • Sort Method: quicksort Memory: 157792kB
  • Worker 0: Sort Method: quicksort Memory: 207100kB
  • Worker 1: Sort Method: quicksort Memory: 227935kB
  • Worker 2: Sort Method: quicksort Memory: 149561kB
  • Worker 3: Sort Method: quicksort Memory: 228820kB
  • Worker 4: Sort Method: quicksort Memory: 208464kB
  • Worker 5: Sort Method: quicksort Memory: 231158kB
12. 609.948 1,007.911 ↑ 1.2 1,133,626 7 / 7

Parallel Hash Join (cost=1,340.11..248,411.01 rows=1,332,815 width=70) (actual time=11.275..1,007.911 rows=1,133,626 loops=7)

  • Hash Cond: ((alza_orders_dwh.kod_zsj_d)::text = (spad_zsj_d_dwh.kod_zsj_d)::text)
13. 386.820 386.820 ↑ 1.2 1,238,289 7 / 7

Parallel Seq Scan on alza_orders_dwh (cost=0.00..236,017.44 rows=1,466,620 width=12) (actual time=0.011..386.820 rows=1,238,289 loops=7)

  • Filter: ((date_id >= '2018-11-24'::date) AND (date_id <= '2019-11-24'::date))
  • Rows Removed by Filter: 861345
14. 2.029 11.143 ↑ 2.9 3,329 7 / 7

Parallel Hash (cost=1,218.73..1,218.73 rows=9,710 width=74) (actual time=11.143..11.143 rows=3,329 loops=7)

  • Buckets: 32768 Batches: 1 Memory Usage: 2944kB
15. 1.253 9.114 ↑ 2.9 3,329 7 / 7

Hash Join (cost=648.61..1,218.73 rows=9,710 width=74) (actual time=4.344..9.114 rows=3,329 loops=7)

  • Hash Cond: ((spad_obec_dwh.kod_orp)::text = (spad_orp_dwh.kod_orp)::text)
16. 1.226 7.701 ↑ 2.9 3,329 7 / 7

Hash Join (cost=640.98..1,185.09 rows=9,710 width=13) (actual time=4.171..7.701 rows=3,329 loops=7)

  • Hash Cond: ((spad_cast_obec_dwh.kod_obec)::text = (spad_obec_dwh.kod_obec)::text)
17. 1.658 3.396 ↑ 2.9 3,329 7 / 7

Parallel Hash Join (cost=413.33..931.93 rows=9,710 width=15) (actual time=1.054..3.396 rows=3,329 loops=7)

  • Hash Cond: ((spad_zsj_d_dwh.kod_cast_obec)::text = (spad_cast_obec_dwh.kod_cast_obec)::text)
18. 0.725 0.725 ↑ 2.9 3,329 7 / 7

Parallel Seq Scan on spad_zsj_d_dwh (cost=0.00..493.10 rows=9,710 width=15) (actual time=0.010..0.725 rows=3,329 loops=7)

19. 0.541 1.013 ↑ 4.1 2,152 7 / 7

Parallel Hash (cost=302.59..302.59 rows=8,859 width=14) (actual time=1.013..1.013 rows=2,152 loops=7)

  • Buckets: 16384 Batches: 1 Memory Usage: 864kB
20. 0.472 0.472 ↑ 1.8 5,020 3 / 7

Parallel Seq Scan on spad_cast_obec_dwh (cost=0.00..302.59 rows=8,859 width=14) (actual time=0.008..1.102 rows=5,020 loops=3)

21. 1.377 3.079 ↑ 1.0 6,251 7 / 7

Hash (cost=149.51..149.51 rows=6,251 width=12) (actual time=3.079..3.079 rows=6,251 loops=7)

  • Buckets: 8192 Batches: 1 Memory Usage: 333kB
22. 1.702 1.702 ↑ 1.0 6,251 7 / 7

Seq Scan on spad_obec_dwh (cost=0.00..149.51 rows=6,251 width=12) (actual time=0.012..1.702 rows=6,251 loops=7)

23. 0.087 0.160 ↑ 1.0 206 7 / 7

Hash (cost=5.06..5.06 rows=206 width=66) (actual time=0.160..0.160 rows=206 loops=7)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
24. 0.073 0.073 ↑ 1.0 206 7 / 7

Seq Scan on spad_orp_dwh (cost=0.00..5.06 rows=206 width=66) (actual time=0.016..0.073 rows=206 loops=7)

25. 9,228.029 9,228.029 ↑ 47.9 206 1

CTE Scan on orders_pocet_zakazniku_metric_1 (cost=0.00..24,040.03 rows=9,873 width=1,668) (actual time=9,227.870..9,228.029 rows=206 loops=1)

  • Filter: ((COALESCE(y_max) > 46.023838125891075) AND (COALESCE(y_min) < 53.50547650316675) AND (COALESCE(x_max) > 7.229003906250001) AND (COALESCE(x_min) < 23.774414062500004))
Planning time : 1.252 ms
Execution time : 9,270.201 ms