explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DBSE

Settings
# exclusive inclusive rows x rows loops node
1. 71.844 4,608.599 ↓ 457.4 15,550 1

Nested Loop Left Join (cost=19,504.45..298,013.11 rows=34 width=452) (actual time=791.297..4,608.599 rows=15,550 loops=1)

  • Join Filter: (hw.week = c.week)
  • Rows Removed by Join Filter: 183394
2. 9.103 4,474.555 ↓ 457.4 15,550 1

Nested Loop Left Join (cost=19,504.30..297,993.70 rows=34 width=411) (actual time=791.267..4,474.555 rows=15,550 loops=1)

3. 17.801 4,418.802 ↓ 457.4 15,550 1

Nested Loop Left Join (cost=19,503.88..297,706.40 rows=34 width=385) (actual time=791.255..4,418.802 rows=15,550 loops=1)

4. 16.533 4,385.451 ↓ 457.4 15,550 1

Nested Loop Left Join (cost=19,503.60..297,638.09 rows=34 width=305) (actual time=791.245..4,385.451 rows=15,550 loops=1)

5. 5.089 4,353.368 ↓ 457.4 15,550 1

Nested Loop Left Join (cost=19,503.31..297,355.55 rows=34 width=303) (actual time=791.235..4,353.368 rows=15,550 loops=1)

6. 12.740 4,301.629 ↓ 457.4 15,550 1

Nested Loop Left Join (cost=19,502.89..297,068.25 rows=34 width=299) (actual time=791.217..4,301.629 rows=15,550 loops=1)

7. 1,275.809 4,055.639 ↓ 457.4 15,550 1

Nested Loop (cost=19,489.98..296,628.53 rows=34 width=295) (actual time=791.168..4,055.639 rows=15,550 loops=1)

  • Join Filter: (so.date_key = c.date_key)
  • Rows Removed by Join Filter: 13755932
8. 0.426 0.426 ↓ 45.8 366 1

Index Scan using calendar_year_week on calendar c (cost=0.28..32.72 rows=8 width=12) (actual time=0.014..0.426 rows=366 loops=1)

  • Index Cond: ((year = ANY ('{2018,2019}'::integer[])) AND (week <= 26))
9. 2,022.512 2,779.404 ↓ 3.7 37,627 366

Materialize (cost=19,489.70..295,388.85 rows=10,272 width=283) (actual time=0.340..7.594 rows=37,627 loops=366)

10. 17.573 756.892 ↓ 3.7 37,627 1

Hash Left Join (cost=19,489.70..295,337.49 rows=10,272 width=283) (actual time=123.825..756.892 rows=37,627 loops=1)

  • Hash Cond: (so.store_key = s.store_key)
11. 9.941 739.257 ↓ 3.7 37,627 1

Nested Loop Left Join (cost=19,474.75..295,295.05 rows=10,272 width=212) (actual time=123.756..739.257 rows=37,627 loops=1)

12. 20.634 616.435 ↓ 3.7 37,627 1

Nested Loop Left Join (cost=19,466.15..206,596.33 rows=10,272 width=206) (actual time=123.738..616.435 rows=37,627 loops=1)

13. 29.208 370.039 ↓ 3.7 37,627 1

Nested Loop Left Join (cost=19,465.58..118,154.41 rows=10,272 width=198) (actual time=123.718..370.039 rows=37,627 loops=1)

14. 9.916 152.696 ↓ 3.7 37,627 1

Nested Loop (cost=19,465.02..29,712.49 rows=10,272 width=182) (actual time=123.699..152.696 rows=37,627 loops=1)

15. 0.083 131.180 ↑ 1.2 100 1

Hash Left Join (cost=19,464.60..23,281.71 rows=118 width=184) (actual time=123.664..131.180 rows=100 loops=1)

  • Hash Cond: (skus.size_key = si.size_key)
16. 7.501 130.945 ↑ 1.2 100 1

Hash Left Join (cost=19,423.15..23,239.96 rows=118 width=148) (actual time=123.501..130.945 rows=100 loops=1)

  • Hash Cond: ((skus.article_key = a.article_key) AND (skus.color_key = a.color_key))
17. 0.060 0.060 ↑ 1.2 100 1

Index Scan using skus_sku_key_key on skus (cost=0.43..195.52 rows=118 width=22) (actual time=0.012..0.060 rows=100 loops=1)

  • Index Cond: ((sku_key > 0) AND (sku_key <= 100))
18. 25.188 123.384 ↑ 1.2 63,159 1

Hash (cost=16,873.72..16,873.72 rows=73,800 width=132) (actual time=123.383..123.384 rows=63,159 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 1691kB
19. 10.301 98.196 ↑ 1.2 63,159 1

Subquery Scan on a (cost=14,844.22..16,873.72 rows=73,800 width=132) (actual time=39.341..98.196 rows=63,159 loops=1)

20. 39.158 87.895 ↑ 1.2 63,159 1

WindowAgg (cost=14,844.22..16,135.72 rows=73,800 width=436) (actual time=39.339..87.895 rows=63,159 loops=1)

21. 33.702 48.737 ↑ 1.2 63,159 1

Sort (cost=14,844.22..15,028.72 rows=73,800 width=100) (actual time=38.921..48.737 rows=63,159 loops=1)

  • Sort Key: articles.subgroep
  • Sort Method: external merge Disk: 3616kB
22. 15.035 15.035 ↑ 1.2 63,159 1

Seq Scan on articles (cost=0.00..4,838.00 rows=73,800 width=100) (actual time=0.013..15.035 rows=63,159 loops=1)

23. 0.070 0.152 ↑ 3.2 418 1

Hash (cost=24.53..24.53 rows=1,353 width=38) (actual time=0.152..0.152 rows=418 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 35kB
24. 0.082 0.082 ↑ 3.2 418 1

Seq Scan on sizes si (cost=0.00..24.53 rows=1,353 width=38) (actual time=0.009..0.082 rows=418 loops=1)

25. 11.600 11.600 ↓ 4.3 376 100

Index Scan using stores_opening_pkey on stores_opening so (cost=0.42..53.63 rows=87 width=6) (actual time=0.013..0.116 rows=376 loops=100)

  • Index Cond: ((store_key = skus.store_key) AND (date_key <= (skus.date_max + '100 days'::interval)) AND (date_key >= skus.date_min))
  • Filter: store_open
  • Rows Removed by Filter: 6
26. 0.000 188.135 ↑ 1.0 1 37,627

Limit (cost=0.56..8.59 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=37,627)

27. 188.135 188.135 ↑ 1.0 1 37,627

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=16) (actual time=0.005..0.005 rows=1 loops=37,627)

  • Index Cond: ((store_key = skus.store_key) AND (article_key = skus.article_key) AND (color_key = skus.color_key) AND (size_key = skus.size_key) AND (date_key <= so.date_key))
28. 0.000 225.762 ↑ 1.0 1 37,627

Limit (cost=0.56..8.59 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=37,627)

29. 225.762 225.762 ↑ 1.0 1 37,627

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 time=0.006..0.006 rows=1 loops=37,627)

  • Index Cond: ((store_key = 1) AND (article_key = skus.article_key) AND (color_key = skus.color_key) AND (size_key = skus.size_key) AND (date_key <= so.date_key))
30. 37.627 112.881 ↑ 1.0 1 37,627

Aggregate (cost=8.60..8.61 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=37,627)

31. 75.254 75.254 ↓ 0.0 0 37,627

Index Scan using mutations_pkey on mutations (cost=0.56..8.60 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=37,627)

  • Index Cond: ((store_key = 91) AND (to_store_key = skus.store_key) AND (article_key = skus.article_key) AND (color_key = skus.color_key) AND (size_key = skus.size_key) AND (date_key = so.date_key) AND (mutation_key = 60))
  • Filter: (to_store_key <> 91)
32. 0.023 0.062 ↑ 1.9 114 1

Hash (cost=12.20..12.20 rows=220 width=73) (actual time=0.062..0.062 rows=114 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
33. 0.039 0.039 ↑ 1.9 114 1

Seq Scan on stores s (cost=0.00..12.20 rows=220 width=73) (actual time=0.005..0.039 rows=114 loops=1)

34. 15.550 233.250 ↑ 1.0 1 15,550

Limit (cost=12.91..12.91 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=15,550)

35. 46.650 217.700 ↑ 1.0 1 15,550

Sort (cost=12.91..12.91 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=15,550)

  • Sort Key: price_changes.date_min DESC
  • Sort Method: top-N heapsort Memory: 25kB
36. 46.650 171.050 ↓ 7.0 7 15,550

Bitmap Heap Scan on price_changes (cost=8.88..12.90 rows=1 width=8) (actual time=0.010..0.011 rows=7 loops=15,550)

  • Recheck Cond: (((article_key = skus.article_key) AND (color_key = skus.color_key) AND (country = s.country) AND (date_min <= c.date_key)) OR ((article_key = skus.article_key) AND (color_key = '-1'::integer) AND (country = s.country) AND (date_min <= c.date_key)))
  • Heap Blocks: exact=24282
37. 15.550 124.400 ↓ 0.0 0 15,550

BitmapOr (cost=8.88..8.88 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=15,550)

38. 62.200 62.200 ↓ 4.0 4 15,550

Bitmap Index Scan on price_changes_pkey (cost=0.00..4.44 rows=1 width=0) (actual time=0.004..0.004 rows=4 loops=15,550)

  • Index Cond: ((article_key = skus.article_key) AND (color_key = skus.color_key) AND (country = s.country) AND (date_min <= c.date_key))
39. 46.650 46.650 ↓ 3.0 3 15,550

Bitmap Index Scan on price_changes_pkey (cost=0.00..4.44 rows=1 width=0) (actual time=0.003..0.003 rows=3 loops=15,550)

  • Index Cond: ((article_key = skus.article_key) AND (color_key = '-1'::integer) AND (country = s.country) AND (date_min <= c.date_key))
40. 46.650 46.650 ↑ 1.0 1 15,550

Index Scan using stock_per_group_key on stock_per_group spg (cost=0.42..8.45 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=15,550)

  • Index Cond: ((store_key = skus.store_key) AND (subgroep = a.subgroep) AND (year = c.year) AND (week = c.week))
41. 15.550 15.550 ↑ 1.0 1 15,550

Index Scan using stores_budget_pkey on stores_budget bg (cost=0.29..8.31 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=15,550)

  • Index Cond: ((store_key = skus.store_key) AND (year = c.year) AND (week = c.week))
42. 15.550 15.550 ↑ 1.0 1 15,550

Index Scan using weather_pkey on weather w (cost=0.28..2.01 rows=1 width=86) (actual time=0.001..0.001 rows=1 loops=15,550)

  • Index Cond: ((weather_station_key = s.weather_station_key) AND (date_key = so.date_key))
43. 46.650 46.650 ↑ 1.0 1 15,550

Index Scan using articles_popularity_article_key_color_key_year_week_idx on articles_popularity ap (cost=0.42..8.45 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=15,550)

  • Index Cond: ((article_key = skus.article_key) AND (color_key = skus.color_key) AND (year = c.year) AND (week = c.week))
44. 62.200 62.200 ↓ 2.6 13 15,550

Index Scan using weather_historic_weather_station_key_week_idx on weather_historic hw (cost=0.15..0.46 rows=5 width=52) (actual time=0.002..0.004 rows=13 loops=15,550)

  • Index Cond: (weather_station_key = s.weather_station_key)
Planning time : 6.406 ms