explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A4W7l

Settings
# exclusive inclusive rows x rows loops node
1. 8,417.336 203,963.858 ↑ 1.0 2,199,216 1

GroupAggregate (cost=4,077,317.53..4,237,484.97 rows=2,227,654 width=52) (actual time=188,086.322..203,963.858 rows=2,199,216 loops=1)

  • Group Key: s.sku_key
2. 30,432.900 195,546.522 ↓ 1.8 19,008,936 1

Sort (cost=4,077,317.53..4,103,781.89 rows=10,585,741 width=13) (actual time=188,086.283..195,546.522 rows=19,008,936 loops=1)

  • Sort Key: s.sku_key
  • Sort Method: external merge Disk: 382744kB
3. 22,276.115 165,113.622 ↓ 1.8 19,008,936 1

Hash Right Join (cost=969,644.27..2,480,370.44 rows=10,585,741 width=13) (actual time=19,056.698..165,113.622 rows=19,008,936 loops=1)

  • Hash Cond: (stock_predictions.sku_key = s.sku_key)
4. 123,793.624 131,703.907 ↓ 1.0 25,354,247 1

Bitmap Heap Scan on stock_predictions (cost=532,258.40..1,558,021.93 rows=25,085,446 width=9) (actual time=7,921.167..131,703.907 rows=25,354,247 loops=1)

  • Recheck Cond: ((date_key >= now()) AND (date_key < (now() + '28 days'::interval)))
  • Rows Removed by Index Recheck: 24955088
  • Heap Blocks: exact=42605 lossy=264449
5. 7,910.283 7,910.283 ↓ 1.0 25,354,247 1

Bitmap Index Scan on stock_predictions_date_key_idx (cost=0.00..525,987.04 rows=25,085,446 width=0) (actual time=7,910.283..7,910.283 rows=25,354,247 loops=1)

  • Index Cond: ((date_key >= now()) AND (date_key < (now() + '28 days'::interval)))
6. 904.536 11,133.600 ↓ 1.1 2,425,557 1

Hash (cost=400,838.20..400,838.20 rows=2,227,654 width=8) (actual time=11,133.600..11,133.600 rows=2,425,557 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 3734kB
7. 1,530.351 10,229.064 ↓ 1.1 2,425,557 1

Hash Right Join (cost=196,318.17..400,838.20 rows=2,227,654 width=8) (actual time=4,826.226..10,229.064 rows=2,425,557 loops=1)

  • Hash Cond: ((stock_sales.store_key = s.store_key) AND (stock_sales.article_key = s.article_key) AND (stock_sales.color_key = s.color_key) AND (stock_sales.size_key = s.size_key))
8. 3,878.914 4,040.285 ↓ 2.0 431,719 1

Bitmap Heap Scan on stock_sales (cost=4,472.30..193,843.80 rows=210,718 width=14) (actual time=163.125..4,040.285 rows=431,719 loops=1)

  • Recheck Cond: ((date_key < now()) AND (date_key >= (now() - '28 days'::interval)))
  • Heap Blocks: exact=7820
9. 161.371 161.371 ↓ 2.0 431,719 1

Bitmap Index Scan on stock_sales_date_key_idx (cost=0.00..4,419.62 rows=210,718 width=0) (actual time=161.370..161.371 rows=431,719 loops=1)

  • Index Cond: ((date_key < now()) AND (date_key >= (now() - '28 days'::interval)))
10. 949.402 4,658.428 ↑ 1.0 2,199,216 1

Hash (cost=136,414.78..136,414.78 rows=2,227,654 width=14) (actual time=4,658.427..4,658.428 rows=2,199,216 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 2622kB
11. 3,709.026 3,709.026 ↑ 1.0 2,199,216 1

Seq Scan on skus s (cost=0.00..136,414.78 rows=2,227,654 width=14) (actual time=0.585..3,709.026 rows=2,199,216 loops=1)

  • Filter: (date_max >= (now() - '28 days'::interval))
  • Rows Removed by Filter: 3079743