explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vI4s

Settings
# exclusive inclusive rows x rows loops node
1. 0.761 2,632.668 ↓ 383.0 383 1

Sort (cost=65,686.38..65,686.38 rows=1 width=662) (actual time=2,632.632..2,632.668 rows=383 loops=1)

  • Sort Key: (max(sp_1."position")), m.label
  • Sort Method: quicksort Memory: 222kB
2. 2.439 2,631.907 ↓ 383.0 383 1

WindowAgg (cost=65,686.23..65,686.37 rows=1 width=662) (actual time=2,629.796..2,631.907 rows=383 loops=1)

3. 0.375 2,629.468 ↓ 383.0 383 1

WindowAgg (cost=65,686.23..65,686.26 rows=1 width=451) (actual time=2,629.094..2,629.468 rows=383 loops=1)

4. 0.225 2,629.093 ↓ 383.0 383 1

Sort (cost=65,686.23..65,686.23 rows=1 width=355) (actual time=2,629.062..2,629.093 rows=383 loops=1)

  • Sort Key: m.commoditygroup_id
  • Sort Method: quicksort Memory: 126kB
5. 0.363 2,628.868 ↓ 383.0 383 1

WindowAgg (cost=65,686.19..65,686.22 rows=1 width=355) (actual time=2,628.496..2,628.868 rows=383 loops=1)

6. 0.349 2,628.505 ↓ 383.0 383 1

Sort (cost=65,686.19..65,686.19 rows=1 width=259) (actual time=2,628.466..2,628.505 rows=383 loops=1)

  • Sort Key: oodl.color_code
  • Sort Method: quicksort Memory: 126kB
7. 0.701 2,628.156 ↓ 383.0 383 1

WindowAgg (cost=65,685.97..65,686.18 rows=1 width=259) (actual time=2,602.276..2,628.156 rows=383 loops=1)

8. 21.934 2,627.455 ↓ 383.0 383 1

GroupAggregate (cost=65,685.97..65,686.15 rows=1 width=163) (actual time=2,602.089..2,627.455 rows=383 loops=1)

  • Group Key: oodl.model_id, oodl.color_code, m.id, col.id
  • Filter: (((sum(oodl.deliveryquantity) - COALESCE(min((sum(oor.quantity))), '0'::bigint)) - COALESCE(min((sum(CASE WHEN (orderoverviewsale.salestart < '2019-02-25'::date) THEN orderoverviewsale.quantity ELSE 0 END (...)
  • Rows Removed by Filter: 57
9. 24.380 2,605.521 ↓ 40,245.0 40,245 1

Sort (cost=65,685.97..65,685.98 rows=1 width=143) (actual time=2,601.995..2,605.521 rows=40,245 loops=1)

  • Sort Key: m.id, oodl.color_code, col.id
  • Sort Method: quicksort Memory: 11586kB
10. 8.734 2,581.141 ↓ 40,245.0 40,245 1

Merge Right Join (cost=65,614.20..65,685.96 rows=1 width=143) (actual time=2,456.019..2,581.141 rows=40,245 loops=1)

  • Merge Cond: ((orderoverviewsale.model_id = oodl.model_id) AND ((orderoverviewsale.color_code)::text = (oodl.color_code)::text))
11. 21.872 1,820.770 ↑ 2.9 441 1

GroupAggregate (cost=47,793.38..47,841.94 rows=1,295 width=28) (actual time=1,792.222..1,820.770 rows=441 loops=1)

  • Group Key: orderoverviewsale.model_id, orderoverviewsale.color_code
12. 32.533 1,798.898 ↓ 65.4 84,751 1

Sort (cost=47,793.38..47,796.61 rows=1,295 width=16) (actual time=1,792.122..1,798.898 rows=84,751 loops=1)

  • Sort Key: orderoverviewsale.model_id, orderoverviewsale.color_code
  • Sort Method: quicksort Memory: 8746kB
13. 12.214 1,766.365 ↓ 65.4 84,751 1

Nested Loop (cost=338.33..47,726.43 rows=1,295 width=16) (actual time=2.339..1,766.365 rows=84,751 loops=1)

  • Join Filter: (m_1.id = orderoverviewsale.model_id)
14. 0.425 3.301 ↑ 1.5 475 1

Hash Join (cost=337.77..777.38 rows=701 width=12) (actual time=2.276..3.301 rows=475 loops=1)

  • Hash Cond: (sp.model_id = m_1.id)
15. 0.700 0.804 ↑ 1.5 475 1

Bitmap Heap Scan on salesprice sp (cost=28.91..458.79 rows=725 width=8) (actual time=0.165..0.804 rows=475 loops=1)

  • Recheck Cond: ((valid >= '2019-02-20'::date) AND (valid <= '2019-03-19'::date))
  • Filter: ((price > '0'::numeric) AND ((target)::text = 'PARTNER'::text))
  • Rows Removed by Filter: 824
  • Heap Blocks: exact=38
16. 0.104 0.104 ↓ 1.1 1,417 1

Bitmap Index Scan on salesprice_valid_idx (cost=0.00..28.73 rows=1,244 width=0) (actual time=0.104..0.104 rows=1,417 loops=1)

  • Index Cond: ((valid >= '2019-02-20'::date) AND (valid <= '2019-03-19'::date))
17. 1.001 2.072 ↓ 1.0 7,140 1

Hash (cost=221.16..221.16 rows=7,016 width=4) (actual time=2.072..2.072 rows=7,140 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 316kB
18. 1.071 1.071 ↓ 1.0 7,140 1

Seq Scan on model m_1 (cost=0.00..221.16 rows=7,016 width=4) (actual time=0.006..1.071 rows=7,140 loops=1)

19. 1,750.850 1,750.850 ↓ 178.0 178 475

Index Scan using orderoverviewsale_model_id_color_code_salestart_idx on orderoverviewsale (cost=0.56..66.96 rows=1 width=16) (actual time=0.074..3.686 rows=178 loops=475)

  • Index Cond: ((model_id = sp.model_id) AND ((color_code)::text = (sp.color_code)::text))
  • Filter: (((((salestart - '2019-02-25'::date) / 7) + 1) <= 8) AND (customer_id = ANY ('{2103,2118,3025,3026,3323,3325,4601,5948,5951,5953,5957,5963,6089,6241,6285,6286,6301,6311 (...)
  • Rows Removed by Filter: 3339
20. 12.566 751.637 ↓ 40,245.0 40,245 1

Materialize (cost=17,820.82..17,824.58 rows=1 width=127) (actual time=663.792..751.637 rows=40,245 loops=1)

21. 16.812 739.071 ↓ 40,245.0 40,245 1

Nested Loop (cost=17,820.82..17,824.58 rows=1 width=127) (actual time=663.784..739.071 rows=40,245 loops=1)

22. 11.993 682.014 ↓ 40,245.0 40,245 1

Merge Left Join (cost=17,820.54..17,824.27 rows=1 width=77) (actual time=663.773..682.014 rows=40,245 loops=1)

  • Merge Cond: ((oodl.model_id = oor.model_id) AND ((oodl.color_code)::text = (oor.color_code)::text))
23. 19.800 647.931 ↓ 40,245.0 40,245 1

Sort (cost=4,666.44..4,666.44 rows=1 width=69) (actual time=643.471..647.931 rows=40,245 loops=1)

  • Sort Key: oodl.model_id, oodl.color_code
  • Sort Method: quicksort Memory: 6090kB
24. 616.957 628.131 ↓ 40,245.0 40,245 1

Nested Loop (cost=4,303.03..4,666.43 rows=1 width=69) (actual time=9.125..628.131 rows=40,245 loops=1)

  • -> Index Scan using orderoverviewdelivery_model_id_color_code_idx on orderoverviewdelivery oodl (cost=0.56..40.59 rows=1 width=12) (actual time=0.151..1.285 rows=85 loo (...)
25. 1.691 11.174 ↓ 475.0 475 1

Hash Join (cost=4,302.46..4,625.83 rows=1 width=65) (actual time=8.866..11.174 rows=475 loops=1)

  • Hash Cond: ((m2c.model_id = sp_1.model_id) AND (m2c.color_id = col.id))
  • Index Cond: ((model_id = m2c.model_id) AND ((color_code)::text = (col.code)::text))
  • Filter: (customer_id = ANY ('{2103,2118,3025,3026,3323,3325,4601,5948,5951,5953,5957,5963,6089,6241,6285,6286,6301,6311,6321,6325,6522,6531,6595,6731,6732,7175,7792 (...)
  • Rows Removed by Filter: 2099
26. 1.531 1.531 ↓ 1.0 13,516 1

Seq Scan on model2color m2c (cost=0.00..222.23 rows=13,483 width=8) (actual time=0.010..1.531 rows=13,516 loops=1)

  • Filter: active
  • Rows Removed by Filter: 40
27. 0.073 7.952 ↑ 1.4 475 1

Hash (cost=4,292.62..4,292.62 rows=656 width=61) (actual time=7.952..7.952 rows=475 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
28. 0.132 7.879 ↑ 1.4 475 1

Hash Join (cost=4,258.23..4,292.62 rows=656 width=61) (actual time=7.451..7.879 rows=475 loops=1)

  • Hash Cond: ((sp_1.color_code)::text = (col.code)::text)
29. 0.303 7.387 ↑ 1.4 475 1

GroupAggregate (cost=4,223.03..4,241.04 rows=655 width=46) (actual time=7.072..7.387 rows=475 loops=1)

  • Group Key: sp_1.model_id, sp_1.color_code, sp_1."position
  • Filter: ((min(sp_1.valid) >= '2019-02-20'::date) AND (min(sp_1.valid) <= '2019-03-19'::date))
30. 0.184 7.084 ↑ 1.4 475 1

Sort (cost=4,223.03..4,224.66 rows=655 width=20) (actual time=7.056..7.084 rows=475 loops=1)

  • Sort Key: sp_1.model_id, sp_1.color_code, sp_1."position
  • Sort Method: quicksort Memory: 62kB
31. 0.171 6.900 ↑ 1.4 475 1

Hash Join (cost=3,579.24..4,192.39 rows=655 width=20) (actual time=6.440..6.900 rows=475 loops=1)

  • Hash Cond: (sp_1.model_id = mp.model_id)
32. 0.375 0.483 ↑ 1.5 475 1

Bitmap Heap Scan on salesprice sp_1 (cost=28.91..458.79 rows=725 width=14) (actual time=0.176..0.483 rows=475 loops=1)

  • Recheck Cond: ((valid >= '2019-02-20'::date) AND (valid <= '2019-03-19'::date))
  • Filter: ((price > '0'::numeric) AND ((target)::text = 'PARTNER'::text))
  • Rows Removed by Filter: 824
  • Heap Blocks: exact=38
33. 0.108 0.108 ↓ 1.1 1,417 1

Bitmap Index Scan on salesprice_valid_idx (cost=0.00..28.73 rows=1,244 width=0) (actual time=0.108..0.108 rows=1,417 loops=1)

  • Index Cond: ((valid >= '2019-02-20'::date) AND (valid <= '2019-03-19'::date))
34. 1.124 6.246 ↓ 1.1 7,063 1

Hash (cost=3,468.30..3,468.30 rows=6,562 width=10) (actual time=6.246..6.246 rows=7,063 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 367kB
35. 5.122 5.122 ↓ 1.1 7,063 1

Bitmap Heap Scan on modelprice mp (cost=123.28..3,468.30 rows=6,562 width=10) (actual time=1.041..5.122 rows=7,063 loops=1)

  • Recheck Cond: ((pricegroup)::text = '101'::text)
  • Heap Blocks: exact=2700
  • -> Bitmap Index Scan on modelprice_pricegroup_idx (cost=0.00..121.64 rows=6562 width=0) (actual time=0.698..0.698 rows=7 (...)
  • Index Cond: ((pricegroup)::text = '101'::text)
36. 0.207 0.360 ↓ 1.0 1,125 1

Hash (cost=21.20..21.20 rows=1,120 width=19) (actual time=0.360..0.360 rows=1,125 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
37. 0.153 0.153 ↓ 1.0 1,125 1

Seq Scan on color col (cost=0.00..21.20 rows=1,120 width=19) (actual time=0.005..0.153 rows=1,125 loops=1)

38. 1.137 22.090 ↓ 228.9 22,660 1

Materialize (cost=13,154.10..13,157.32 rows=99 width=16) (actual time=20.298..22.090 rows=22,660 loops=1)

39. 0.414 20.953 ↓ 1.5 146 1

GroupAggregate (cost=13,154.10..13,156.08 rows=99 width=16) (actual time=20.294..20.953 rows=146 loops=1)

  • Group Key: oor.model_id, oor.color_code
40. 0.884 20.539 ↓ 25.1 2,481 1

Sort (cost=13,154.10..13,154.35 rows=99 width=12) (actual time=20.287..20.539 rows=2,481 loops=1)

  • Sort Key: oor.model_id, oor.color_code
  • Sort Method: quicksort Memory: 213kB
41. 7.154 19.655 ↓ 25.1 2,481 1

Hash Join (cost=1,822.21..13,150.82 rows=99 width=12) (actual time=16.446..19.655 rows=2,481 loops=1)

  • Hash Cond: ((oor.model_id = m_2.id) AND ((oor.color_code)::text = (sp_2.color_code)::text))
42. 6.182 9.821 ↓ 1.6 57,651 1

Bitmap Heap Scan on orderoverviewreturn oor (cost=1,034.31..12,094.96 rows=35,596 width=12) (actual time=3.780..9.821 rows=57,651 loops=1)

  • Recheck Cond: (customer_id = ANY ('{2103,2118,3025,3026,3323,3325,4601,5948,5951,5953,5957,5963,6089,6241,6285,6286,6301,6311,6321,6325,6522,6531,6595,6 (...)
  • Heap Blocks: exact=1165
43. 3.639 3.639 ↓ 1.6 57,651 1

Bitmap Index Scan on orderoverviewreturn_pkey (cost=0.00..1,025.41 rows=35,596 width=0) (actual time=3.639..3.639 rows=57,651 loops=1)

  • Index Cond: (customer_id = ANY ('{2103,2118,3025,3026,3323,3325,4601,5948,5951,5953,5957,5963,6089,6241,6285,6286,6301,6311,6321,6325,6522,6531,65 (...)
44. 0.089 2.680 ↑ 1.5 475 1

Hash (cost=777.38..777.38 rows=701 width=12) (actual time=2.680..2.680 rows=475 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
45. 0.154 2.591 ↑ 1.5 475 1

Hash Join (cost=337.77..777.38 rows=701 width=12) (actual time=2.172..2.591 rows=475 loops=1)

  • Hash Cond: (sp_2.model_id = m_2.id)
46. 0.374 0.488 ↑ 1.5 475 1

Bitmap Heap Scan on salesprice sp_2 (cost=28.91..458.79 rows=725 width=8) (actual time=0.179..0.488 rows=475 loops=1)

  • Recheck Cond: ((valid >= '2019-02-20'::date) AND (valid <= '2019-03-19'::date))
  • Filter: ((price > '0'::numeric) AND ((target)::text = 'PARTNER'::text))
  • Rows Removed by Filter: 824
  • Heap Blocks: exact=38
47. 0.114 0.114 ↓ 1.1 1,417 1

Bitmap Index Scan on salesprice_valid_idx (cost=0.00..28.73 rows=1,244 width=0) (actual time=0.114..0.114 rows=1,417 loops=1)

  • Index Cond: ((valid >= '2019-02-20'::date) AND (valid <= '2019-03-19'::date))
48. 0.895 1.949 ↓ 1.0 7,140 1

Hash (cost=221.16..221.16 rows=7,016 width=4) (actual time=1.949..1.949 rows=7,140 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 316kB
49. 1.054 1.054 ↓ 1.0 7,140 1

Seq Scan on model m_2 (cost=0.00..221.16 rows=7,016 width=4) (actual time=0.006..1.054 rows=7,140 loops=1)

50. 40.245 40.245 ↑ 1.0 1 40,245

Index Scan using model_pkey on model m (cost=0.28..0.30 rows=1 width=58) (actual time=0.001..0.001 rows=1 loops=40,245)

  • Index Cond: (id = oodl.model_id)