explain.depesz.com

PostgreSQL's explain analyze made readable

Result: psGh

Settings
# exclusive inclusive rows x rows loops node
1. 1,343.001 207,394.774 ↓ 1.4 751,958 1

GroupAggregate (cost=7,468,370.01..7,620,664.49 rows=527,896 width=104) (actual time=202,879.230..207,394.774 rows=751,958 loops=1)

  • Group Key: s.store_key, s.article_key, s.color_key
2. 1,463.450 206,051.773 ↑ 2.0 2,398,214 1

Merge Left Join (cost=7,468,370.01..7,538,720.57 rows=4,847,049 width=56) (actual time=202,879.210..206,051.773 rows=2,398,214 loops=1)

  • Merge 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. 4,216.955 199,983.700 ↑ 2.0 2,398,214 1

Sort (cost=7,252,297.73..7,264,415.35 rows=4,847,049 width=42) (actual time=198,637.581..199,983.700 rows=2,398,214 loops=1)

  • Sort Key: s.store_key, s.article_key, s.color_key, s.size_key
  • Sort Method: external merge Disk: 55304kB
4. 2,377.457 195,766.745 ↑ 2.0 2,398,214 1

Hash Left Join (cost=6,138,508.87..6,415,851.04 rows=4,847,049 width=42) (actual time=185,585.905..195,766.745 rows=2,398,214 loops=1)

  • Hash Cond: (s.sku_key = p.sku_key)
5. 5,193.628 8,202.611 ↑ 2.0 2,398,214 1

Hash Left Join (cost=16,637.85..232,723.51 rows=4,847,049 width=14) (actual time=392.276..8,202.611 rows=2,398,214 loops=1)

  • Hash Cond: ((s.store_key = sc.store_key) AND (s.article_key = sc.article_key) AND (s.color_key = sc.color_key) AND (s.size_key = sc.size_key))
  • Filter: ((s.date_max >= (now() - '28 days'::interval)) OR (sc.stock_technical > 0))
  • Rows Removed by Filter: 2880745
6. 2,622.169 2,622.169 ↑ 1.0 5,278,959 1

Seq Scan on skus s (cost=0.00..96,822.59 rows=5,278,959 width=18) (actual time=0.588..2,622.169 rows=5,278,959 loops=1)

7. 217.368 386.814 ↑ 1.0 403,295 1

Hash (cost=6,601.95..6,601.95 rows=403,295 width=14) (actual time=386.814..386.814 rows=403,295 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3399kB
8. 169.446 169.446 ↑ 1.0 403,295 1

Seq Scan on stock_current sc (cost=0.00..6,601.95 rows=403,295 width=14) (actual time=0.006..169.446 rows=403,295 loops=1)

9. 581.006 185,186.677 ↓ 8.0 1,217,633 1

Hash (cost=6,118,759.94..6,118,759.94 rows=153,126 width=36) (actual time=185,186.677..185,186.677 rows=1,217,633 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 16 (originally 4) Memory Usage: 3788kB
10. 515.071 184,605.671 ↓ 8.0 1,217,633 1

Subquery Scan on p (cost=5,927,174.51..6,118,759.94 rows=153,126 width=36) (actual time=164,497.214..184,605.671 rows=1,217,633 loops=1)

11. 9,407.594 184,090.600 ↓ 8.0 1,217,633 1

GroupAggregate (cost=5,927,174.51..6,117,228.68 rows=153,126 width=36) (actual time=164,497.211..184,090.600 rows=1,217,633 loops=1)

  • Group Key: stock_predictions.sku_key
12. 43,697.030 174,683.006 ↓ 1.0 25,354,247 1

Sort (cost=5,927,174.51..5,989,887.88 rows=25,085,346 width=9) (actual time=164,484.027..174,683.006 rows=25,354,247 loops=1)

  • Sort Key: stock_predictions.sku_key
  • Sort Method: external merge Disk: 471456kB
13. 122,977.083 130,985.976 ↓ 1.0 25,354,247 1

Bitmap Heap Scan on stock_predictions (cost=532,257.37..1,558,018.66 rows=25,085,346 width=9) (actual time=8,020.042..130,985.976 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
14. 8,008.893 8,008.893 ↓ 1.0 25,354,247 1

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

  • Index Cond: ((date_key >= now()) AND (date_key < (now() + '28 days'::interval)))
15. 222.707 4,604.623 ↓ 1.0 205,378 1

GroupAggregate (cost=216,072.28..221,784.38 rows=202,513 width=26) (actual time=4,241.098..4,604.623 rows=205,378 loops=1)

  • Group Key: stock_sales.store_key, stock_sales.article_key, stock_sales.color_key, stock_sales.size_key
16. 533.857 4,381.916 ↓ 2.0 431,719 1

Sort (cost=216,072.28..216,598.99 rows=210,684 width=14) (actual time=4,241.084..4,381.916 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
17. 3,752.903 3,848.059 ↓ 2.0 431,719 1

Bitmap Heap Scan on stock_sales (cost=4,471.96..193,841.34 rows=210,684 width=14) (actual time=97.621..3,848.059 rows=431,719 loops=1)

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

Bitmap Index Scan on stock_sales_date_key_idx (cost=0.00..4,419.28 rows=210,684 width=0) (actual time=95.156..95.156 rows=431,719 loops=1)

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