explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5NWO

Settings
# exclusive inclusive rows x rows loops node
1. 2,581.774 186,191.093 ↑ 1.0 2,199,216 1

Hash Left Join (cost=6,351,197.34..6,589,048.24 rows=2,227,666 width=85) (actual time=177,639.506..186,191.093 rows=2,199,216 loops=1)

  • Hash Cond: (s.sku_key = stock_predictions.sku_key)
2. 2,205.103 6,956.141 ↑ 1.0 2,199,216 1

Hash Left Join (cost=229,267.70..416,563.98 rows=2,227,666 width=49) (actual time=981.122..6,956.141 rows=2,199,216 loops=1)

  • Hash Cond: ((s.store_key = stock_sales.store_key) AND (s.article_key = stock_sales.article_key) AND (s.color_key = stock_sales.color_key) AND (s.size_key = stock_sales.size_key))
3. 3,771.375 3,771.375 ↑ 1.0 2,199,216 1

Seq Scan on skus s (cost=0.00..136,414.78 rows=2,227,666 width=23) (actual time=0.013..3,771.375 rows=2,199,216 loops=1)

  • Filter: (date_max >= (now() - '28 days'::interval))
  • Rows Removed by Filter: 3079743
4. 93.281 979.663 ↓ 1.0 205,378 1

Hash (cost=223,830.82..223,830.82 rows=202,594 width=26) (actual time=979.663..979.663 rows=205,378 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3725kB
5. 216.481 886.382 ↓ 1.0 205,378 1

GroupAggregate (cost=216,090.45..221,804.88 rows=202,594 width=26) (actual time=535.589..886.382 rows=205,378 loops=1)

  • Group Key: stock_sales.store_key, stock_sales.article_key, stock_sales.color_key, stock_sales.size_key
6. 465.720 669.901 ↓ 2.0 431,719 1

Sort (cost=216,090.45..216,617.38 rows=210,771 width=14) (actual time=535.572..669.901 rows=431,719 loops=1)

  • Sort Key: stock_sales.store_key, stock_sales.article_key, stock_sales.color_key, stock_sales.size_key
  • Sort Method: external merge Disk: 11008kB
7. 146.752 204.181 ↓ 2.0 431,719 1

Bitmap Heap Scan on stock_sales (cost=4,472.85..193,847.69 rows=210,771 width=14) (actual time=58.565..204.181 rows=431,719 loops=1)

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

Bitmap Index Scan on stock_sales_date_key_idx (cost=0.00..4,420.16 rows=210,771 width=0) (actual time=57.429..57.429 rows=431,719 loops=1)

  • Index Cond: ((date_key < now()) AND (date_key >= (now() - '28 days'::interval)))
9. 660.748 176,653.178 ↓ 8.0 1,217,633 1

Hash (cost=6,118,818.57..6,118,818.57 rows=153,126 width=36) (actual time=176,653.178..176,653.178 rows=1,217,633 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 16 (originally 4) Memory Usage: 3587kB
10. 9,254.495 175,992.430 ↓ 8.0 1,217,633 1

GroupAggregate (cost=5,927,231.20..6,117,287.31 rows=153,126 width=36) (actual time=157,072.288..175,992.430 rows=1,217,633 loops=1)

  • Group Key: stock_predictions.sku_key
11. 42,613.154 166,737.935 ↓ 1.0 25,354,247 1

Sort (cost=5,927,231.20..5,989,945.21 rows=25,085,605 width=9) (actual time=157,072.225..166,737.935 rows=25,354,247 loops=1)

  • Sort Key: stock_predictions.sku_key
  • Sort Method: external merge Disk: 471456kB
12. 105,052.059 124,124.781 ↓ 1.0 25,354,247 1

Bitmap Heap Scan on stock_predictions (cost=532,264.03..1,558,031.14 rows=25,085,605 width=9) (actual time=19,083.629..124,124.781 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
13. 19,072.722 19,072.722 ↓ 1.0 25,354,247 1

Bitmap Index Scan on stock_predictions_date_key_idx (cost=0.00..525,992.62 rows=25,085,605 width=0) (actual time=19,072.722..19,072.722 rows=25,354,247 loops=1)

  • Index Cond: ((date_key >= now()) AND (date_key < (now() + '28 days'::interval)))