explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lEtJ

Settings
# exclusive inclusive rows x rows loops node
1. 4.000 21,382.509 ↑ 72.2 4,203 1

Sort (cost=524,247.19..525,005.51 rows=303,329 width=142) (actual time=21,381.974..21,382.509 rows=4,203 loops=1)

  • Sort Key: ((sum(LEAST((p.potential * ('1'::numeric - sr.return_perc)), (GREATEST((stock_proposals.proposal)::integer, (GREATEST(0, stock_current.economical))))::numeric)))::integer) DESC
  • Sort Method: quicksort Memory: 784kB
2. 14.176 21,378.509 ↑ 72.2 4,203 1

Hash Left Join (cost=405,771.51..474,854.79 rows=303,329 width=142) (actual time=16,418.239..21,378.509 rows=4,203 loops=1)

  • Hash Cond: ((stock_proposals.org_key = dc.org_key) AND (sk.option_key = dc.option_key))
3. 4,523.826 21,320.730 ↑ 72.2 4,203 1

GroupAggregate (cost=399,581.55..459,489.02 rows=303,329 width=417) (actual time=16,374.608..21,320.730 rows=4,203 loops=1)

  • Group Key: stock_proposals.org_key, sk.option_key, sk2.properties
4. 8,243.679 16,796.904 ↓ 4.5 1,368,949 1

Sort (cost=399,581.55..400,339.87 rows=303,329 width=366) (actual time=16,374.341..16,796.904 rows=1,368,949 loops=1)

  • Sort Key: sk.option_key, sk2.properties
  • Sort Method: external merge Disk: 527,120kB
5. 1,048.149 8,553.225 ↓ 4.5 1,368,949 1

Hash Right Join (cost=264,133.87..270,357.65 rows=303,329 width=366) (actual time=7,439.212..8,553.225 rows=1,368,949 loops=1)

  • Hash Cond: ((sk2.org_key = sk.org_key) AND (sk2.option_key = sk.option_key))
6. 12.529 252.483 ↓ 43.3 23,917 1

Subquery Scan on sk2 (cost=43,520.24..47,381.51 rows=552 width=353) (actual time=132.285..252.483 rows=23,917 loops=1)

  • Filter: (sk2.rn = 1)
  • Rows Removed by Filter: 86,405
7. 77.506 239.954 ↑ 1.0 110,322 1

WindowAgg (cost=43,520.24..46,002.49 rows=110,322 width=472) (actual time=132.282..239.954 rows=110,322 loops=1)

8. 123.031 162.448 ↑ 1.0 110,322 1

Sort (cost=43,520.24..43,796.05 rows=110,322 width=357) (actual time=132.268..162.448 rows=110,322 loops=1)

  • Sort Key: skus.option_key, skus.sku_key
  • Sort Method: external merge Disk: 39,768kB
9. 39.417 39.417 ↑ 1.0 110,322 1

Seq Scan on skus (cost=0.00..16,178.02 rows=110,322 width=357) (actual time=0.015..39.417 rows=110,322 loops=1)

  • Filter: (org_key = 1)
10. 432.980 7,252.593 ↓ 4.5 1,368,949 1

Hash (cost=213,989.69..213,989.69 rows=303,329 width=25) (actual time=7,252.593..7,252.593 rows=1,368,949 loops=1)

  • Buckets: 65,536 (originally 65536) Batches: 32 (originally 8) Memory Usage: 3,585kB
11. 1,552.907 6,819.613 ↓ 4.5 1,368,949 1

Hash Join (cost=182,787.19..213,989.69 rows=303,329 width=25) (actual time=2,917.671..6,819.613 rows=1,368,949 loops=1)

  • Hash Cond: ((stock_proposals.sku_key = p.sku_key) AND (stock_proposals.store_key = p.store_key))
12. 1,203.372 4,399.904 ↓ 5.2 1,657,425 1

Hash Left Join (cost=112,715.28..129,971.07 rows=318,039 width=30) (actual time=2,050.683..4,399.904 rows=1,657,425 loops=1)

  • Hash Cond: ((stock_proposals.org_key = stock_current.org_key) AND (stock_proposals.store_key = stock_current.store_key) AND (stock_proposals.sku_key = stock_current.sku_key))
13. 734.159 3,027.322 ↓ 5.2 1,657,425 1

Merge Left Join (cost=93,797.86..102,410.10 rows=318,039 width=26) (actual time=1,881.115..3,027.322 rows=1,657,425 loops=1)

  • Merge Cond: ((stock_proposals.store_key = sr.store_key) AND (sk.group_key = sr.group_key))
  • Join Filter: (stock_proposals.org_key = sr.org_key)
14. 1,305.633 2,176.787 ↓ 5.2 1,657,425 1

Sort (cost=92,974.21..93,769.31 rows=318,039 width=24) (actual time=1,877.264..2,176.787 rows=1,657,425 loops=1)

  • Sort Key: stock_proposals.store_key, sk.group_key
  • Sort Method: external merge Disk: 58,480kB
15. 564.276 871.154 ↓ 5.2 1,657,425 1

Hash Join (cost=18,141.89..57,383.28 rows=318,039 width=24) (actual time=66.406..871.154 rows=1,657,425 loops=1)

  • Hash Cond: (stock_proposals.sku_key = sk.sku_key)
16. 240.623 240.623 ↑ 1.0 1,657,425 1

Seq Scan on stock_proposals (cost=0.00..29,842.31 rows=1,658,345 width=10) (actual time=0.009..240.623 rows=1,657,425 loops=1)

  • Filter: (org_key = 1)
17. 5.365 66.255 ↓ 1.0 21,525 1

Hash (cost=17,877.43..17,877.43 rows=21,157 width=14) (actual time=66.255..66.255 rows=21,525 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,223kB
18. 5.891 60.890 ↓ 1.0 21,525 1

Hash Join (cost=598.36..17,877.43 rows=21,157 width=14) (actual time=3.073..60.890 rows=21,525 loops=1)

  • Hash Cond: (sk.group_key = groups.group_key)
19. 16.142 54.951 ↓ 1.0 21,525 1

Hash Join (cost=593.40..17,814.00 rows=21,516 width=14) (actual time=3.014..54.951 rows=21,525 loops=1)

  • Hash Cond: (sk.option_key = ss.option_key)
20. 35.836 35.836 ↑ 1.0 110,322 1

Seq Scan on skus sk (cost=0.00..16,178.02 rows=110,322 width=14) (actual time=0.005..35.836 rows=110,322 loops=1)

  • Filter: (org_key = 1)
21. 0.645 2.973 ↑ 1.0 4,215 1

Hash (cost=540.72..540.72 rows=4,215 width=6) (actual time=2.972..2.973 rows=4,215 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 229kB
22. 2.328 2.328 ↑ 1.0 4,215 1

Seq Scan on stock_settings_options ss (cost=0.00..540.72 rows=4,215 width=6) (actual time=0.010..2.328 rows=4,215 loops=1)

  • Filter: ((org_key = 1) AND (date_min <= (now())::date) AND ((now())::date <= date_max))
  • Rows Removed by Filter: 102
23. 0.021 0.048 ↑ 1.0 118 1

Hash (cost=3.48..3.48 rows=118 width=4) (actual time=0.048..0.048 rows=118 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
24. 0.027 0.027 ↑ 1.0 118 1

Seq Scan on groups (cost=0.00..3.48 rows=118 width=4) (actual time=0.007..0.027 rows=118 loops=1)

  • Filter: (org_key = 1)
25. 114.487 116.376 ↓ 169.5 1,660,288 1

Sort (cost=823.65..848.14 rows=9,794 width=10) (actual time=3.840..116.376 rows=1,660,288 loops=1)

  • Sort Key: sr.store_key, sr.group_key
  • Sort Method: quicksort Memory: 844kB
26. 1.889 1.889 ↑ 1.0 9,794 1

Seq Scan on sales_returns sr (cost=0.00..174.43 rows=9,794 width=10) (actual time=0.014..1.889 rows=9,794 loops=1)

  • Filter: (org_key = 1)
27. 88.827 169.210 ↑ 1.1 325,822 1

Hash (cost=10,716.73..10,716.73 rows=366,382 width=12) (actual time=169.210..169.210 rows=325,822 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,781kB
28. 80.383 80.383 ↑ 1.1 325,822 1

Seq Scan on stock_current (cost=0.00..10,716.73 rows=366,382 width=12) (actual time=0.008..80.383 rows=325,822 loops=1)

  • Filter: (org_key = 1)
29. 513.157 866.802 ↑ 1.0 1,623,415 1

Hash (cost=37,793.69..37,793.69 rows=1,623,415 width=13) (actual time=866.802..866.802 rows=1,623,415 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 3,402kB
30. 353.645 353.645 ↑ 1.0 1,623,415 1

Seq Scan on predictions_skus_n4w p (cost=0.00..37,793.69 rows=1,623,415 width=13) (actual time=0.013..353.645 rows=1,623,415 loops=1)

  • Filter: (org_key = 1)
31. 0.676 43.603 ↓ 1.7 4,118 1

Hash (cost=6,154.04..6,154.04 rows=2,395 width=16) (actual time=43.603..43.603 rows=4,118 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 258kB
32. 0.640 42.927 ↓ 1.7 4,118 1

Subquery Scan on dc (cost=6,106.14..6,154.04 rows=2,395 width=16) (actual time=41.340..42.927 rows=4,118 loops=1)

33. 5.026 42.287 ↓ 1.7 4,118 1

HashAggregate (cost=6,106.14..6,130.09 rows=2,395 width=16) (actual time=41.339..42.287 rows=4,118 loops=1)

  • Group Key: sk_1.org_key, sk_1.option_key
34. 1.583 37.261 ↓ 5.8 13,937 1

Nested Loop (cost=0.98..6,082.19 rows=2,395 width=12) (actual time=0.047..37.261 rows=13,937 loops=1)

  • Join Filter: (st.org_key = sk_1.org_key)
35. 2.713 7.234 ↓ 5.9 14,222 1

Nested Loop (cost=0.57..4,898.23 rows=2,395 width=12) (actual time=0.034..7.234 rows=14,222 loops=1)

36. 0.057 0.057 ↑ 1.0 1 1

Index Scan using stores_pkey on stores st (cost=0.14..33.32 rows=1 width=4) (actual time=0.016..0.057 rows=1 loops=1)

  • Filter: (store_status = 'DC'::text)
  • Rows Removed by Filter: 151
37. 4.464 4.464 ↓ 4.1 14,222 1

Index Scan using stock_current_pkey on stock_current sc (cost=0.42..4,830.02 rows=3,489 width=12) (actual time=0.012..4.464 rows=14,222 loops=1)

  • Index Cond: ((org_key = st.org_key) AND (store_key = st.store_key))
38. 28.444 28.444 ↑ 1.0 1 14,222

Index Only Scan using skus_org_sku_option_idx on skus sk_1 (cost=0.42..0.48 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=14,222)

  • Index Cond: ((org_key = sc.org_key) AND (sku_key = sc.sku_key))
  • Heap Fetches: 9,519