explain.depesz.com

PostgreSQL's explain analyze made readable

Result: orCF : Optimization for: plan #eIS5

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.024 13,571.433 ↓ 1.0 206 1

Limit (cost=748,123.22..748,128.22 rows=200 width=556) (actual time=13,571.377..13,571.433 rows=206 loops=1)

2. 0.075 13,571.409 ↓ 1.0 206 1

WindowAgg (cost=748,123.22..748,128.22 rows=200 width=556) (actual time=13,571.375..13,571.409 rows=206 loops=1)

3. 0.030 13,571.334 ↓ 1.0 206 1

Subquery Scan on inner (cost=748,123.22..748,125.72 rows=200 width=548) (actual time=13,571.291..13,571.334 rows=206 loops=1)

4. 0.068 13,571.304 ↓ 1.0 206 1

Sort (cost=748,123.22..748,123.72 rows=200 width=556) (actual time=13,571.290..13,571.304 rows=206 loops=1)

  • Sort Key: (COALESCE(orders_pocet_zakazniku_metric_1.kod_orp)), orders_pocet_zakazniku_metric_1.orders_pocet_zakazniku_metric_1
  • Sort Method: quicksort Memory: 41kB
5.          

CTE orders_pocet_zakazniku_metric_1

6. 0.192 13,571.165 ↓ 1.0 206 1

Sort (cost=748,110.07..748,110.57 rows=200 width=13) (actual time=13,571.151..13,571.165 rows=206 loops=1)

  • Sort Key: spad_orp_dwh.kod_orp
  • Sort Method: quicksort Memory: 34kB
7. 499.327 13,570.973 ↓ 1.0 206 1

HashAggregate (cost=748,100.43..748,102.43 rows=200 width=13) (actual time=13,570.944..13,570.973 rows=206 loops=1)

  • Group Key: spad_orp_dwh.kod_orp
8. 3,122.750 13,071.646 ↑ 3.0 2,688,752 1

HashAggregate (cost=548,178.16..628,147.07 rows=7,996,891 width=9) (actual time=12,423.648..13,071.646 rows=2,688,752 loops=1)

  • Group Key: spad_orp_dwh.kod_orp, alza_orders_dwh.company_id
9. 1,868.588 9,948.896 ↑ 1.0 7,935,382 1

Hash Join (cost=1,708.47..508,193.70 rows=7,996,891 width=9) (actual time=15.509..9,948.896 rows=7,935,382 loops=1)

  • Hash Cond: ((spad_obec_dwh.kod_orp)::text = (spad_orp_dwh.kod_orp)::text)
10. 1,884.261 8,080.228 ↑ 1.0 7,935,382 1

Hash Join (cost=1,700.84..486,762.35 rows=7,996,891 width=9) (actual time=15.424..8,080.228 rows=7,935,382 loops=1)

  • Hash Cond: ((spad_cast_obec_dwh.kod_obec)::text = (spad_obec_dwh.kod_obec)::text)
11. 1,979.214 6,193.950 ↑ 1.0 7,935,382 1

Hash Join (cost=1,473.19..465,528.64 rows=7,996,891 width=11) (actual time=13.394..6,193.950 rows=7,935,382 loops=1)

  • Hash Cond: ((spad_zsj_d_dwh.kod_cast_obec)::text = (spad_cast_obec_dwh.kod_cast_obec)::text)
12. 2,172.688 4,209.844 ↑ 1.0 7,935,382 1

Hash Join (cost=920.32..443,978.02 rows=7,996,891 width=11) (actual time=8.479..4,209.844 rows=7,935,382 loops=1)

  • Hash Cond: ((alza_orders_dwh.kod_zsj_d)::text = (spad_zsj_d_dwh.kod_zsj_d)::text)
13. 2,028.728 2,028.728 ↑ 1.0 8,668,023 1

Seq Scan on alza_orders_dwh (cost=0.00..419,954.62 rows=8,799,719 width=12) (actual time=0.008..2,028.728 rows=8,668,023 loops=1)

  • Filter: ((date_id >= '2018-11-24'::date) AND (date_id <= '2019-11-24'::date))
  • Rows Removed by Filter: 6029414
14. 3.976 8.428 ↑ 1.0 23,303 1

Hash (cost=629.03..629.03 rows=23,303 width=15) (actual time=8.427..8.428 rows=23,303 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1326kB
15. 4.452 4.452 ↑ 1.0 23,303 1

Seq Scan on spad_zsj_d_dwh (cost=0.00..629.03 rows=23,303 width=15) (actual time=0.004..4.452 rows=23,303 loops=1)

16. 2.226 4.892 ↑ 1.0 15,061 1

Hash (cost=364.61..364.61 rows=15,061 width=14) (actual time=4.892..4.892 rows=15,061 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 805kB
17. 2.666 2.666 ↑ 1.0 15,061 1

Seq Scan on spad_cast_obec_dwh (cost=0.00..364.61 rows=15,061 width=14) (actual time=0.006..2.666 rows=15,061 loops=1)

18. 0.923 2.017 ↑ 1.0 6,251 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 333kB
19. 1.094 1.094 ↑ 1.0 6,251 1

Seq Scan on spad_obec_dwh (cost=0.00..149.51 rows=6,251 width=12) (actual time=0.006..1.094 rows=6,251 loops=1)

20. 0.032 0.080 ↑ 1.0 206 1

Hash (cost=5.06..5.06 rows=206 width=5) (actual time=0.080..0.080 rows=206 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
21. 0.048 0.048 ↑ 1.0 206 1

Seq Scan on spad_orp_dwh (cost=0.00..5.06 rows=206 width=5) (actual time=0.013..0.048 rows=206 loops=1)

22. 13,571.236 13,571.236 ↓ 1.0 206 1

CTE Scan on orders_pocet_zakazniku_metric_1 (cost=0.00..5.00 rows=200 width=556) (actual time=13,571.156..13,571.236 rows=206 loops=1)

Planning time : 1.166 ms
Execution time : 13,666.408 ms