explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SpU

Settings
# exclusive inclusive rows x rows loops node
1. 687.908 75,237.441 ↓ 0.0 0 1

Insert on f_bin_as (cost=1,031,741.53..1,756,084.19 rows=1,145,510 width=4,469) (actual time=75,237.441..75,237.441 rows=0 loops=1)

  • Functions: 466
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 68.109 ms, Inlining 77.157 ms, Optimization 4334.708 ms, Emission 3020.318 ms, Total 7500.292 ms
2. 1,381.594 74,549.533 ↑ 132.8 8,628 1

Hash Anti Join (cost=1,031,741.53..1,756,084.19 rows=1,145,510 width=4,469) (actual time=38,341.948..74,549.533 rows=8,628 loops=1)

  • Hash Cond: ((a.bin_number)::text = (w.bin_number)::text)
3. 1,578.029 71,268.394 ↑ 1.0 2,290,967 1

Merge Left Join (cost=894,960.40..1,222,527.03 rows=2,291,021 width=435) (actual time=36,416.003..71,268.394 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = ae.season) AND ((a.commodity_code)::text = (ae.fruit_type)::text) AND ((a.variety_code)::text = (ae.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (ae.farm_group_code)::text))
4. 1,629.607 69,528.872 ↑ 1.0 2,290,967 1

Merge Left Join (cost=893,673.36..1,198,279.82 rows=2,291,021 width=434) (actual time=36,406.174..69,528.872 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = af.season) AND ((a.commodity_code)::text = (af.fruit_type)::text) AND ((a.variety_code)::text = (af.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (af.farm_group_code)::text))
  • Join Filter: ((ab.season = af.season) AND ((ab.farm_group_code)::text = (af.farm_group_code)::text) AND ((ab.variety_code)::text = (af.rmt_variety_code)::text))
  • Rows Removed by Join Filter: 1984764
5. 1,632.073 67,746.083 ↑ 1.0 2,290,967 1

Merge Left Join (cost=892,622.85..1,174,295.12 rows=2,291,021 width=444) (actual time=36,401.893..67,746.083 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = ad.season) AND ((a.commodity_code)::text = (ad.fruit_type)::text) AND ((a.variety_code)::text = (ad.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (ad.farm_group_code)::text))
  • Join Filter: ((ab.season = ad.season) AND ((ab.farm_group_code)::text = (ad.farm_group_code)::text) AND ((ab.variety_code)::text = (ad.rmt_variety_code)::text))
  • Rows Removed by Join Filter: 1984764
6. 1,700.217 65,957.320 ↑ 1.0 2,290,967 1

Merge Left Join (cost=891,574.14..1,150,312.50 rows=2,291,021 width=439) (actual time=36,397.849..65,957.320 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = v.season) AND ((a.commodity_code)::text = (v.fruit_type)::text) AND ((a.variety_code)::text = (v.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (v.farm_group_code)::text))
  • Join Filter: ((ab.season = v.season) AND ((ab.farm_group_code)::text = (v.farm_group_code)::text) AND ((ab.variety_code)::text = (v.rmt_variety_code)::text))
  • Rows Removed by Join Filter: 1984764
7. 1,680.587 64,099.297 ↑ 1.0 2,290,967 1

Merge Left Join (cost=890,532.54..1,126,338.08 rows=2,291,021 width=434) (actual time=36,393.524..64,099.297 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = u.season) AND ((a.commodity_code)::text = (u.fruit_type)::text) AND ((a.variety_code)::text = (u.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (u.farm_group_code)::text))
  • Join Filter: ((ab.season = u.season) AND ((ab.farm_group_code)::text = (u.farm_group_code)::text) AND ((ab.variety_code)::text = (u.rmt_variety_code)::text))
  • Rows Removed by Join Filter: 1984764
8. 1,704.145 62,241.742 ↑ 1.0 2,290,967 1

Merge Left Join (cost=889,473.02..1,102,343.61 rows=2,291,021 width=429) (actual time=36,389.095..62,241.742 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = t.season) AND ((a.commodity_code)::text = (t.fruit_type)::text) AND ((a.variety_code)::text = (t.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (t.farm_group_code)::text))
  • Join Filter: ((aa.season = t.season) AND ((aa.commodity_code)::text = (t.fruit_type)::text) AND ((aa.farm_group_code)::text = (t.farm_group_code)::text) AND ((aa.variety_code)::text = (t.rmt_variety_code)::text))
  • Rows Removed by Join Filter: 2048111
9. 1,710.943 60,383.671 ↑ 1.0 2,290,967 1

Merge Left Join (cost=888,172.99..1,078,082.06 rows=2,291,021 width=442) (actual time=36,381.252..60,383.671 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = s.season) AND ((a.commodity_code)::text = (s.fruit_type)::text) AND ((a.variety_code)::text = (s.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (s.farm_group_code)::text))
  • Join Filter: ((aa.season = s.season) AND ((aa.commodity_code)::text = (s.fruit_type)::text) AND ((aa.farm_group_code)::text = (s.farm_group_code)::text) AND ((aa.variety_code)::text = (s.rmt_variety_code)::text))
  • Rows Removed by Join Filter: 2046010
10. 5,661.265 58,502.136 ↑ 1.0 2,290,967 1

Merge Left Join (cost=886,975.76..1,053,934.70 rows=2,291,021 width=437) (actual time=36,374.889..58,502.136 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = n.season) AND ((a.commodity_code)::text = (n.fruit_type)::text) AND ((a.variety_code)::text = (n.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (n.farm_group_code)::text))
  • Join Filter: (((a.track_slms_indicator_1_code)::text = (r.track_indicator)::text) AND ((n.track_indicator)::text = (e.track_indicator_code)::text))
  • Rows Removed by Join Filter: 17164631
11. 4,902.459 51,795.929 ↑ 1.0 2,290,967 1

Merge Left Join (cost=883,125.29..1,026,840.74 rows=2,291,021 width=444) (actual time=36,333.130..51,795.929 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = r.season) AND ((a.commodity_code)::text = (r.fruit_type)::text) AND ((a.variety_code)::text = (r.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (r.farm_group_code)::text))
  • Join Filter: (((a.track_slms_indicator_1_code)::text = (r.track_indicator)::text) AND ((r.track_indicator)::text = (e.track_indicator_code)::text))
  • Rows Removed by Join Filter: 16195011
12. 1,513.063 45,836.406 ↑ 1.0 2,290,967 1

Merge Left Join (cost=879,541.54..1,000,055.13 rows=2,291,021 width=435) (actual time=36,284.579..45,836.406 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = q.season) AND ((a.commodity_code)::text = (q.fruit_type)::text) AND ((a.variety_code)::text = (q.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (q.farm_group_code)::text))
13. 1,423.165 44,130.385 ↑ 1.0 2,290,967 1

Merge Left Join (cost=878,241.51..975,793.64 rows=2,291,021 width=430) (actual time=36,276.733..44,130.385 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = p.season) AND ((a.commodity_code)::text = (p.fruit_type)::text) AND ((a.variety_code)::text = (p.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (p.farm_group_code)::text))
14. 1,497.178 42,528.476 ↑ 1.0 2,290,967 1

Merge Left Join (cost=876,939.11..951,529.45 rows=2,291,021 width=425) (actual time=36,267.747..42,528.476 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = m.season) AND ((a.commodity_code)::text = (m.fruit_type)::text) AND ((a.variety_code)::text = (m.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (m.farm_group_code)::text))
15. 1,436.295 40,854.089 ↑ 1.0 2,290,967 1

Merge Left Join (cost=875,733.05..927,372.02 rows=2,291,021 width=420) (actual time=36,261.001..40,854.089 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = l.season) AND ((a.commodity_code)::text = (l.fruit_type)::text) AND ((a.variety_code)::text = (l.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (l.farm_group_code)::text))
16. 2,346.424 39,236.956 ↑ 1.0 2,290,967 1

Merge Left Join (cost=874,544.78..903,234.41 rows=2,291,021 width=415) (actual time=36,253.549..39,236.956 rows=2,290,967 loops=1)

  • Merge Cond: ((a.season = ag.season) AND ((a.commodity_code)::text = (ag.fruit_type)::text) AND ((a.variety_code)::text = (ag.rmt_variety_code)::text) AND ((a.farm_group_code)::text = (ag.farm_group_code)::text))
17. 7,530.248 36,629.494 ↑ 1.0 2,290,967 1

Sort (cost=873,236.37..878,963.92 rows=2,291,021 width=410) (actual time=36,185.935..36,629.494 rows=2,290,967 loops=1)

  • Sort Key: a.season, a.commodity_code, a.variety_code, a.farm_group_code
  • Sort Method: quicksort Memory: 707142kB
18. 1,329.385 29,099.246 ↑ 1.0 2,290,967 1

Hash Left Join (cost=59,301.28..631,217.96 rows=2,291,021 width=410) (actual time=8,370.248..29,099.246 rows=2,290,967 loops=1)

  • Hash Cond: ((a.bin_number)::text = (ab.bin_number)::text)
19. 1,249.776 27,679.422 ↑ 1.0 2,290,967 1

Hash Left Join (cost=52,484.09..592,899.23 rows=2,291,021 width=395) (actual time=8,278.583..27,679.422 rows=2,290,967 loops=1)

  • Hash Cond: ((a.bin_number)::text = (aa.bin_number)::text)
20. 732.380 26,342.165 ↑ 1.0 2,290,967 1

Hash Left Join (cost=46,647.02..555,560.63 rows=2,291,021 width=365) (actual time=8,189.821..26,342.165 rows=2,290,967 loops=1)

  • Hash Cond: ((f_bin_sales_1.ordernr = tk.ordernr) AND ((f_bin_sales_1.exitrefheader)::text = (tk.exitrefheader)::text) AND ((f_bin_sales_1.artcode)::text = (tk.artcode)::text))
21. 851.333 25,601.906 ↑ 1.0 2,290,967 1

Hash Left Join (cost=45,949.19..536,821.01 rows=2,291,021 width=342) (actual time=8,181.913..25,601.906 rows=2,290,967 loops=1)

  • Hash Cond: ((f_bin_sales.ordernr = tw.ordernr) AND ((f_bin_sales.exitrefheader)::text = (tw.exitrefheader)::text) AND ((f_bin_sales.artcode)::text = (tw.artcode)::text))
22. 1,713.435 24,547.092 ↑ 1.0 2,290,967 1

Hash Left Join (cost=44,738.35..517,568.38 rows=2,291,021 width=319) (actual time=7,978.285..24,547.092 rows=2,290,967 loops=1)

  • Hash Cond: (((a.bin_number)::text = (f_bin_sales.bin_number)::text) AND ((a.bin_load_number)::text = (f_bin_sales.exitrefheader)::text) AND ((((a.track_slms_indicator_1_code)::text || '_'::text) || (a.rmt_product_code)::text) = (f_bin_sales.artcode)::text))
23. 875.600 22,709.244 ↑ 1.0 2,290,967 1

Hash Left Join (cost=35,332.64..476,661.11 rows=2,291,021 width=293) (actual time=7,852.614..22,709.244 rows=2,290,967 loops=1)

  • Hash Cond: (a.shift_id = j.id)
24. 717.376 21,814.320 ↑ 1.0 2,290,967 1

Hash Left Join (cost=30,778.73..466,092.75 rows=2,291,021 width=293) (actual time=7,832.963..21,814.320 rows=2,290,967 loops=1)

  • Hash Cond: ((a.rebin_run)::text = (i.production_run_code)::text)
25. 904.655 21,062.004 ↑ 1.0 2,290,967 1

Hash Left Join (cost=23,313.99..452,613.70 rows=2,291,021 width=309) (actual time=7,797.371..21,062.004 rows=2,290,967 loops=1)

  • Hash Cond: ((a.tipped_run)::text = (h.production_run_code)::text)
26. 950.487 20,117.150 ↑ 1.0 2,290,967 1

Hash Left Join (cost=15,849.24..439,134.65 rows=2,291,021 width=325) (actual time=7,756.961..20,117.150 rows=2,290,967 loops=1)

  • Hash Cond: ((a.farm_code)::text = (g.farm_code)::text)
27. 2,376.085 19,166.549 ↑ 1.0 2,290,967 1

Hash Left Join (cost=15,825.37..410,816.67 rows=2,291,021 width=325) (actual time=7,756.823..19,166.549 rows=2,290,967 loops=1)

  • Hash Cond: (((a.bin_number)::text = (f_bin_sales_1.bin_number)::text) AND ((a.bin_load_number)::text = (f_bin_sales_1.exitrefheader)::text) AND ((((((((((a.commodity_code)::text || '_'::text) || (a.variety_code)::text) || '_'::text) || (a.track_slms_indicator_1_code)::text) || '_'::text) || replace((a.product_class_description)::text, ' '::text, '_'::text)) || '_'::text) || (a.size_code)::text) = (f_bin_sales_1.artcode)::text))
28. 1,061.936 16,494.596 ↑ 1.0 2,290,967 1

Hash Left Join (cost=4,293.93..347,737.24 rows=2,291,021 width=286) (actual time=7,460.819..16,494.596 rows=2,290,967 loops=1)

  • Hash Cond: ((a.rmt_product_code)::text = (f.rmt_product_code)::text)
29. 966.493 15,424.788 ↑ 1.0 2,290,967 1

Hash Left Join (cost=2,482.80..339,909.91 rows=2,291,021 width=277) (actual time=7,452.880..15,424.788 rows=2,290,967 loops=1)

  • Hash Cond: ((a.track_slms_indicator_1_code)::text = (e.track_indicator_code)::text)
30. 792.267 14,458.023 ↑ 1.0 2,290,967 1

Hash Left Join (cost=2,415.67..333,781.56 rows=2,291,021 width=269) (actual time=7,452.590..14,458.023 rows=2,290,967 loops=1)

  • Hash Cond: ((a.sealed_ca_open_date_time)::date = ai.date_value)
31. 867.054 13,662.305 ↑ 1.0 2,290,967 1

Hash Left Join (cost=1,932.54..298,933.11 rows=2,291,021 width=273) (actual time=7,449.088..13,662.305 rows=2,290,967 loops=1)

  • Hash Cond: ((a.sealed_ca_date_time)::date = ah.date_value)
32. 639.491 12,791.788 ↑ 1.0 2,290,967 1

Hash Left Join (cost=1,449.40..264,084.66 rows=2,291,021 width=277) (actual time=7,445.596..12,791.788 rows=2,290,967 loops=1)

  • Hash Cond: ((a.rebin_date_time)::date = d.date_value)
33. 1,033.041 12,148.957 ↑ 1.0 2,290,967 1

Hash Left Join (cost=966.27..229,236.21 rows=2,291,021 width=281) (actual time=7,442.226..12,148.957 rows=2,290,967 loops=1)

  • Hash Cond: ((a.tipped_date_time)::date = c.date_value)
34. 1,002.102 11,112.569 ↑ 1.0 2,290,967 1

Hash Left Join (cost=483.13..194,387.76 rows=2,291,021 width=285) (actual time=7,438.850..11,112.569 rows=2,290,967 loops=1)

  • Hash Cond: ((a.created_on)::date = b.date_value)
35. 287.835 10,106.432 ↑ 1.0 2,290,967 1

Append (cost=0.00..159,539.32 rows=2,291,021 width=289) (actual time=7,434.710..10,106.432 rows=2,290,967 loops=1)

36. 9,238.355 9,238.355 ↑ 1.0 1,521,815 1

Seq Scan on mes_bin_old a (cost=0.00..97,936.15 rows=1,521,815 width=286) (actual time=7,434.708..9,238.355 rows=1,521,815 loops=1)

37. 580.242 580.242 ↑ 1.0 769,152 1

Seq Scan on mes_bin_current a_1 (cost=0.00..50,148.06 rows=769,206 width=296) (actual time=0.028..580.242 rows=769,152 loops=1)

38. 1.616 4.035 ↑ 1.0 11,206 1

Hash (cost=343.06..343.06 rows=11,206 width=8) (actual time=4.035..4.035 rows=11,206 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 566kB
39. 2.419 2.419 ↑ 1.0 11,206 1

Seq Scan on d_date b (cost=0.00..343.06 rows=11,206 width=8) (actual time=0.052..2.419 rows=11,206 loops=1)

40. 1.614 3.347 ↑ 1.0 11,206 1

Hash (cost=343.06..343.06 rows=11,206 width=8) (actual time=3.347..3.347 rows=11,206 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 566kB
41. 1.733 1.733 ↑ 1.0 11,206 1

Seq Scan on d_date c (cost=0.00..343.06 rows=11,206 width=8) (actual time=0.009..1.733 rows=11,206 loops=1)

42. 1.614 3.340 ↑ 1.0 11,206 1

Hash (cost=343.06..343.06 rows=11,206 width=8) (actual time=3.340..3.340 rows=11,206 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 566kB
43. 1.726 1.726 ↑ 1.0 11,206 1

Seq Scan on d_date d (cost=0.00..343.06 rows=11,206 width=8) (actual time=0.009..1.726 rows=11,206 loops=1)

44. 1.685 3.463 ↑ 1.0 11,206 1

Hash (cost=343.06..343.06 rows=11,206 width=8) (actual time=3.463..3.463 rows=11,206 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 566kB
45. 1.778 1.778 ↑ 1.0 11,206 1

Seq Scan on d_date ah (cost=0.00..343.06 rows=11,206 width=8) (actual time=0.014..1.778 rows=11,206 loops=1)

46. 1.686 3.451 ↑ 1.0 11,206 1

Hash (cost=343.06..343.06 rows=11,206 width=8) (actual time=3.451..3.451 rows=11,206 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 566kB
47. 1.765 1.765 ↑ 1.0 11,206 1

Seq Scan on d_date ai (cost=0.00..343.06 rows=11,206 width=8) (actual time=0.009..1.765 rows=11,206 loops=1)

48. 0.108 0.272 ↑ 1.0 539 1

Hash (cost=60.39..60.39 rows=539 width=8) (actual time=0.272..0.272 rows=539 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
49. 0.164 0.164 ↑ 1.0 539 1

Seq Scan on d_track_indicator e (cost=0.00..60.39 rows=539 width=8) (actual time=0.020..0.164 rows=539 loops=1)

50. 2.816 7.872 ↑ 1.0 11,250 1

Hash (cost=1,670.50..1,670.50 rows=11,250 width=33) (actual time=7.872..7.872 rows=11,250 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 847kB
51. 5.056 5.056 ↑ 1.0 11,250 1

Seq Scan on d_rmt_product f (cost=0.00..1,670.50 rows=11,250 width=33) (actual time=0.020..5.056 rows=11,250 loops=1)

52. 16.106 295.868 ↓ 1.0 41,326 1

Hash (cost=10,816.06..10,816.06 rows=40,879 width=67) (actual time=295.868..295.868 rows=41,326 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4590kB
53. 12.938 279.762 ↓ 1.0 41,326 1

Hash Left Join (cost=4,396.32..10,816.06 rows=40,879 width=67) (actual time=103.455..279.762 rows=41,326 loops=1)

  • Hash Cond: ((f_bin_sales_1.ordernr = o.order_number) AND ((f_bin_sales_1.exitrefheader)::text = (o.exit_ref_header)::text))
54. 215.694 215.694 ↓ 1.0 41,326 1

Seq Scan on f_bin_sales f_bin_sales_1 (cost=0.00..6,205.12 rows=40,879 width=59) (actual time=52.170..215.694 rows=41,326 loops=1)

  • Filter: (ordernr < 1026910)
  • Rows Removed by Filter: 182444
55. 15.827 51.130 ↑ 1.2 52,459 1

Hash (cost=3,479.33..3,479.33 rows=61,133 width=17) (actual time=51.130..51.130 rows=52,459 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3056kB
56. 35.303 35.303 ↑ 1.0 61,133 1

Seq Scan on d_sales_order o (cost=0.00..3,479.33 rows=61,133 width=17) (actual time=0.026..35.303 rows=61,133 loops=1)

57. 0.035 0.114 ↑ 1.0 172 1

Hash (cost=21.72..21.72 rows=172 width=8) (actual time=0.114..0.114 rows=172 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
58. 0.079 0.079 ↑ 1.0 172 1

Seq Scan on d_farm g (cost=0.00..21.72 rows=172 width=8) (actual time=0.018..0.079 rows=172 loops=1)

59. 18.393 40.199 ↑ 1.0 67,322 1

Hash (cost=6,623.22..6,623.22 rows=67,322 width=27) (actual time=40.198..40.199 rows=67,322 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4946kB
60. 21.806 21.806 ↑ 1.0 67,322 1

Seq Scan on d_production_run h (cost=0.00..6,623.22 rows=67,322 width=27) (actual time=0.024..21.806 rows=67,322 loops=1)

61. 18.824 34.940 ↑ 1.0 67,322 1

Hash (cost=6,623.22..6,623.22 rows=67,322 width=27) (actual time=34.940..34.940 rows=67,322 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4946kB
62. 16.116 16.116 ↑ 1.0 67,322 1

Seq Scan on d_production_run i (cost=0.00..6,623.22 rows=67,322 width=27) (actual time=0.013..16.116 rows=67,322 loops=1)

63. 8.931 19.324 ↑ 1.0 48,929 1

Hash (cost=3,942.29..3,942.29 rows=48,929 width=8) (actual time=19.324..19.324 rows=48,929 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2424kB
64. 10.393 10.393 ↑ 1.0 48,929 1

Seq Scan on d_shift j (cost=0.00..3,942.29 rows=48,929 width=8) (actual time=0.014..10.393 rows=48,929 loops=1)

65. 84.341 124.413 ↑ 1.0 182,444 1

Hash (cost=6,205.12..6,205.12 rows=182,891 width=59) (actual time=124.413..124.413 rows=182,444 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 19144kB
66. 40.072 40.072 ↑ 1.0 182,444 1

Seq Scan on f_bin_sales (cost=0.00..6,205.12 rows=182,891 width=59) (actual time=2.962..40.072 rows=182,444 loops=1)

  • Filter: (ordernr >= 1026910)
  • Rows Removed by Filter: 41326
67. 9.035 203.481 ↓ 2.3 21,471 1

Hash (cost=1,049.61..1,049.61 rows=9,213 width=107) (actual time=203.481..203.481 rows=21,471 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2029kB
68. 2.123 194.446 ↓ 2.3 21,471 1

Subquery Scan on tw (cost=819.29..1,049.61 rows=9,213 width=107) (actual time=177.633..194.446 rows=21,471 loops=1)

69. 52.820 192.323 ↓ 2.3 21,471 1

HashAggregate (cost=819.29..957.48 rows=9,213 width=107) (actual time=177.631..192.323 rows=21,471 loops=1)

  • Group Key: f_bin_sales_summary.ordernr, f_bin_sales_summary.exitrefheader, f_bin_sales_summary.artcode
70. 139.503 139.503 ↑ 1.0 21,471 1

Seq Scan on f_bin_sales_summary (cost=0.00..549.36 rows=21,594 width=55) (actual time=48.825..139.503 rows=21,471 loops=1)

  • Filter: (ordernr >= 1026910)
  • Rows Removed by Filter: 3038
71. 1.124 7.879 ↓ 1.2 3,038 1

Hash (cost=651.70..651.70 rows=2,636 width=107) (actual time=7.878..7.879 rows=3,038 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 266kB
72. 0.285 6.755 ↓ 1.2 3,038 1

Subquery Scan on tk (cost=585.80..651.70 rows=2,636 width=107) (actual time=5.255..6.755 rows=3,038 loops=1)

73. 4.220 6.470 ↓ 1.2 3,038 1

HashAggregate (cost=585.80..625.34 rows=2,636 width=107) (actual time=5.253..6.470 rows=3,038 loops=1)

  • Group Key: f_bin_sales_summary_1.ordernr, f_bin_sales_summary_1.exitrefheader, f_bin_sales_summary_1.artcode
74. 2.250 2.250 ↓ 1.0 3,038 1

Seq Scan on f_bin_sales_summary f_bin_sales_summary_1 (cost=0.00..549.36 rows=2,915 width=55) (actual time=0.026..2.250 rows=3,038 loops=1)

  • Filter: (ordernr < 1026910)
  • Rows Removed by Filter: 21471
75. 56.988 87.481 ↑ 1.0 178,803 1

Hash (cost=3,602.03..3,602.03 rows=178,803 width=39) (actual time=87.480..87.481 rows=178,803 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 14991kB
76. 30.493 30.493 ↑ 1.0 178,803 1

Seq Scan on ra_bin aa (cost=0.00..3,602.03 rows=178,803 width=39) (actual time=0.020..30.493 rows=178,803 loops=1)

77. 58.138 90.439 ↑ 1.0 209,386 1

Hash (cost=4,199.86..4,199.86 rows=209,386 width=24) (actual time=90.438..90.439 rows=209,386 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 14119kB
78. 32.301 32.301 ↑ 1.0 209,386 1

Seq Scan on ca_bin ab (cost=0.00..4,199.86 rows=209,386 width=24) (actual time=0.047..32.301 rows=209,386 loops=1)

79. 200.964 261.038 ↓ 538.2 2,230,691 1

Sort (cost=1,308.41..1,318.77 rows=4,145 width=25) (actual time=67.454..261.038 rows=2,230,691 loops=1)

  • Sort Key: ag.season, ag.fruit_type, ag.rmt_variety_code, ag.farm_group_code
  • Sort Method: quicksort Memory: 414kB
80. 1.591 60.074 ↑ 1.0 4,060 1

Bitmap Heap Scan on bin_tariff ag (cost=156.54..1,059.35 rows=4,145 width=25) (actual time=58.574..60.074 rows=4,060 loops=1)

  • Recheck Cond: ((fee_type)::text = 'BIN'::text)
  • Heap Blocks: exact=369
81. 58.483 58.483 ↑ 1.0 4,060 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..155.50 rows=4,145 width=0) (actual time=58.483..58.483 rows=4,060 loops=1)

  • Index Cond: ((fee_type)::text = 'BIN'::text)
82. 179.309 180.838 ↓ 712.4 2,227,824 1

Sort (cost=1,188.27..1,196.09 rows=3,127 width=25) (actual time=7.351..180.838 rows=2,227,824 loops=1)

  • Sort Key: l.season, l.fruit_type, l.rmt_variety_code, l.farm_group_code
  • Sort Method: quicksort Memory: 345kB
83. 1.044 1.529 ↓ 1.0 3,178 1

Bitmap Heap Scan on bin_tariff l (cost=116.65..1,006.74 rows=3,127 width=25) (actual time=0.555..1.529 rows=3,178 loops=1)

  • Recheck Cond: ((fee_type)::text = 'TIPPING'::text)
  • Heap Blocks: exact=374
84. 0.485 0.485 ↓ 1.0 3,178 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..115.87 rows=3,127 width=0) (actual time=0.485..0.485 rows=3,178 loops=1)

  • Index Cond: ((fee_type)::text = 'TIPPING'::text)
85. 175.747 177.209 ↓ 678.1 2,229,450 1

Sort (cost=1,206.07..1,214.29 rows=3,288 width=25) (actual time=6.626..177.209 rows=2,229,450 loops=1)

  • Sort Key: m.season, m.fruit_type, m.rmt_variety_code, m.farm_group_code
  • Sort Method: quicksort Memory: 350kB
86. 0.986 1.462 ↑ 1.0 3,250 1

Bitmap Heap Scan on bin_tariff m (cost=121.90..1,014.00 rows=3,288 width=25) (actual time=0.540..1.462 rows=3,250 loops=1)

  • Recheck Cond: ((fee_type)::text = 'TIPPING_REBIN'::text)
  • Heap Blocks: exact=353
87. 0.476 0.476 ↑ 1.0 3,250 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..121.08 rows=3,288 width=0) (actual time=0.476..0.476 rows=3,250 loops=1)

  • Index Cond: ((fee_type)::text = 'TIPPING_REBIN'::text)
88. 176.830 178.744 ↓ 541.1 2,230,579 1

Sort (cost=1,302.40..1,312.70 rows=4,122 width=25) (actual time=8.822..178.744 rows=2,230,579 loops=1)

  • Sort Key: p.season, p.fruit_type, p.rmt_variety_code, p.farm_group_code
  • Sort Method: quicksort Memory: 405kB
89. 1.341 1.914 ↑ 1.0 3,952 1

Bitmap Heap Scan on bin_tariff p (cost=152.36..1,054.89 rows=4,122 width=25) (actual time=0.641..1.914 rows=3,952 loops=1)

  • Recheck Cond: ((fee_type)::text = 'INTAKE'::text)
  • Heap Blocks: exact=391
90. 0.573 0.573 ↑ 1.0 3,952 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..151.33 rows=4,122 width=0) (actual time=0.573..0.573 rows=3,952 loops=1)

  • Index Cond: ((fee_type)::text = 'INTAKE'::text)
91. 191.297 192.958 ↓ 544.7 2,230,691 1

Sort (cost=1,300.03..1,310.27 rows=4,095 width=25) (actual time=7.713..192.958 rows=2,230,691 loops=1)

  • Sort Key: q.season, q.fruit_type, q.rmt_variety_code, q.farm_group_code
  • Sort Method: quicksort Memory: 414kB
92. 1.266 1.661 ↑ 1.0 4,060 1

Bitmap Heap Scan on bin_tariff q (cost=152.15..1,054.34 rows=4,095 width=25) (actual time=0.461..1.661 rows=4,060 loops=1)

  • Recheck Cond: ((fee_type)::text = 'RA_SUMMER_BINS'::text)
  • Heap Blocks: exact=420
93. 0.395 0.395 ↑ 1.0 4,060 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..151.13 rows=4,095 width=0) (actual time=0.395..0.395 rows=4,060 loops=1)

  • Index Cond: ((fee_type)::text = 'RA_SUMMER_BINS'::text)
94. 1,048.060 1,057.064 ↓ 696.2 16,217,111 1

Sort (cost=3,583.74..3,641.97 rows=23,293 width=29) (actual time=47.794..1,057.064 rows=16,217,111 loops=1)

  • Sort Key: r.season, r.fruit_type, r.rmt_variety_code, r.farm_group_code
  • Sort Method: quicksort Memory: 2603kB
95. 9.004 9.004 ↓ 1.0 23,479 1

Seq Scan on bin_tariff r (cost=0.00..1,894.11 rows=23,293 width=29) (actual time=0.017..9.004 rows=23,479 loops=1)

  • Filter: ((fee_type)::text = 'DRENCH'::text)
  • Rows Removed by Filter: 59970
96. 1,036.101 1,044.942 ↓ 645.8 17,189,977 1

Sort (cost=3,850.47..3,917.01 rows=26,617 width=29) (actual time=40.953..1,044.942 rows=17,189,977 loops=1)

  • Sort Key: n.season, n.fruit_type, n.rmt_variety_code, n.farm_group_code
  • Sort Method: quicksort Memory: 2861kB
97. 8.841 8.841 ↓ 1.0 26,789 1

Seq Scan on bin_tariff n (cost=0.00..1,894.11 rows=26,617 width=29) (actual time=0.043..8.841 rows=26,789 loops=1)

  • Filter: ((fee_type)::text = 'REBIN_DRENCH'::text)
  • Rows Removed by Filter: 56660
98. 169.081 170.592 ↓ 699.4 2,227,474 1

Sort (cost=1,197.23..1,205.20 rows=3,185 width=25) (actual time=6.247..170.592 rows=2,227,474 loops=1)

  • Sort Key: s.season, s.fruit_type, s.rmt_variety_code, s.farm_group_code
  • Sort Method: quicksort Memory: 340kB
99. 1.050 1.511 ↑ 1.0 3,117 1

Bitmap Heap Scan on bin_tariff s (cost=121.10..1,011.91 rows=3,185 width=25) (actual time=0.536..1.511 rows=3,117 loops=1)

  • Recheck Cond: ((fee_type)::text = 'RA_NORMAL_BINS'::text)
  • Heap Blocks: exact=346
100. 0.461 0.461 ↑ 1.0 3,117 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..120.31 rows=3,185 width=0) (actual time=0.461..0.461 rows=3,117 loops=1)

  • Index Cond: ((fee_type)::text = 'RA_NORMAL_BINS'::text)
101. 152.363 153.926 ↓ 544.7 2,230,691 1

Sort (cost=1,300.03..1,310.27 rows=4,095 width=25) (actual time=7.698..153.926 rows=2,230,691 loops=1)

  • Sort Key: t.season, t.fruit_type, t.rmt_variety_code, t.farm_group_code
  • Sort Method: quicksort Memory: 414kB
102. 1.212 1.563 ↑ 1.0 4,060 1

Bitmap Heap Scan on bin_tariff t (cost=152.15..1,054.34 rows=4,095 width=25) (actual time=0.422..1.563 rows=4,060 loops=1)

  • Recheck Cond: ((fee_type)::text = 'RA_SUMMER_BINS'::text)
  • Heap Blocks: exact=420
103. 0.351 0.351 ↑ 1.0 4,060 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..151.13 rows=4,095 width=0) (actual time=0.351..0.351 rows=4,060 loops=1)

  • Index Cond: ((fee_type)::text = 'RA_SUMMER_BINS'::text)
104. 176.124 176.968 ↓ 1,111.8 2,195,840 1

Sort (cost=1,059.52..1,064.46 rows=1,975 width=25) (actual time=4.361..176.968 rows=2,195,840 loops=1)

  • Sort Key: u.season, u.fruit_type, u.rmt_variety_code, u.farm_group_code
  • Sort Method: quicksort Memory: 195kB
105. 0.553 0.844 ↑ 1.1 1,876 1

Bitmap Heap Scan on bin_tariff u (cost=75.72..951.41 rows=1,975 width=25) (actual time=0.320..0.844 rows=1,876 loops=1)

  • Recheck Cond: ((fee_type)::text = 'CA_LONG_TERM'::text)
  • Heap Blocks: exact=207
106. 0.291 0.291 ↑ 1.1 1,876 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..75.23 rows=1,975 width=0) (actual time=0.291..0.291 rows=1,876 loops=1)

  • Index Cond: ((fee_type)::text = 'CA_LONG_TERM'::text)
107. 157.005 157.806 ↓ 1,216.5 2,195,840 1

Sort (cost=1,041.60..1,046.11 rows=1,805 width=25) (actual time=4.257..157.806 rows=2,195,840 loops=1)

  • Sort Key: v.season, v.fruit_type, v.rmt_variety_code, v.farm_group_code
  • Sort Method: quicksort Memory: 195kB
108. 0.533 0.801 ↓ 1.0 1,876 1

Bitmap Heap Scan on bin_tariff v (cost=70.41..943.97 rows=1,805 width=25) (actual time=0.297..0.801 rows=1,876 loops=1)

  • Recheck Cond: ((fee_type)::text = 'CA_NORMAL'::text)
  • Heap Blocks: exact=192
109. 0.268 0.268 ↓ 1.0 1,876 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..69.95 rows=1,805 width=0) (actual time=0.268..0.268 rows=1,876 loops=1)

  • Index Cond: ((fee_type)::text = 'CA_NORMAL'::text)
110. 155.876 156.690 ↓ 1,160.6 2,195,840 1

Sort (cost=1,048.71..1,053.44 rows=1,892 width=25) (actual time=3.970..156.690 rows=2,195,840 loops=1)

  • Sort Key: ad.season, ad.fruit_type, ad.rmt_variety_code, ad.farm_group_code
  • Sort Method: quicksort Memory: 195kB
111. 0.538 0.814 ↑ 1.0 1,876 1

Bitmap Heap Scan on bin_tariff ad (cost=71.08..945.73 rows=1,892 width=25) (actual time=0.307..0.814 rows=1,876 loops=1)

  • Recheck Cond: ((fee_type)::text = 'CA_SHORT'::text)
  • Heap Blocks: exact=206
112. 0.276 0.276 ↑ 1.0 1,876 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..70.61 rows=1,892 width=0) (actual time=0.276..0.276 rows=1,876 loops=1)

  • Index Cond: ((fee_type)::text = 'CA_SHORT'::text)
113. 152.414 153.182 ↓ 1,147.3 2,195,840 1

Sort (cost=1,050.51..1,055.30 rows=1,914 width=25) (actual time=4.200..153.182 rows=2,195,840 loops=1)

  • Sort Key: af.season, af.fruit_type, af.rmt_variety_code, af.farm_group_code
  • Sort Method: quicksort Memory: 195kB
114. 0.505 0.768 ↑ 1.0 1,876 1

Bitmap Heap Scan on bin_tariff af (cost=71.25..946.18 rows=1,914 width=25) (actual time=0.291..0.768 rows=1,876 loops=1)

  • Recheck Cond: ((fee_type)::text = 'CA_REBIN'::text)
  • Heap Blocks: exact=202
115. 0.263 0.263 ↑ 1.0 1,876 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..70.77 rows=1,914 width=0) (actual time=0.263..0.263 rows=1,876 loops=1)

  • Index Cond: ((fee_type)::text = 'CA_REBIN'::text)
116. 159.501 161.493 ↓ 558.8 2,230,691 1

Sort (cost=1,287.03..1,297.01 rows=3,992 width=25) (actual time=9.694..161.493 rows=2,230,691 loops=1)

  • Sort Key: ae.season, ae.fruit_type, ae.rmt_variety_code, ae.farm_group_code
  • Sort Method: quicksort Memory: 414kB
117. 1.382 1.992 ↓ 1.0 4,060 1

Bitmap Heap Scan on bin_tariff ae (cost=147.36..1,048.26 rows=3,992 width=25) (actual time=0.702..1.992 rows=4,060 loops=1)

  • Recheck Cond: ((fee_type)::text = 'SMRTFRSH'::text)
  • Heap Blocks: exact=435
118. 0.610 0.610 ↓ 1.0 4,060 1

Bitmap Index Scan on bin_tariff_idx (cost=0.00..146.36 rows=3,992 width=0) (actual time=0.609..0.610 rows=4,060 loops=1)

  • Index Cond: ((fee_type)::text = 'SMRTFRSH'::text)
119. 1,131.616 1,899.545 ↓ 1.4 3,608,035 1

Hash (cost=103,386.26..103,386.26 rows=2,671,589 width=9) (actual time=1,899.545..1,899.545 rows=3,608,035 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 180333kB
120. 767.929 767.929 ↓ 1.4 3,608,035 1

Index Only Scan using f_bin_as_bin_number_key on f_bin_as w (cost=0.43..103,386.26 rows=2,671,589 width=9) (actual time=0.109..767.929 rows=3,608,035 loops=1)

  • Heap Fetches: 10549
Planning time : 109.091 ms
Execution time : 75,599.201 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint f_bin_as_fk 8.806 ms 8628 0.001 ms
for constraint f_bin_as_fk1 81.167 ms 8628 0.009 ms
for constraint f_bin_as_fk2 77.240 ms 8628 0.009 ms
for constraint f_bin_as_fk3 78.317 ms 8628 0.009 ms
for constraint f_bin_as_fk4 34.242 ms 8628 0.004 ms