explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0qh

Settings
# exclusive inclusive rows x rows loops node
1. 218.931 16,756.786 ↑ 4.3 16,081 1

Sort (cost=663,738.50..663,910.19 rows=68,675 width=250) (actual time=16,673.906..16,756.786 rows=16,081 loops=1)

  • Sort Key: so.store_key, aa.color_key, aa.size_key, so.date_key
  • Sort Method: external merge Disk: 2136kB
2. 30.320 16,537.855 ↑ 4.3 16,081 1

Hash Left Join (cost=70,425.01..645,307.33 rows=68,675 width=250) (actual time=7,110.142..16,537.855 rows=16,081 loops=1)

  • Hash Cond: ((aa.article_key = pc2.article_key) AND (s.country = pc2.country))
  • Join Filter: ((c.date_key >= pc2.date_min) AND (c.date_key <= pc2.date_max))
  • Rows Removed by Join Filter: 27323
3. 43.298 16,505.705 ↑ 4.3 16,081 1

Hash Left Join (cost=70,402.41..641,141.83 rows=68,675 width=104) (actual time=7,105.815..16,505.705 rows=16,081 loops=1)

  • Hash Cond: ((aa.article_key = pc1.article_key) AND (aa.color_key = pc1.color_key) AND (s.country = pc1.country))
  • Join Filter: ((c.date_key >= pc1.date_min) AND (c.date_key <= pc1.date_max))
  • Rows Removed by Join Filter: 83653
4. 11.730 16,420.586 ↑ 4.3 16,081 1

Hash Left Join (cost=70,280.37..629,264.10 rows=68,675 width=100) (actual time=7,057.440..16,420.586 rows=16,081 loops=1)

  • Hash Cond: ((aa.article_key = apr.article_key) AND (aa.color_key = apr.color_key))
5. 9.520 16,402.389 ↑ 4.3 16,081 1

Hash Left Join (cost=70,273.93..628,542.48 rows=68,675 width=92) (actual time=7,048.144..16,402.389 rows=16,081 loops=1)

  • Hash Cond: (so.store_key = s.store_key)
6. 14.012 16,381.570 ↑ 4.3 16,081 1

Hash Left Join (cost=70,266.38..627,590.66 rows=68,675 width=76) (actual time=7,035.217..16,381.570 rows=16,081 loops=1)

  • Hash Cond: ((aa.article_key = a.article_key) AND (aa.color_key = a.color_key))
7. 67.899 16,354.633 ↑ 4.3 16,081 1

Hash Right Join (cost=70,259.94..626,869.03 rows=68,675 width=47) (actual time=7,017.556..16,354.633 rows=16,081 loops=1)

  • Hash Cond: ((ss.article_key = aa.article_key) AND (ss.store_key = aa.store_key) AND (ss.color_key = aa.color_key) AND (ss.size_key = aa.size_key) AND (ss.date_key = so.date_key))
8. 9,619.593 9,619.593 ↑ 2.5 1,346 1

Seq Scan on stock_sales ss (cost=0.00..554,254.95 rows=3,378 width=20) (actual time=205.948..9,619.593 rows=1,346 loops=1)

  • Filter: (article_key = 26761)
  • Rows Removed by Filter: 22275192
9. 27.163 6,667.141 ↑ 4.3 16,081 1

Hash (cost=68,110.75..68,110.75 rows=68,675 width=43) (actual time=6,667.141..6,667.141 rows=16,081 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 212kB
10. 51.081 6,639.978 ↑ 4.3 16,081 1

Hash Right Join (cost=52,249.39..68,110.75 rows=68,675 width=43) (actual time=6,591.589..6,639.978 rows=16,081 loops=1)

  • Hash Cond: ((ap.article_key = aa.article_key) AND (ap.year = c.year) AND (ap.week = c.week) AND (ap.color_key = aa.color_key))
11. 228.197 228.197 ↑ 2.7 38 1

Seq Scan on article_popularity ap (cost=0.00..14,638.73 rows=103 width=16) (actual time=220.210..228.197 rows=38 loops=1)

  • Filter: (article_key = 26761)
  • Rows Removed by Filter: 779860
12. 38.497 6,360.700 ↑ 4.3 16,081 1

Hash (cost=50,338.89..50,338.89 rows=68,675 width=35) (actual time=6,360.700..6,360.700 rows=16,081 loops=1)

  • Buckets: 16384 Batches: 8 Memory Usage: 277kB
13. 9.723 6,322.203 ↑ 4.3 16,081 1

Hash Left Join (cost=62.44..50,338.89 rows=68,675 width=35) (actual time=495.516..6,322.203 rows=16,081 loops=1)

  • Hash Cond: (so.date_key = c.date_key)
14. 4.072 6,311.599 ↑ 4.3 16,081 1

Nested Loop (cost=1.12..49,333.29 rows=68,675 width=23) (actual time=494.584..6,311.599 rows=16,081 loops=1)

15. 1.483 6,160.998 ↑ 1.8 243 1

Nested Loop Left Join (cost=0.70..42,771.32 rows=436 width=25) (actual time=142.221..6,160.998 rows=243 loops=1)

16. 6,157.085 6,157.085 ↑ 1.8 243 1

Index Scan using article_active_pkey on article_active aa (cost=0.43..42,623.02 rows=436 width=16) (actual time=141.533..6,157.085 rows=243 loops=1)

  • Index Cond: (article_key = 26761)
17. 2.430 2.430 ↑ 1.0 1 243

Index Scan using sizes_pkey on sizes si (cost=0.27..0.33 rows=1 width=11) (actual time=0.009..0.010 rows=1 loops=243)

  • Index Cond: (aa.size_key = size_key)
18. 146.529 146.529 ↑ 2.8 66 243

Index Scan using stores_opening_pkey on stores_opening so (cost=0.42..13.21 rows=184 width=6) (actual time=0.409..0.603 rows=66 loops=243)

  • Index Cond: ((store_key = aa.store_key) AND (date_key <= aa.date_max) AND (date_key >= aa.date_min))
  • Filter: store_open
  • Rows Removed by Filter: 5
19. 0.488 0.881 ↑ 1.0 2,192 1

Hash (cost=33.92..33.92 rows=2,192 width=12) (actual time=0.881..0.881 rows=2,192 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 127kB
20. 0.393 0.393 ↑ 1.0 2,192 1

Seq Scan on calendar c (cost=0.00..33.92 rows=2,192 width=12) (actual time=0.018..0.393 rows=2,192 loops=1)

21. 8.841 12.925 ↑ 2.0 1 1

Hash (cost=6.41..6.41 rows=2 width=35) (actual time=12.925..12.925 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 4.084 4.084 ↑ 2.0 1 1

Index Scan using articles_pkey on articles a (cost=0.29..6.41 rows=2 width=35) (actual time=4.079..4.084 rows=1 loops=1)

  • Index Cond: (article_key = 26761)
23. 0.834 11.299 ↑ 1.0 113 1

Hash (cost=6.13..6.13 rows=113 width=18) (actual time=11.299..11.299 rows=113 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
24. 10.465 10.465 ↑ 1.0 113 1

Seq Scan on stores s (cost=0.00..6.13 rows=113 width=18) (actual time=10.407..10.465 rows=113 loops=1)

25. 1.320 6.467 ↑ 2.0 1 1

Hash (cost=6.41..6.41 rows=2 width=16) (actual time=6.467..6.467 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 5.147 5.147 ↑ 2.0 1 1

Index Scan using article_price_rank_pkey on article_price_rank apr (cost=0.29..6.41 rows=2 width=16) (actual time=5.145..5.147 rows=1 loops=1)

  • Index Cond: (article_key = 26761)
27. 2.059 41.821 ↑ 1.5 28 1

Hash (cost=121.28..121.28 rows=43 width=21) (actual time=41.820..41.821 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
28. 35.314 39.762 ↑ 1.5 28 1

Bitmap Heap Scan on price_changes pc1 (cost=3.32..121.28 rows=43 width=21) (actual time=23.098..39.762 rows=28 loops=1)

  • Recheck Cond: (article_key = 26761)
  • Filter: (color_key <> '-1'::integer)
  • Rows Removed by Filter: 12
  • Heap Blocks: exact=40
29. 4.448 4.448 ↑ 1.3 40 1

Bitmap Index Scan on price_changes_pkey (cost=0.00..3.30 rows=51 width=0) (actual time=4.448..4.448 rows=40 loops=1)

  • Index Cond: (article_key = 26761)
30. 0.677 1.830 ↓ 1.5 12 1

Hash (cost=22.48..22.48 rows=8 width=19) (actual time=1.830..1.830 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.357 1.153 ↓ 1.5 12 1

Bitmap Heap Scan on price_changes pc2 (cost=3.00..22.48 rows=8 width=19) (actual time=1.133..1.153 rows=12 loops=1)

  • Recheck Cond: ((article_key = 26761) AND (color_key = '-1'::integer))
  • Heap Blocks: exact=12
32. 0.796 0.796 ↓ 1.5 12 1

Bitmap Index Scan on price_changes_pkey (cost=0.00..3.00 rows=8 width=0) (actual time=0.796..0.796 rows=12 loops=1)

  • Index Cond: ((article_key = 26761) AND (color_key = '-1'::integer))