explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uTlp

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=72,384,949.03..72,384,950.07 rows=418 width=19) (actual rows= loops=)

  • Sort Key: si.size_key, si.bkmaat
2. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=72,364,542.43..72,384,930.83 rows=418 width=19) (actual rows= loops=)

  • Group Key: si.size_key
3. 0.000 0.000 ↓ 0.0

Sort (cost=72,364,542.43..72,367,939.45 rows=1,358,809 width=21) (actual rows= loops=)

  • Sort Key: si.size_key
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,598,513.52..72,170,387.16 rows=1,358,809 width=21) (actual rows= loops=)

  • Hash Cond: (s.size_key = si.size_key)
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,598,493.12..72,166,763.71 rows=1,358,809 width=16) (actual rows= loops=)

  • Filter: ((CASE WHEN stores.one_stock THEN stock_sales_1.end_stock ELSE ss.end_stock END > 0) OR ((CASE WHEN stores.one_stock THEN stock_sales_1.end_stock ELSE ss.end_stock END = 0) AND (CASE WHEN (p.date_key = ss.date_key) THEN (ss.sales)::integer ELSE 0 END > 0)))
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,598,492.55..37,134,555.04 rows=4,062,883 width=31) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Gather (cost=1,598,491.99..2,122,660.79 rows=4,062,883 width=19) (actual rows= loops=)

  • Workers Planned: 2
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,597,491.99..1,715,372.49 rows=1,692,868 width=19) (actual rows= loops=)

  • Hash Cond: ((s.store_key = so.store_key) AND (p.date_key = so.date_key))
9. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,590,246.96..1,660,286.06 rows=2,777,579 width=21) (actual rows= loops=)

  • Merge Cond: (p.sku_key = s.sku_key)
10. 0.000 0.000 ↓ 0.0

Sort (cost=1,033,225.30..1,042,809.52 rows=3,833,690 width=12) (actual rows= loops=)

  • Sort Key: p.sku_key
11. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on predictions p (cost=0.00..482,965.50 rows=3,833,690 width=12) (actual rows= loops=)

  • Filter: ((date_key >= '2019-01-01'::date) AND (date_key <= '2019-03-04'::date))
12. 0.000 0.000 ↓ 0.0

Materialize (cost=556,947.70..572,519.06 rows=3,114,272 width=17) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=556,947.70..564,733.38 rows=3,114,272 width=17) (actual rows= loops=)

  • Sort Key: s.sku_key
14. 0.000 0.000 ↓ 0.0

Hash Join (cost=9.44..93,330.25 rows=3,114,272 width=17) (actual rows= loops=)

  • Hash Cond: (s.store_key = stores.store_key)
15. 0.000 0.000 ↓ 0.0

Seq Scan on skus s (cost=0.00..79,369.06 rows=5,152,006 width=14) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash (cost=8.43..8.43 rows=81 width=3) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on stores (cost=0.00..8.43 rows=81 width=3) (actual rows= loops=)

  • Filter: (store_status = 'open_store'::text)
18. 0.000 0.000 ↓ 0.0

Hash (cost=3,815.96..3,815.96 rows=181,338 width=6) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on stores_opening so (cost=0.00..3,815.96 rows=181,338 width=6) (actual rows= loops=)

  • Filter: store_open
20. 0.000 0.000 ↓ 0.0

Subquery Scan on ss (cost=0.56..8.61 rows=1 width=14) (actual rows= loops=)

  • Filter: (ss.size_key = ANY ('{219,220,221,222,223,224}'::integer[]))
21. 0.000 0.000 ↓ 0.0

Limit (cost=0.56..8.59 rows=1 width=24) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Scan Backward using stock_sales_store_key_article_key_color_key_size_key_date_k_idx on stock_sales (cost=0.56..8.59 rows=1 width=24) (actual rows= loops=)

  • Index Cond: ((store_key = s.store_key) AND (article_key = s.article_key) AND (color_key = s.color_key) AND (size_key = s.size_key) AND (date_key <= p.date_key))
23. 0.000 0.000 ↓ 0.0

Limit (cost=0.56..8.59 rows=1 width=12) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Index Scan Backward using stock_sales_store_key_article_key_color_key_size_key_date_k_idx on stock_sales stock_sales_1 (cost=0.56..8.59 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((store_key = 1) AND (article_key = s.article_key) AND (color_key = s.color_key) AND (size_key = s.size_key) AND (date_key <= p.date_key))
25. 0.000 0.000 ↓ 0.0

Hash (cost=15.18..15.18 rows=418 width=7) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on sizes si (cost=0.00..15.18 rows=418 width=7) (actual rows= loops=)