explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3rVI

Settings
# exclusive inclusive rows x rows loops node
1. 8,219.659 200,191.003 ↑ 1.0 2,199,216 1

GroupAggregate (cost=4,077,339.37..4,237,507.79 rows=2,227,661 width=52) (actual time=184,916.968..200,191.003 rows=2,199,216 loops=1)

  • Group Key: s.sku_key
2. 28,531.306 191,971.344 ↓ 1.8 19,008,936 1

Sort (cost=4,077,339.37..4,103,803.90 rows=10,585,813 width=13) (actual time=184,907.376..191,971.344 rows=19,008,936 loops=1)

  • Sort Key: s.sku_key
  • Sort Method: external merge Disk: 382744kB
3. 20,987.141 163,440.038 ↓ 1.8 19,008,936 1

Hash Right Join (cost=969,653.96..2,480,383.36 rows=10,585,813 width=13) (actual time=19,143.201..163,440.038 rows=19,008,936 loops=1)

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

Bitmap Heap Scan on stock_predictions (cost=532,263.33..1,558,028.91 rows=25,085,537 width=9) (actual time=8,018.838..131,331.225 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. 8,008.172 8,008.172 ↓ 1.0 25,354,247 1

Bitmap Index Scan on stock_predictions_date_key_idx (cost=0.00..525,991.94 rows=25,085,537 width=0) (actual time=8,008.172..8,008.172 rows=25,354,247 loops=1)

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

Hash (cost=400,842.87..400,842.87 rows=2,227,661 width=8) (actual time=11,121.672..11,121.672 rows=2,425,557 loops=1)

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

Hash Right Join (cost=196,318.61..400,842.87 rows=2,227,661 width=8) (actual time=4,856.793..10,189.137 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,871.625 4,024.869 ↓ 2.0 431,719 1

Bitmap Heap Scan on stock_sales (cost=4,472.61..193,846.01 rows=210,748 width=14) (actual time=155.035..4,024.869 rows=431,719 loops=1)

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

Bitmap Index Scan on stock_sales_date_key_idx (cost=0.00..4,419.93 rows=210,748 width=0) (actual time=153.244..153.244 rows=431,719 loops=1)

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

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

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

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

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