explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m05s

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 3.914 ↑ 2.0 4 1

Nested Loop Left Join (cost=211.00..224.85 rows=8 width=80) (actual time=3.904..3.914 rows=4 loops=1)

  • Output: ci.contract_item_id, COALESCE(base.quantity, '0'::numeric), COALESCE(obj.quantity, '0'::numeric)
  • Join Filter: (obj.contract_item_id = ci.contract_item_id)
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=916
2.          

CTE obj

3. 0.014 1.097 ↓ 3.0 3 1

GroupAggregate (cost=84.63..84.69 rows=1 width=48) (actual time=1.092..1.097 rows=3 loops=1)

  • Output: contract_product_materialized.contract_item_id, sum((((fi.quantity * (tuomc.rate / suomc.rate)) / ((upper(fi.date_range) - lower(fi.date_range)))::numeric) * ((upper((fi.date_range * crv.date_range)) - lower((fi.date_range * crv.date_range))))::numeric))
  • Group Key: contract_product_materialized.contract_item_id
  • Buffers: shared hit=340
4. 0.010 1.083 ↓ 3.0 3 1

Sort (cost=84.63..84.63 rows=1 width=59) (actual time=1.083..1.083 rows=3 loops=1)

  • Output: contract_product_materialized.contract_item_id, fi.quantity, tuomc.rate, suomc.rate, fi.date_range, crv.date_range
  • Sort Key: contract_product_materialized.contract_item_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=340
5. 0.003 1.073 ↓ 3.0 3 1

Nested Loop (cost=71.49..84.62 rows=1 width=59) (actual time=1.042..1.073 rows=3 loops=1)

  • Output: contract_product_materialized.contract_item_id, fi.quantity, tuomc.rate, suomc.rate, fi.date_range, crv.date_range
  • Inner Unique: true
  • Join Filter: ((fi.product_id = tuomc.product_id) AND (contract_product_materialized.uom_type_id = tuomc.uom_type_id))
  • Buffers: shared hit=340
6. 0.003 1.064 ↓ 3.0 3 1

Nested Loop (cost=71.21..84.21 rows=1 width=134) (actual time=1.036..1.064 rows=3 loops=1)

  • Output: crv.date_range, contract_product_materialized.contract_item_id, contract_product_materialized.product_id, contract_product_materialized.uom_type_id, fi.quantity, fi.date_range, fi.product_id, pro.product_id, suomc.rate, suomc.product_id
  • Inner Unique: true
  • Buffers: shared hit=331
7. 0.004 1.049 ↓ 3.0 3 1

Nested Loop (cost=70.93..83.86 rows=1 width=129) (actual time=1.026..1.049 rows=3 loops=1)

  • Output: crv.date_range, contract_product_materialized.contract_item_id, contract_product_materialized.product_id, contract_product_materialized.uom_type_id, fi.quantity, fi.date_range, fi.product_id, fi.uom_type_id, pro.product_id
  • Inner Unique: true
  • Buffers: shared hit=321
8. 0.013 1.036 ↓ 3.0 3 1

Nested Loop (cost=70.65..83.56 rows=1 width=113) (actual time=1.017..1.036 rows=3 loops=1)

  • Output: crv.date_range, contract_product_materialized.contract_item_id, contract_product_materialized.product_id, contract_product_materialized.uom_type_id, fi.quantity, fi.date_range, fi.product_id, fi.uom_type_id
  • Join Filter: (fi.company_id = cmp.company_id)
  • Rows Removed by Join Filter: 37
  • Buffers: shared hit=314
9. 0.027 0.923 ↓ 20.0 20 1

Merge Join (cost=8.09..19.31 rows=1 width=145) (actual time=0.910..0.923 rows=20 loops=1)

  • Output: cav.contract_amend_version_id, crv.date_range, contract_product_materialized.contract_item_id, contract_product_materialized.product_id, contract_product_materialized.uom_type_id, fi.quantity, fi.date_range, fi.company_id, fi.product_id, fi.uom_type_id
  • Inner Unique: true
  • Merge Cond: (fi.product_id = contract_product_materialized.product_id)
  • Buffers: shared hit=292
10. 0.115 0.884 ↓ 15.2 182 1

Sort (cost=7.67..7.70 rows=12 width=97) (actual time=0.875..0.884 rows=182 loops=1)

  • Output: cav.contract_amend_version_id, crv.date_range, fi.quantity, fi.date_range, fi.company_id, fi.product_id, fi.uom_type_id
  • Sort Key: fi.product_id
  • Sort Method: quicksort Memory: 58kB
  • Buffers: shared hit=288
11. 0.074 0.769 ↓ 20.0 240 1

Nested Loop (cost=0.72..7.45 rows=12 width=97) (actual time=0.063..0.769 rows=240 loops=1)

  • Output: cav.contract_amend_version_id, crv.date_range, fi.quantity, fi.date_range, fi.company_id, fi.product_id, fi.uom_type_id
  • Buffers: shared hit=288
12. 0.003 0.021 ↑ 1.0 1 1

Nested Loop (cost=0.57..6.61 rows=1 width=30) (actual time=0.021..0.021 rows=1 loops=1)

  • Output: cav.contract_amend_version_id, crv.date_range
  • Inner Unique: true
  • Buffers: shared hit=6
13. 0.010 0.010 ↑ 1.0 1 1

Index Scan using contract_amend_version_pkey on client_lactalis_deli.contract_amend_version cav (cost=0.29..3.31 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)

  • Output: cav.contract_amend_version_id, cav.contract_renew_version_id, cav.contract_state, cav.contract_amend_version_parent_id, cav.contract_name, cav.contact_name, cav.contact_email, cav.contact_phone, cav.create_timestamp, cav.amend_version
  • Index Cond: (cav.contract_amend_version_id = 'f26a3d79-ba45-11ea-8740-53a42f606cbb'::uuid)
  • Buffers: shared hit=3
14. 0.008 0.008 ↑ 1.0 1 1

Index Scan using contract_renew_version_pkey on client_lactalis_deli.contract_renew_version crv (cost=0.29..3.31 rows=1 width=30) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: crv.contract_renew_version_id, crv.contract_family_id, crv.date_range, crv.create_timestamp, crv.dead, crv.renew_version
  • Index Cond: (crv.contract_renew_version_id = cav.contract_renew_version_id)
  • Buffers: shared hit=3
15. 0.674 0.674 ↓ 9.6 240 1

Index Scan using forecast_objective_exclusion on client_lactalis_deli.forecast_objective fi (cost=0.14..0.59 rows=25 width=67) (actual time=0.039..0.674 rows=240 loops=1)

  • Output: fi.forecast_objective_id, fi.product_id, fi.company_id, fi.date_range, fi.uom_type_id, fi.quantity
  • Index Cond: (fi.date_range && crv.date_range)
  • Buffers: shared hit=282
16. 0.012 0.012 ↑ 2.0 4 1

Index Scan using contract_product_materialized_pkey on client_lactalis_deli.contract_product_materialized (cost=0.42..11.55 rows=8 width=64) (actual time=0.011..0.012 rows=4 loops=1)

  • Output: contract_product_materialized.contract_item_id, contract_product_materialized.contract_id, contract_product_materialized.product_id, contract_product_materialized.estimated_quantity, contract_product_materialized.uom_type_id, contract_product_materialized.rebate_deviated_value, contract_product_materialized.rebate_deviated_type, contract_product_materialized.inactive_override, contract_product_materialized.rebate_direct_value, contract_product_materialized.rebate_direct_type, contract_product_materialized.rebate_direct_decimal_model, contract_product_materialized.rebate_deviated_decimal_model, contract_product_materialized.won, contract_product_materialized.rebate_growth_type, contract_product_materialized.rebate_growth_decimal_model, contract_product_materialized.lift_quantity
  • Index Cond: (contract_product_materialized.contract_id = 'f26a3d79-ba45-11ea-8740-53a42f606cbb'::uuid)
  • Buffers: shared hit=4
17. 0.008 0.100 ↑ 26.0 2 20

HashAggregate (cost=62.57..63.09 rows=52 width=36) (actual time=0.005..0.005 rows=2 loops=20)

  • Output: cav_1.contract_amend_version_id, cmp.company_id, ct.internal_company_type
  • Group Key: cav_1.contract_amend_version_id, cmp.company_id, ct.internal_company_type
  • Buffers: shared hit=22
18. 0.009 0.092 ↑ 26.0 2 1

Hash Join (cost=16.79..62.18 rows=52 width=36) (actual time=0.085..0.092 rows=2 loops=1)

  • Output: cav_1.contract_amend_version_id, cmp.company_id, ct.internal_company_type
  • Inner Unique: true
  • Hash Cond: (cmp.company_type_id = ct.company_type_id)
  • Buffers: shared hit=22
19. 0.003 0.071 ↑ 26.0 2 1

Nested Loop (cost=15.66..60.80 rows=52 width=48) (actual time=0.064..0.071 rows=2 loops=1)

  • Output: cav_1.contract_amend_version_id, cmp.company_id, cmp.company_type_id
  • Inner Unique: true
  • Buffers: shared hit=21
20. 0.003 0.056 ↑ 32.5 2 1

Nested Loop (cost=15.38..41.37 rows=65 width=32) (actual time=0.054..0.056 rows=2 loops=1)

  • Output: cav_1.contract_amend_version_id, cagg.agg_company_id
  • Buffers: shared hit=15
21. 0.002 0.041 ↑ 7.0 1 1

Nested Loop Left Join (cost=14.97..33.43 rows=7 width=48) (actual time=0.041..0.041 rows=1 loops=1)

  • Output: c.contractee_company_id, cav_1.contract_amend_version_id, cg_1.company_id
  • Buffers: shared hit=11
22. 0.002 0.038 ↑ 1.0 1 1

Nested Loop Left Join (cost=14.82..21.43 rows=1 width=48) (actual time=0.038..0.038 rows=1 loops=1)

  • Output: c.contractee_company_id, cav_1.contract_amend_version_id, groupings_list.grouping_id
  • Join Filter: (groupings_list.original_grouping_id = c.contractee_grouping_id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=11
23. 0.002 0.018 ↑ 1.0 1 1

Nested Loop (cost=0.86..7.01 rows=1 width=48) (actual time=0.018..0.018 rows=1 loops=1)

  • Output: c.contractee_grouping_id, c.contractee_company_id, cav_1.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=9
24. 0.001 0.008 ↑ 1.0 1 1

Nested Loop (cost=0.57..6.61 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: crv_1.contract_family_id, cav_1.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=6
25. 0.004 0.004 ↑ 1.0 1 1

Index Scan using contract_amend_version_pkey on client_lactalis_deli.contract_amend_version cav_1 (cost=0.29..3.31 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1)

  • Output: cav_1.contract_amend_version_id, cav_1.contract_renew_version_id, cav_1.contract_state, cav_1.contract_amend_version_parent_id, cav_1.contract_name, cav_1.contact_name, cav_1.contact_email, cav_1.contact_phone, cav_1.create_timestamp, cav_1.amend_version
  • Index Cond: (cav_1.contract_amend_version_id = 'f26a3d79-ba45-11ea-8740-53a42f606cbb'::uuid)
  • Buffers: shared hit=3
26. 0.003 0.003 ↑ 1.0 1 1

Index Scan using contract_renew_version_pkey on client_lactalis_deli.contract_renew_version crv_1 (cost=0.29..3.31 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: crv_1.contract_renew_version_id, crv_1.contract_family_id, crv_1.date_range, crv_1.create_timestamp, crv_1.dead, crv_1.renew_version
  • Index Cond: (crv_1.contract_renew_version_id = cav_1.contract_renew_version_id)
  • Buffers: shared hit=3
27. 0.008 0.008 ↑ 1.0 1 1

Index Scan using contract_family_pkey on client_lactalis_deli.contract_family c (cost=0.29..0.40 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: c.contract_family_id, c.client_id, c.contractee_company_id, c.contractee_grouping_id, c.creator_id, c.contract_sequence, c.cost_basis_type, c.contract_type, c.contract_title, c.contract_template_id, c.payment_calendar_id, c.catch_all, c.accrual_quantity_source, c.import_contract_number, c.accrual_lumpsum_spread
  • Index Cond: (c.contract_family_id = crv_1.contract_family_id)
  • Buffers: shared hit=3
28. 0.018 0.018 ↑ 11.0 1 1

CTE Scan on groupings_list (cost=13.95..14.17 rows=11 width=52) (actual time=0.009..0.018 rows=1 loops=1)

  • Output: groupings_list.original_grouping_id, NULL::uuid, groupings_list.grouping_id, NULL::integer
  • Buffers: shared hit=2
29.          

CTE groupings_list

30. 0.002 0.017 ↑ 11.0 1 1

Recursive Union (cost=0.00..13.95 rows=11 width=52) (actual time=0.007..0.017 rows=1 loops=1)

  • Buffers: shared hit=2
31. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on client_lactalis_deli.grouping_company pg (cost=0.00..1.01 rows=1 width=52) (actual time=0.006..0.006 rows=1 loops=1)

  • Output: pg.grouping_id, pg.parent_grouping_id, pg.grouping_id, 0
  • Buffers: shared hit=1
32. 0.004 0.009 ↓ 0.0 0 1

Hash Join (cost=1.02..1.27 rows=1 width=52) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: gl.original_grouping_id, cg.parent_grouping_id, cg.grouping_id, (gl.level + 1)
  • Hash Cond: (gl.grouping_id = cg.parent_grouping_id)
  • Buffers: shared hit=1
33. 0.002 0.002 ↑ 10.0 1 1

WorkTable Scan on groupings_list gl (cost=0.00..0.20 rows=10 width=36) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: gl.original_grouping_id, gl.parent_grouping_id, gl.grouping_id, gl.level
34. 0.001 0.003 ↓ 0.0 0 1

Hash (cost=1.01..1.01 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: cg.parent_grouping_id, cg.grouping_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
35. 0.002 0.002 ↑ 1.0 1 1

Seq Scan on client_lactalis_deli.grouping_company cg (cost=0.00..1.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: cg.parent_grouping_id, cg.grouping_id
  • Filter: cg.active_ind
  • Buffers: shared hit=1
36. 0.001 0.001 ↓ 0.0 0 1

Index Only Scan using company_grouping_pkey on client_lactalis_deli.company_grouping cg_1 (cost=0.15..11.92 rows=7 width=32) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: cg_1.company_id, cg_1.grouping_id
  • Index Cond: (cg_1.grouping_id = groupings_list.grouping_id)
  • Heap Fetches: 0
37. 0.012 0.012 ↑ 4.5 2 1

Index Only Scan using company_agg_pkey on client_lactalis_deli.company_agg cagg (cost=0.41..1.04 rows=9 width=32) (actual time=0.011..0.012 rows=2 loops=1)

  • Output: cagg.company_id, cagg.agg_company_id
  • Index Cond: (cagg.company_id = COALESCE(c.contractee_company_id, cg_1.company_id))
  • Heap Fetches: 0
  • Buffers: shared hit=4
38. 0.012 0.012 ↑ 1.0 1 2

Index Scan using company_pkey on client_lactalis_deli.company cmp (cost=0.28..0.30 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=2)

  • Output: cmp.company_id, cmp.client_id, cmp.company_name, cmp.company_number, cmp.company_type_id, cmp.active_ind, cmp.address_1, cmp.address_2, cmp.address_3, cmp.address_4, cmp.city, cmp.region, cmp.postal_code, cmp.country, cmp.company_agg_id, cmp.deduction_interval, cmp.alt_address_active, cmp.alt_address_1, cmp.alt_address_2, cmp.alt_address_3, cmp.alt_address_4, cmp.alt_city, cmp.alt_region, cmp.alt_postal_code, cmp.alt_country, cmp.atlas_company_id, cmp.email, cmp.tax_type_id, cmp.redistributor_ind, cmp.operator_request, cmp.early_payment_discount, cmp.vip, cmp.deduction_contact_email, cmp.is_unipro, cmp.is_golbon, cmp.is_ima, cmp.cross_ship_company_id, cmp.tpo_ind, cmp.auto_submit_eclaim, cmp.auto_approve_eclaim
  • Index Cond: (cmp.company_id = cagg.agg_company_id)
  • Buffers: shared hit=6
39. 0.002 0.012 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=20) (actual time=0.012..0.012 rows=6 loops=1)

  • Output: ct.internal_company_type, ct.company_type_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
40. 0.010 0.010 ↑ 1.0 6 1

Seq Scan on client_lactalis_deli.company_type ct (cost=0.00..1.06 rows=6 width=20) (actual time=0.009..0.010 rows=6 loops=1)

  • Output: ct.internal_company_type, ct.company_type_id
  • Buffers: shared hit=1
41. 0.009 0.009 ↑ 1.0 1 3

Index Only Scan using product_pkey on client_lactalis_deli.product pro (cost=0.28..0.30 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3)

  • Output: pro.product_id
  • Index Cond: (pro.product_id = fi.product_id)
  • Heap Fetches: 0
  • Buffers: shared hit=7
42. 0.012 0.012 ↑ 1.0 1 3

Index Scan using product_uom_conversion_pkey on client_lactalis_deli.product_uom_conversion suomc (cost=0.28..0.35 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=3)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
  • Index Cond: ((suomc.product_id = fi.product_id) AND (suomc.uom_type_id = fi.uom_type_id))
  • Buffers: shared hit=10
43. 0.006 0.006 ↑ 3.0 1 3

Index Scan using product_uom_conversion_pkey on client_lactalis_deli.product_uom_conversion tuomc (cost=0.28..0.36 rows=3 width=37) (actual time=0.002..0.002 rows=1 loops=3)

  • Output: tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Index Cond: (tuomc.product_id = suomc.product_id)
  • Buffers: shared hit=9
44.          

CTE base

45. 0.050 2.769 ↑ 2.0 4 1

GroupAggregate (cost=124.35..125.64 rows=8 width=48) (actual time=2.751..2.769 rows=4 loops=1)

  • Output: contract_product_materialized_1.contract_item_id, sum((((fi_1.quantity * (tuomc_1.rate / suomc_1.rate)) / ((upper(fi_1.date_range) - lower(fi_1.date_range)))::numeric) * ((upper((fi_1.date_range * crv_2.date_range)) - lower((fi_1.date_range * crv_2.date_range))))::numeric))
  • Group Key: contract_product_materialized_1.contract_item_id
  • Buffers: shared hit=570
46. 0.015 2.719 ↑ 2.3 12 1

Sort (cost=124.35..124.42 rows=28 width=59) (actual time=2.718..2.719 rows=12 loops=1)

  • Output: contract_product_materialized_1.contract_item_id, fi_1.quantity, tuomc_1.rate, suomc_1.rate, fi_1.date_range, crv_2.date_range
  • Sort Key: contract_product_materialized_1.contract_item_id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=570
47. 0.004 2.704 ↑ 2.3 12 1

Nested Loop (cost=66.37..123.67 rows=28 width=59) (actual time=0.554..2.704 rows=12 loops=1)

  • Output: contract_product_materialized_1.contract_item_id, fi_1.quantity, tuomc_1.rate, suomc_1.rate, fi_1.date_range, crv_2.date_range
  • Inner Unique: true
  • Buffers: shared hit=570
48. 0.036 2.676 ↑ 2.1 12 1

Hash Join (cost=66.09..116.09 rows=25 width=134) (actual time=0.549..2.676 rows=12 loops=1)

  • Output: crv_2.date_range, contract_product_materialized_1.contract_item_id, contract_product_materialized_1.product_id, fi_1.quantity, fi_1.date_range, fi_1.product_id, fi_1.uom_type_id, pro_1.product_id, tuomc_1.rate, tuomc_1.product_id
  • Hash Cond: (fi_1.company_id = cmp_1.company_id)
  • Buffers: shared hit=531
49. 0.095 2.549 ↓ 4.0 290 1

Nested Loop (cost=1.83..51.32 rows=72 width=166) (actual time=0.137..2.549 rows=290 loops=1)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range, contract_product_materialized_1.contract_item_id, contract_product_materialized_1.product_id, fi_1.quantity, fi_1.date_range, fi_1.company_id, fi_1.product_id, fi_1.uom_type_id, pro_1.product_id, tuomc_1.rate, tuomc_1.product_id
  • Join Filter: (contract_product_materialized_1.product_id = fi_1.product_id)
  • Buffers: shared hit=509
50. 0.006 0.110 ↑ 2.2 4 1

Nested Loop (cost=1.55..33.32 rows=9 width=99) (actual time=0.080..0.110 rows=4 loops=1)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range, contract_product_materialized_1.contract_item_id, contract_product_materialized_1.product_id, pro_1.product_id, tuomc_1.rate, tuomc_1.product_id
  • Inner Unique: true
  • Join Filter: ((contract_product_materialized_1.product_id = tuomc_1.product_id) AND (contract_product_materialized_1.uom_type_id = tuomc_1.uom_type_id))
  • Buffers: shared hit=31
51. 0.003 0.088 ↑ 2.0 4 1

Nested Loop (cost=1.27..29.62 rows=8 width=94) (actual time=0.070..0.088 rows=4 loops=1)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range, contract_product_materialized_1.contract_item_id, contract_product_materialized_1.product_id, contract_product_materialized_1.uom_type_id, pro_1.product_id
  • Inner Unique: true
  • Buffers: shared hit=19
52. 0.001 0.065 ↑ 2.0 4 1

Nested Loop (cost=0.99..18.26 rows=8 width=78) (actual time=0.057..0.065 rows=4 loops=1)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range, contract_product_materialized_1.contract_item_id, contract_product_materialized_1.product_id, contract_product_materialized_1.uom_type_id
  • Buffers: shared hit=10
53. 0.016 0.016 ↑ 2.0 4 1

Index Scan using contract_product_materialized_pkey on client_lactalis_deli.contract_product_materialized contract_product_materialized_1 (cost=0.42..11.55 rows=8 width=64) (actual time=0.013..0.016 rows=4 loops=1)

  • Output: contract_product_materialized_1.contract_item_id, contract_product_materialized_1.contract_id, contract_product_materialized_1.product_id, contract_product_materialized_1.estimated_quantity, contract_product_materialized_1.uom_type_id, contract_product_materialized_1.rebate_deviated_value, contract_product_materialized_1.rebate_deviated_type, contract_product_materialized_1.inactive_override, contract_product_materialized_1.rebate_direct_value, contract_product_materialized_1.rebate_direct_type, contract_product_materialized_1.rebate_direct_decimal_model, contract_product_materialized_1.rebate_deviated_decimal_model, contract_product_materialized_1.won, contract_product_materialized_1.rebate_growth_type, contract_product_materialized_1.rebate_growth_decimal_model, contract_product_materialized_1.lift_quantity
  • Index Cond: (contract_product_materialized_1.contract_id = 'f26a3d79-ba45-11ea-8740-53a42f606cbb'::uuid)
  • Buffers: shared hit=4
54. 0.005 0.048 ↑ 1.0 1 4

Materialize (cost=0.57..6.62 rows=1 width=30) (actual time=0.011..0.012 rows=1 loops=4)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range
  • Buffers: shared hit=6
55. 0.001 0.043 ↑ 1.0 1 1

Nested Loop (cost=0.57..6.61 rows=1 width=30) (actual time=0.042..0.043 rows=1 loops=1)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range
  • Inner Unique: true
  • Buffers: shared hit=6
56. 0.010 0.010 ↑ 1.0 1 1

Index Scan using contract_amend_version_pkey on client_lactalis_deli.contract_amend_version cav_2 (cost=0.29..3.31 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=1)

  • Output: cav_2.contract_amend_version_id, cav_2.contract_renew_version_id, cav_2.contract_state, cav_2.contract_amend_version_parent_id, cav_2.contract_name, cav_2.contact_name, cav_2.contact_email, cav_2.contact_phone, cav_2.create_timestamp, cav_2.amend_version
  • Index Cond: (cav_2.contract_amend_version_id = 'f26a3d79-ba45-11ea-8740-53a42f606cbb'::uuid)
  • Buffers: shared hit=3
57. 0.032 0.032 ↑ 1.0 1 1

Index Scan using contract_renew_version_pkey on client_lactalis_deli.contract_renew_version crv_2 (cost=0.29..3.31 rows=1 width=30) (actual time=0.032..0.032 rows=1 loops=1)

  • Output: crv_2.contract_renew_version_id, crv_2.contract_family_id, crv_2.date_range, crv_2.create_timestamp, crv_2.dead, crv_2.renew_version
  • Index Cond: (crv_2.contract_renew_version_id = cav_2.contract_renew_version_id)
  • Buffers: shared hit=3
58. 0.020 0.020 ↑ 1.0 1 4

Index Only Scan using product_pkey on client_lactalis_deli.product pro_1 (cost=0.28..1.42 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=4)

  • Output: pro_1.product_id
  • Index Cond: (pro_1.product_id = contract_product_materialized_1.product_id)
  • Heap Fetches: 0
  • Buffers: shared hit=9
59. 0.016 0.016 ↑ 3.0 1 4

Index Scan using product_uom_conversion_pkey on client_lactalis_deli.product_uom_conversion tuomc_1 (cost=0.28..0.42 rows=3 width=37) (actual time=0.004..0.004 rows=1 loops=4)

  • Output: tuomc_1.product_id, tuomc_1.uom_type_id, tuomc_1.rate
  • Index Cond: (tuomc_1.product_id = pro_1.product_id)
  • Buffers: shared hit=12
60. 2.344 2.344 ↓ 12.0 72 4

Index Scan using forecast_base_exclusion on client_lactalis_deli.forecast_base fi_1 (cost=0.28..1.92 rows=6 width=67) (actual time=0.042..0.586 rows=72 loops=4)

  • Output: fi_1.forecast_base_id, fi_1.product_id, fi_1.company_id, fi_1.date_range, fi_1.uom_type_id, fi_1.quantity
  • Index Cond: ((fi_1.product_id = tuomc_1.product_id) AND (fi_1.date_range && crv_2.date_range))
  • Buffers: shared hit=478
61. 0.002 0.091 ↑ 26.0 2 1

Hash (cost=63.61..63.61 rows=52 width=32) (actual time=0.091..0.091 rows=2 loops=1)

  • Output: cmp_1.contract_id, cmp_1.company_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=22
62. 0.001 0.089 ↑ 26.0 2 1

Subquery Scan on cmp_1 (cost=62.57..63.61 rows=52 width=32) (actual time=0.088..0.089 rows=2 loops=1)

  • Output: cmp_1.contract_id, cmp_1.company_id
  • Buffers: shared hit=22
63. 0.004 0.088 ↑ 26.0 2 1

HashAggregate (cost=62.57..63.09 rows=52 width=36) (actual time=0.088..0.088 rows=2 loops=1)

  • Output: cav_3.contract_amend_version_id, cmp_2.company_id, ct_1.internal_company_type
  • Group Key: cav_3.contract_amend_version_id, cmp_2.company_id, ct_1.internal_company_type
  • Buffers: shared hit=22
64. 0.004 0.084 ↑ 26.0 2 1

Hash Join (cost=16.79..62.18 rows=52 width=36) (actual time=0.077..0.084 rows=2 loops=1)

  • Output: cav_3.contract_amend_version_id, cmp_2.company_id, ct_1.internal_company_type
  • Inner Unique: true
  • Hash Cond: (cmp_2.company_type_id = ct_1.company_type_id)
  • Buffers: shared hit=22
65. 0.003 0.068 ↑ 26.0 2 1

Nested Loop (cost=15.66..60.80 rows=52 width=48) (actual time=0.060..0.068 rows=2 loops=1)

  • Output: cav_3.contract_amend_version_id, cmp_2.company_id, cmp_2.company_type_id
  • Inner Unique: true
  • Buffers: shared hit=21
66. 0.001 0.051 ↑ 32.5 2 1

Nested Loop (cost=15.38..41.37 rows=65 width=32) (actual time=0.049..0.051 rows=2 loops=1)

  • Output: cav_3.contract_amend_version_id, cagg_1.agg_company_id
  • Buffers: shared hit=15
67. 0.001 0.037 ↑ 7.0 1 1

Nested Loop Left Join (cost=14.97..33.43 rows=7 width=48) (actual time=0.036..0.037 rows=1 loops=1)

  • Output: c_1.contractee_company_id, cav_3.contract_amend_version_id, cg_3.company_id
  • Buffers: shared hit=11
68. 0.001 0.034 ↑ 1.0 1 1

Nested Loop Left Join (cost=14.82..21.43 rows=1 width=48) (actual time=0.033..0.034 rows=1 loops=1)

  • Output: c_1.contractee_company_id, cav_3.contract_amend_version_id, groupings_list_1.grouping_id
  • Join Filter: (groupings_list_1.original_grouping_id = c_1.contractee_grouping_id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=11
69. 0.002 0.017 ↑ 1.0 1 1

Nested Loop (cost=0.86..7.01 rows=1 width=48) (actual time=0.016..0.017 rows=1 loops=1)

  • Output: c_1.contractee_grouping_id, c_1.contractee_company_id, cav_3.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=9
70. 0.001 0.007 ↑ 1.0 1 1

Nested Loop (cost=0.57..6.61 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=1)

  • Output: crv_3.contract_family_id, cav_3.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=6
71. 0.003 0.003 ↑ 1.0 1 1

Index Scan using contract_amend_version_pkey on client_lactalis_deli.contract_amend_version cav_3 (cost=0.29..3.31 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: cav_3.contract_amend_version_id, cav_3.contract_renew_version_id, cav_3.contract_state, cav_3.contract_amend_version_parent_id, cav_3.contract_name, cav_3.contact_name, cav_3.contact_email, cav_3.contact_phone, cav_3.create_timestamp, cav_3.amend_version
  • Index Cond: (cav_3.contract_amend_version_id = 'f26a3d79-ba45-11ea-8740-53a42f606cbb'::uuid)
  • Buffers: shared hit=3
72. 0.003 0.003 ↑ 1.0 1 1

Index Scan using contract_renew_version_pkey on client_lactalis_deli.contract_renew_version crv_3 (cost=0.29..3.31 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: crv_3.contract_renew_version_id, crv_3.contract_family_id, crv_3.date_range, crv_3.create_timestamp, crv_3.dead, crv_3.renew_version
  • Index Cond: (crv_3.contract_renew_version_id = cav_3.contract_renew_version_id)
  • Buffers: shared hit=3
73. 0.008 0.008 ↑ 1.0 1 1

Index Scan using contract_family_pkey on client_lactalis_deli.contract_family c_1 (cost=0.29..0.40 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: c_1.contract_family_id, c_1.client_id, c_1.contractee_company_id, c_1.contractee_grouping_id, c_1.creator_id, c_1.contract_sequence, c_1.cost_basis_type, c_1.contract_type, c_1.contract_title, c_1.contract_template_id, c_1.payment_calendar_id, c_1.catch_all, c_1.accrual_quantity_source, c_1.import_contract_number, c_1.accrual_lumpsum_spread
  • Index Cond: (c_1.contract_family_id = crv_3.contract_family_id)
  • Buffers: shared hit=3
74. 0.016 0.016 ↑ 11.0 1 1

CTE Scan on groupings_list groupings_list_1 (cost=13.95..14.17 rows=11 width=52) (actual time=0.008..0.016 rows=1 loops=1)

  • Output: groupings_list_1.original_grouping_id, NULL::uuid, groupings_list_1.grouping_id, NULL::integer
  • Buffers: shared hit=2
75.          

CTE groupings_list

76. 0.001 0.015 ↑ 11.0 1 1

Recursive Union (cost=0.00..13.95 rows=11 width=52) (actual time=0.007..0.015 rows=1 loops=1)

  • Buffers: shared hit=2
77. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on client_lactalis_deli.grouping_company pg_1 (cost=0.00..1.01 rows=1 width=52) (actual time=0.006..0.006 rows=1 loops=1)

  • Output: pg_1.grouping_id, pg_1.parent_grouping_id, pg_1.grouping_id, 0
  • Buffers: shared hit=1
78. 0.005 0.008 ↓ 0.0 0 1

Hash Join (cost=1.02..1.27 rows=1 width=52) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: gl_1.original_grouping_id, cg_2.parent_grouping_id, cg_2.grouping_id, (gl_1.level + 1)
  • Hash Cond: (gl_1.grouping_id = cg_2.parent_grouping_id)
  • Buffers: shared hit=1
79. 0.000 0.000 ↑ 10.0 1 1

WorkTable Scan on groupings_list gl_1 (cost=0.00..0.20 rows=10 width=36) (actual time=0.000..0.000 rows=1 loops=1)

  • Output: gl_1.original_grouping_id, gl_1.parent_grouping_id, gl_1.grouping_id, gl_1.level
80. 0.000 0.003 ↓ 0.0 0 1

Hash (cost=1.01..1.01 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: cg_2.parent_grouping_id, cg_2.grouping_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
81. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on client_lactalis_deli.grouping_company cg_2 (cost=0.00..1.01 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: cg_2.parent_grouping_id, cg_2.grouping_id
  • Filter: cg_2.active_ind
  • Buffers: shared hit=1
82. 0.002 0.002 ↓ 0.0 0 1

Index Only Scan using company_grouping_pkey on client_lactalis_deli.company_grouping cg_3 (cost=0.15..11.92 rows=7 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: cg_3.company_id, cg_3.grouping_id
  • Index Cond: (cg_3.grouping_id = groupings_list_1.grouping_id)
  • Heap Fetches: 0
83. 0.013 0.013 ↑ 4.5 2 1

Index Only Scan using company_agg_pkey on client_lactalis_deli.company_agg cagg_1 (cost=0.41..1.04 rows=9 width=32) (actual time=0.012..0.013 rows=2 loops=1)

  • Output: cagg_1.company_id, cagg_1.agg_company_id
  • Index Cond: (cagg_1.company_id = COALESCE(c_1.contractee_company_id, cg_3.company_id))
  • Heap Fetches: 0
  • Buffers: shared hit=4
84. 0.014 0.014 ↑ 1.0 1 2

Index Scan using company_pkey on client_lactalis_deli.company cmp_2 (cost=0.28..0.30 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=2)

  • Output: cmp_2.company_id, cmp_2.client_id, cmp_2.company_name, cmp_2.company_number, cmp_2.company_type_id, cmp_2.active_ind, cmp_2.address_1, cmp_2.address_2, cmp_2.address_3, cmp_2.address_4, cmp_2.city, cmp_2.region, cmp_2.postal_code, cmp_2.country, cmp_2.company_agg_id, cmp_2.deduction_interval, cmp_2.alt_address_active, cmp_2.alt_address_1, cmp_2.alt_address_2, cmp_2.alt_address_3, cmp_2.alt_address_4, cmp_2.alt_city, cmp_2.alt_region, cmp_2.alt_postal_code, cmp_2.alt_country, cmp_2.atlas_company_id, cmp_2.email, cmp_2.tax_type_id, cmp_2.redistributor_ind, cmp_2.operator_request, cmp_2.early_payment_discount, cmp_2.vip, cmp_2.deduction_contact_email, cmp_2.is_unipro, cmp_2.is_golbon, cmp_2.is_ima, cmp_2.cross_ship_company_id, cmp_2.tpo_ind, cmp_2.auto_submit_eclaim, cmp_2.auto_approve_eclaim
  • Index Cond: (cmp_2.company_id = cagg_1.agg_company_id)
  • Buffers: shared hit=6
85. 0.002 0.012 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=20) (actual time=0.012..0.012 rows=6 loops=1)

  • Output: ct_1.internal_company_type, ct_1.company_type_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
86. 0.010 0.010 ↑ 1.0 6 1

Seq Scan on client_lactalis_deli.company_type ct_1 (cost=0.00..1.06 rows=6 width=20) (actual time=0.008..0.010 rows=6 loops=1)

  • Output: ct_1.internal_company_type, ct_1.company_type_id
  • Buffers: shared hit=1
87. 0.024 0.024 ↑ 1.0 1 12

Index Scan using product_uom_conversion_pkey on client_lactalis_deli.product_uom_conversion suomc_1 (cost=0.28..0.30 rows=1 width=37) (actual time=0.002..0.002 rows=1 loops=12)

  • Output: suomc_1.product_id, suomc_1.uom_type_id, suomc_1.rate
  • Index Cond: ((suomc_1.product_id = fi_1.product_id) AND (suomc_1.uom_type_id = fi_1.uom_type_id))
  • Buffers: shared hit=39
88. 0.008 2.808 ↑ 2.0 4 1

Hash Left Join (cost=0.68..14.34 rows=8 width=48) (actual time=2.802..2.808 rows=4 loops=1)

  • Output: ci.contract_item_id, base.quantity
  • Hash Cond: (ci.contract_item_id = base.contract_item_id)
  • Buffers: shared hit=576
89. 0.024 0.024 ↑ 2.0 4 1

Index Scan using contract_item_contract_id_product_id_key on client_lactalis_deli.contract_item ci (cost=0.42..14.04 rows=8 width=16) (actual time=0.020..0.024 rows=4 loops=1)

  • Output: ci.contract_item_id, ci.contract_id, ci.product_id, ci.grouping_id, ci.estimated_quantity, ci.uom_type_id, ci.rebate_deviated_value, ci.rebate_deviated_type, ci.inactive_override, ci.rebate_direct_value, ci.rebate_direct_type, ci.rebate_direct_decimal_model, ci.rebate_deviated_decimal_model, ci.won, ci.rebate_growth_type, ci.rebate_growth_decimal_model, ci.lift_quantity
  • Index Cond: (ci.contract_id = 'f26a3d79-ba45-11ea-8740-53a42f606cbb'::uuid)
  • Buffers: shared hit=6
90. 0.003 2.776 ↑ 2.0 4 1

Hash (cost=0.16..0.16 rows=8 width=48) (actual time=2.776..2.776 rows=4 loops=1)

  • Output: base.quantity, base.contract_item_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=570
91. 2.773 2.773 ↑ 2.0 4 1

CTE Scan on base (cost=0.00..0.16 rows=8 width=48) (actual time=2.753..2.773 rows=4 loops=1)

  • Output: base.quantity, base.contract_item_id
  • Buffers: shared hit=570
92. 1.100 1.100 ↓ 3.0 3 4

CTE Scan on obj (cost=0.00..0.02 rows=1 width=48) (actual time=0.273..0.275 rows=3 loops=4)

  • Output: obj.contract_item_id, obj.quantity
  • Buffers: shared hit=340
Planning time : 39.841 ms
Execution time : 4.417 ms