explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZASF

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 3.999 ↓ 10.0 10 1

Subquery Scan on claim_select_product (cost=231.93..231.94 rows=1 width=472) (actual time=3.993..3.999 rows=10 loops=1)

  • Output: claim_select_product.claim_product_id, claim_select_product.product_id, claim_select_product.product_name, claim_select_product.product_number, claim_select_product.uom_type_ids, claim_select_product.distributor_company_id, claim_select_product.distributor_company_name, claim_select_product.distributor_company_number, claim_select_product.uom_type_id, claim_select_product.rebate_calculated_rate, claim_select_product.rebate_requested_quantity, claim_select_product.rebate_requested_rate, claim_select_product.rebate_allowed_quantity, claim_select_product.rebate_allowed_rate, claim_select_product.price, claim_select_product.price_tier, claim_select_product.resolve_date, claim_select_product.claimant_contract_name, claim_select_product.contract_item_id, claim_select_product.contract_id, claim_select_product.contract_title, claim_select_product.contract_version_id, claim_select_product.won, claim_select_product.rebate_method, claim_select_product.rebate_value_type, claim_select_product.rebate_decimal_model, claim_select_product.rebate_value, claim_select_product.distributor_invoice_number, claim_select_product.display_order
  • Buffers: shared hit=292
2. 0.025 3.992 ↓ 10.0 10 1

Sort (cost=231.93..231.93 rows=1 width=480) (actual time=3.990..3.992 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, ((product.product_name)::text), ((product.product_number)::text), (array_agg(product_uom_conversion.uom_type_id)), claim_product.distributor_company_id, ((company.company_name)::text), ((company.company_number)::text), claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc_1.rate / suomc_1.rate))), ('DEFAULT'::enum.price_tier), claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, contract_item.contract_id, ((contract_family.contract_title)::text), ((((((contract_family_1.contract_sequence)::text || '.R'::text) || (contract_renew_version_view.contract_renew_version)::text) || '.A'::text) || (contract_amend_version_view.contract_amend_version)::text)), contract_item.won, ((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method), (CASE WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'GROWTH'::enum.contract_rebate_method) THEN contract_item.rebate_growth_type WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'DIRECT'::enum.contract_rebate_method) THEN contract_item.rebate_direct_type WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'DEVIATED'::enum.contract_rebate_method) THEN contract_item.rebate_deviated_type ELSE NULL::enum.rebate_value_type END), (CASE WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'GROWTH'::enum.contract_rebate_method) THEN contract_item.rebate_growth_decimal_model WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'DIRECT'::enum.contract_rebate_method) THEN contract_item.rebate_direct_decimal_model WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'DEVIATED'::enum.contract_rebate_method) THEN contract_item.rebate_deviated_decimal_model ELSE NULL::enum.decimal_model END), (CASE ((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) WHEN 'GROWTH'::enum.contract_rebate_method THEN NULL::numeric WHEN 'DIRECT'::enum.contract_rebate_method THEN CASE WHEN (contract_item.rebate_direct_type = 'FIXED_RATE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_direct_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_direct_value / (tuomc.rate / suomc.rate)) WHEN (contract_item.rebate_direct_decimal_model = 'PERCENT'::enum.decimal_model) THEN contract_item.rebate_direct_value ELSE NULL::numeric END WHEN (contract_item.rebate_direct_type = 'FIXED_PRICE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_direct_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_direct_value / (tuomc.rate / suomc.rate)) ELSE NULL::numeric END ELSE NULL::numeric END WHEN 'DEVIATED'::enum.contract_rebate_method THEN CASE WHEN (contract_item.rebate_deviated_type = 'FIXED_RATE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_deviated_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_deviated_value / (tuomc.rate / suomc.rate)) WHEN (contract_item.rebate_deviated_decimal_model = 'PERCENT'::enum.decimal_model) THEN contract_item.rebate_deviated_value ELSE NULL::numeric END WHEN (contract_item.rebate_deviated_type = 'FIXED_PRICE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_deviated_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_deviated_value / (tuomc.rate / suomc.rate)) ELSE NULL::numeric END ELSE NULL::numeric END ELSE NULL::numeric END), (COALESCE(claim_product.distributor_invoice_number, (string_agg(claim_product_location.distributor_invoice_number, ', '::text)))), ((cpo.display_order)::integer), cpo.display_order
  • Sort Key: cpo.display_order
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=292
3. 0.020 3.967 ↓ 10.0 10 1

Nested Loop Left Join (cost=182.97..231.92 rows=1 width=480) (actual time=3.653..3.967 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, product.product_name, product.product_number, (array_agg(product_uom_conversion.uom_type_id)), claim_product.distributor_company_id, company.company_name, company.company_number, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc_1.rate / suomc_1.rate))), ('DEFAULT'::enum.price_tier), claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, contract_item.contract_id, contract_family.contract_title, (((((contract_family_1.contract_sequence)::text || '.R'::text) || (contract_renew_version_view.contract_renew_version)::text) || '.A'::text) || (contract_amend_version_view.contract_amend_version)::text), contract_item.won, ((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method), CASE WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'GROWTH'::enum.contract_rebate_method) THEN contract_item.rebate_growth_type WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'DIRECT'::enum.contract_rebate_method) THEN contract_item.rebate_direct_type WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'DEVIATED'::enum.contract_rebate_method) THEN contract_item.rebate_deviated_type ELSE NULL::enum.rebate_value_type END, CASE WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'GROWTH'::enum.contract_rebate_method) THEN contract_item.rebate_growth_decimal_model WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'DIRECT'::enum.contract_rebate_method) THEN contract_item.rebate_direct_decimal_model WHEN (((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) = 'DEVIATED'::enum.contract_rebate_method) THEN contract_item.rebate_deviated_decimal_model ELSE NULL::enum.decimal_model END, CASE ((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method) WHEN 'GROWTH'::enum.contract_rebate_method THEN NULL::numeric WHEN 'DIRECT'::enum.contract_rebate_method THEN CASE WHEN (contract_item.rebate_direct_type = 'FIXED_RATE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_direct_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_direct_value / (tuomc.rate / suomc.rate)) WHEN (contract_item.rebate_direct_decimal_model = 'PERCENT'::enum.decimal_model) THEN contract_item.rebate_direct_value ELSE NULL::numeric END WHEN (contract_item.rebate_direct_type = 'FIXED_PRICE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_direct_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_direct_value / (tuomc.rate / suomc.rate)) ELSE NULL::numeric END ELSE NULL::numeric END WHEN 'DEVIATED'::enum.contract_rebate_method THEN CASE WHEN (contract_item.rebate_deviated_type = 'FIXED_RATE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_deviated_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_deviated_value / (tuomc.rate / suomc.rate)) WHEN (contract_item.rebate_deviated_decimal_model = 'PERCENT'::enum.decimal_model) THEN contract_item.rebate_deviated_value ELSE NULL::numeric END WHEN (contract_item.rebate_deviated_type = 'FIXED_PRICE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_deviated_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_deviated_value / (tuomc.rate / suomc.rate)) ELSE NULL::numeric END ELSE NULL::numeric END ELSE NULL::numeric END, COALESCE(claim_product.distributor_invoice_number, (string_agg(claim_product_location.distributor_invoice_number, ', '::text))), (cpo.display_order)::integer, cpo.display_order
  • Buffers: shared hit=292
4. 0.030 3.917 ↓ 10.0 10 1

Merge Left Join (cost=182.83..227.52 rows=1 width=389) (actual time=3.639..3.917 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_family.contract_title, suomc.rate, tuomc.rate, contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version_view.contract_amend_version, ((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method), ('DEFAULT'::enum.price_tier), (array_agg(product_uom_conversion.uom_type_id)), (string_agg(claim_product_location.distributor_invoice_number, ', '::text)), ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc_1.rate / suomc_1.rate)))
  • Inner Unique: true
  • Merge Cond: (claim_product.claim_product_id = claim_product_location.claim_product_id)
  • Join Filter: (claim_product.claim_id = claim_product_3.claim_id)
  • Buffers: shared hit=282
5. 0.017 3.767 ↓ 10.0 10 1

Nested Loop Left Join (cost=167.83..212.32 rows=1 width=357) (actual time=3.533..3.767 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_family.contract_title, suomc.rate, tuomc.rate, contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version_view.contract_amend_version, ((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method), ('DEFAULT'::enum.price_tier), (array_agg(product_uom_conversion.uom_type_id)), ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc_1.rate / suomc_1.rate)))
  • Buffers: shared hit=257
6. 0.032 3.630 ↓ 10.0 10 1

Nested Loop (cost=166.30..192.47 rows=1 width=321) (actual time=3.501..3.630 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_family.contract_title, suomc.rate, tuomc.rate, contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version_view.contract_amend_version, ((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method), (array_agg(product_uom_conversion.uom_type_id))
  • Join Filter: (claim_product.claim_product_id = claim_product_2.claim_product_id)
  • Rows Removed by Join Filter: 90
  • Buffers: shared hit=217
7. 0.031 0.128 ↓ 2.5 10 1

GroupAggregate (cost=14.87..14.96 rows=4 width=64) (actual time=0.105..0.128 rows=10 loops=1)

  • Output: claim_product_2.claim_id, claim_product_2.claim_product_id, array_agg(product_uom_conversion.uom_type_id)
  • Group Key: claim_product_2.claim_product_id
  • Buffers: shared hit=25
8. 0.021 0.097 ↓ 2.0 10 1

Sort (cost=14.87..14.88 rows=5 width=48) (actual time=0.094..0.097 rows=10 loops=1)

  • Output: claim_product_2.claim_product_id, claim_product_2.claim_id, product_uom_conversion.uom_type_id
  • Sort Key: claim_product_2.claim_product_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=25
9. 0.014 0.076 ↓ 2.0 10 1

Nested Loop (cost=2.10..14.81 rows=5 width=48) (actual time=0.038..0.076 rows=10 loops=1)

  • Output: claim_product_2.claim_product_id, claim_product_2.claim_id, product_uom_conversion.uom_type_id
  • Buffers: shared hit=25
10. 0.015 0.032 ↓ 2.5 10 1

Bitmap Heap Scan on client_demo.claim_product claim_product_2 (cost=1.82..7.58 rows=4 width=48) (actual time=0.025..0.032 rows=10 loops=1)

  • Output: claim_product_2.claim_id, claim_product_2.claim_product_id, claim_product_2.product_id
  • Recheck Cond: (claim_product_2.claim_id = '79fc2375-6e6b-4300-8909-9cf665c0b6a2'::uuid)
  • Heap Blocks: exact=2
  • Buffers: shared hit=4
11. 0.017 0.017 ↓ 2.5 10 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..1.81 rows=4 width=0) (actual time=0.017..0.017 rows=10 loops=1)

  • Index Cond: (claim_product_2.claim_id = '79fc2375-6e6b-4300-8909-9cf665c0b6a2'::uuid)
  • Buffers: shared hit=2
12. 0.030 0.030 ↑ 1.0 1 10

Index Only Scan using product_uom_conversion_pkey on client_demo.product_uom_conversion (cost=0.28..1.80 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=10)

  • Output: product_uom_conversion.product_id, product_uom_conversion.uom_type_id
  • Index Cond: (product_uom_conversion.product_id = claim_product_2.product_id)
  • Heap Fetches: 0
  • Buffers: shared hit=21
13. 0.031 3.470 ↓ 2.5 10 10

Materialize (cost=151.43..177.25 rows=4 width=289) (actual time=0.328..0.347 rows=10 loops=10)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_family.contract_title, suomc.rate, tuomc.rate, contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version_view.contract_amend_version, ((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method)
  • Buffers: shared hit=192
14. 0.012 3.439 ↓ 2.5 10 1

Nested Loop Left Join (cost=151.43..177.23 rows=4 width=289) (actual time=3.272..3.439 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_family.contract_title, suomc.rate, tuomc.rate, contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version_view.contract_amend_version, ((CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method)
  • Buffers: shared hit=192
15. 0.011 3.417 ↓ 2.5 10 1

Nested Loop Left Join (cost=151.43..176.99 rows=4 width=297) (actual time=3.266..3.417 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claimant_company_type.internal_company_type, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_family.contract_title, contract_family.contract_type, ct.internal_company_type, suomc.rate, tuomc.rate, contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version_view.contract_amend_version
  • Join Filter: (contract_item.uom_type_id = suomc.uom_type_id)
  • Rows Removed by Join Filter: 10
  • Buffers: shared hit=192
16. 0.018 3.266 ↓ 2.5 10 1

Nested Loop Left Join (cost=150.59..167.09 rows=4 width=303) (actual time=3.230..3.266 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claimant_company_type.internal_company_type, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_item.uom_type_id, contract_family.contract_title, contract_family.contract_type, ct.internal_company_type, contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version_view.contract_amend_version
  • Buffers: shared hit=111
17. 0.089 3.228 ↓ 2.5 10 1

Hash Right Join (cost=149.78..161.89 rows=4 width=315) (actual time=3.216..3.228 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claimant_company_type.internal_company_type, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_item.uom_type_id, contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version_view.contract_amend_version, contract_family.contract_title, contract_family.contract_type, contract_family.contract_family_id
  • Hash Cond: (contract_amend_version.contract_amend_version_id = contract_item.contract_id)
  • Buffers: shared hit=111
18. 0.161 2.814 ↑ 1.0 290 1

Hash Join (cost=87.31..98.29 rows=290 width=36) (actual time=2.119..2.814 rows=290 loops=1)

  • Output: contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version.contract_amend_version_id, contract_amend_version_view.contract_amend_version
  • Inner Unique: true
  • Hash Cond: (contract_amend_version.contract_amend_version_id = contract_amend_version_view.contract_amend_version_id)
  • Buffers: shared hit=30
19. 0.162 1.685 ↑ 1.0 290 1

Hash Join (cost=49.24..59.46 rows=290 width=28) (actual time=1.142..1.685 rows=290 loops=1)

  • Output: contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version.contract_amend_version_id
  • Inner Unique: true
  • Hash Cond: (contract_renew_version.contract_renew_version_id = contract_renew_version_view.contract_renew_version_id)
  • Buffers: shared hit=20
20. 0.157 0.744 ↑ 1.0 290 1

Hash Join (cost=19.17..28.61 rows=290 width=52) (actual time=0.355..0.744 rows=290 loops=1)

  • Output: contract_family_1.contract_sequence, contract_renew_version.contract_renew_version_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id
  • Inner Unique: true
  • Hash Cond: (contract_renew_version.contract_family_id = contract_family_1.contract_family_id)
  • Buffers: shared hit=14
21. 0.200 0.452 ↑ 1.0 290 1

Hash Join (cost=8.54..17.21 rows=290 width=64) (actual time=0.208..0.452 rows=290 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id
  • Inner Unique: true
  • Hash Cond: (contract_amend_version.contract_renew_version_id = contract_renew_version.contract_renew_version_id)
  • Buffers: shared hit=8
22. 0.059 0.059 ↑ 1.0 290 1

Seq Scan on client_demo.contract_amend_version (cost=0.00..7.90 rows=290 width=32) (actual time=0.005..0.059 rows=290 loops=1)

  • Output: contract_amend_version.contract_amend_version_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_state, contract_amend_version.contract_amend_version_parent_id, contract_amend_version.contract_name, contract_amend_version.contact_name, contract_amend_version.contact_email, contract_amend_version.contact_phone, contract_amend_version.create_timestamp
  • Buffers: shared hit=5
23. 0.100 0.193 ↑ 1.0 246 1

Hash (cost=5.46..5.46 rows=246 width=32) (actual time=0.193..0.193 rows=246 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id
  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=3
24. 0.093 0.093 ↑ 1.0 246 1

Seq Scan on client_demo.contract_renew_version (cost=0.00..5.46 rows=246 width=32) (actual time=0.005..0.093 rows=246 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id
  • Buffers: shared hit=3
25. 0.071 0.135 ↑ 1.0 206 1

Hash (cost=8.06..8.06 rows=206 width=20) (actual time=0.135..0.135 rows=206 loops=1)

  • Output: contract_family_1.contract_sequence, contract_family_1.contract_family_id
  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=6
26. 0.064 0.064 ↑ 1.0 206 1

Seq Scan on client_demo.contract_family contract_family_1 (cost=0.00..8.06 rows=206 width=20) (actual time=0.008..0.064 rows=206 loops=1)

  • Output: contract_family_1.contract_sequence, contract_family_1.contract_family_id
  • Buffers: shared hit=6
27. 0.095 0.779 ↑ 1.0 246 1

Hash (cost=27.00..27.00 rows=246 width=24) (actual time=0.778..0.779 rows=246 loops=1)

  • Output: contract_renew_version_view.contract_renew_version, contract_renew_version_view.contract_renew_version_id
  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=6
28. 0.059 0.684 ↑ 1.0 246 1

Subquery Scan on contract_renew_version_view (cost=22.08..27.00 rows=246 width=24) (actual time=0.553..0.684 rows=246 loops=1)

  • Output: contract_renew_version_view.contract_renew_version, contract_renew_version_view.contract_renew_version_id
  • Buffers: shared hit=6
29. 0.219 0.625 ↑ 1.0 246 1

HashAggregate (cost=22.08..24.54 rows=246 width=24) (actual time=0.552..0.625 rows=246 loops=1)

  • Output: crv1.contract_renew_version_id, count(crv2.contract_renew_version_id)
  • Group Key: crv1.contract_renew_version_id
  • Buffers: shared hit=6
30. 0.195 0.406 ↓ 1.1 271 1

Hash Left Join (cost=8.54..20.85 rows=246 width=32) (actual time=0.181..0.406 rows=271 loops=1)

  • Output: crv1.contract_renew_version_id, crv2.contract_renew_version_id
  • Hash Cond: (crv1.contract_family_id = crv2.contract_family_id)
  • Join Filter: (crv2.create_timestamp < crv1.create_timestamp)
  • Rows Removed by Join Filter: 311
  • Buffers: shared hit=6
31. 0.043 0.043 ↑ 1.0 246 1

Seq Scan on client_demo.contract_renew_version crv1 (cost=0.00..5.46 rows=246 width=40) (actual time=0.005..0.043 rows=246 loops=1)

  • Output: crv1.contract_renew_version_id, crv1.contract_family_id, crv1.date_range, crv1.create_timestamp, crv1.dead
  • Buffers: shared hit=3
32. 0.096 0.168 ↑ 1.0 246 1

Hash (cost=5.46..5.46 rows=246 width=40) (actual time=0.168..0.168 rows=246 loops=1)

  • Output: crv2.contract_renew_version_id, crv2.contract_family_id, crv2.create_timestamp
  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
  • Buffers: shared hit=3
33. 0.072 0.072 ↑ 1.0 246 1

Seq Scan on client_demo.contract_renew_version crv2 (cost=0.00..5.46 rows=246 width=40) (actual time=0.003..0.072 rows=246 loops=1)

  • Output: crv2.contract_renew_version_id, crv2.contract_family_id, crv2.create_timestamp
  • Buffers: shared hit=3
34. 0.109 0.968 ↑ 1.0 290 1

Hash (cost=34.44..34.44 rows=290 width=24) (actual time=0.968..0.968 rows=290 loops=1)

  • Output: contract_amend_version_view.contract_amend_version, contract_amend_version_view.contract_amend_version_id
  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=10
35. 0.069 0.859 ↑ 1.0 290 1

Subquery Scan on contract_amend_version_view (cost=28.64..34.44 rows=290 width=24) (actual time=0.705..0.859 rows=290 loops=1)

  • Output: contract_amend_version_view.contract_amend_version, contract_amend_version_view.contract_amend_version_id
  • Buffers: shared hit=10
36. 0.282 0.790 ↑ 1.0 290 1

HashAggregate (cost=28.64..31.54 rows=290 width=24) (actual time=0.705..0.790 rows=290 loops=1)

  • Output: cav1.contract_amend_version_id, count(cav2.contract_amend_version_id)
  • Group Key: cav1.contract_amend_version_id
  • Buffers: shared hit=10
37. 0.222 0.508 ↓ 1.1 310 1

Hash Left Join (cost=11.53..27.19 rows=290 width=32) (actual time=0.248..0.508 rows=310 loops=1)

  • Output: cav1.contract_amend_version_id, cav2.contract_amend_version_id
  • Hash Cond: (cav1.contract_renew_version_id = cav2.contract_renew_version_id)
  • Join Filter: (cav2.create_timestamp < cav1.create_timestamp)
  • Rows Removed by Join Filter: 354
  • Buffers: shared hit=10
38. 0.054 0.054 ↑ 1.0 290 1

Seq Scan on client_demo.contract_amend_version cav1 (cost=0.00..7.90 rows=290 width=40) (actual time=0.008..0.054 rows=290 loops=1)

  • Output: cav1.contract_amend_version_id, cav1.contract_renew_version_id, cav1.contract_state, cav1.contract_amend_version_parent_id, cav1.contract_name, cav1.contact_name, cav1.contact_email, cav1.contact_phone, cav1.create_timestamp
  • Buffers: shared hit=5
39. 0.117 0.232 ↑ 1.0 290 1

Hash (cost=7.90..7.90 rows=290 width=40) (actual time=0.232..0.232 rows=290 loops=1)

  • Output: cav2.contract_amend_version_id, cav2.contract_renew_version_id, cav2.create_timestamp
  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
  • Buffers: shared hit=5
40. 0.115 0.115 ↑ 1.0 290 1

Seq Scan on client_demo.contract_amend_version cav2 (cost=0.00..7.90 rows=290 width=40) (actual time=0.004..0.115 rows=290 loops=1)

  • Output: cav2.contract_amend_version_id, cav2.contract_renew_version_id, cav2.create_timestamp
  • Buffers: shared hit=5
41. 0.012 0.325 ↓ 2.5 10 1

Hash (cost=62.42..62.42 rows=4 width=295) (actual time=0.325..0.325 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claimant_company_type.internal_company_type, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_item.uom_type_id, contract_family.contract_title, contract_family.contract_type, contract_family.contract_family_id
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=81
42. 0.085 0.313 ↓ 2.5 10 1

Hash Right Join (cost=52.81..62.42 rows=4 width=295) (actual time=0.304..0.313 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claimant_company_type.internal_company_type, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_item.uom_type_id, contract_family.contract_title, contract_family.contract_type, contract_family.contract_family_id
  • Hash Cond: (contract_family.contract_sequence = claim.contract_sequence)
  • Buffers: shared hit=81
43. 0.048 0.048 ↑ 1.0 206 1

Seq Scan on client_demo.contract_family (cost=0.00..8.06 rows=206 width=39) (actual time=0.009..0.048 rows=206 loops=1)

  • Output: contract_family.contract_family_id, contract_family.client_id, contract_family.contractee_company_id, contract_family.contractee_grouping_id, contract_family.creator_id, contract_family.contract_sequence, contract_family.cost_basis_type, contract_family.contract_type, contract_family.contract_title, contract_family.contract_template_id, contract_family.payment_calendar_id, contract_family.catch_all, contract_family.import_contract_number, contract_family.accrual_quantity_source
  • Buffers: shared hit=6
44. 0.014 0.180 ↓ 2.5 10 1

Hash (cost=52.76..52.76 rows=4 width=264) (actual time=0.180..0.180 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim.contract_sequence, claimant_company_type.internal_company_type, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_item.uom_type_id
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=75
45. 0.014 0.166 ↓ 2.5 10 1

Nested Loop Left Join (cost=3.60..52.76 rows=4 width=264) (actual time=0.076..0.166 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim.contract_sequence, claimant_company_type.internal_company_type, product.product_name, product.product_number, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_direct_type, contract_item.rebate_deviated_type, contract_item.rebate_growth_decimal_model, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_value, contract_item.uom_type_id
  • Inner Unique: true
  • Buffers: shared hit=75
46. 0.007 0.152 ↓ 2.5 10 1

Nested Loop Left Join (cost=3.32..39.57 rows=4 width=199) (actual time=0.072..0.152 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim.contract_sequence, claimant_company_type.internal_company_type, product.product_name, product.product_number, company.company_name, company.company_number
  • Inner Unique: true
  • Buffers: shared hit=75
47. 0.008 0.115 ↓ 2.5 10 1

Nested Loop (cost=3.05..27.89 rows=4 width=176) (actual time=0.066..0.115 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim.contract_sequence, claimant_company_type.internal_company_type, product.product_name, product.product_number
  • Inner Unique: true
  • Buffers: shared hit=45
48. 0.007 0.067 ↓ 2.5 10 1

Nested Loop (cost=2.77..14.71 rows=4 width=147) (actual time=0.056..0.067 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim.contract_sequence, claimant_company_type.internal_company_type
  • Buffers: shared hit=15
49. 0.001 0.042 ↑ 1.0 1 1

Nested Loop (cost=0.96..7.09 rows=1 width=24) (actual time=0.041..0.042 rows=1 loops=1)

  • Output: claim.claim_id, claim.contract_sequence, claimant_company_type.internal_company_type
  • Inner Unique: true
  • Buffers: shared hit=11
50. 0.002 0.035 ↑ 1.0 1 1

Nested Loop (cost=0.82..6.92 rows=1 width=36) (actual time=0.033..0.035 rows=1 loops=1)

  • Output: claim.claim_id, claim.contract_sequence, claimant_company.company_type_id
  • Inner Unique: true
  • Buffers: shared hit=9
51. 0.002 0.023 ↑ 1.0 1 1

Nested Loop (cost=0.55..6.60 rows=1 width=36) (actual time=0.022..0.023 rows=1 loops=1)

  • Output: claim.claim_id, claim.contract_sequence, invoice.claimant_company_id
  • Inner Unique: true
  • Buffers: shared hit=6
52. 0.011 0.011 ↑ 1.0 1 1

Index Scan using claim_pkey on client_demo.claim (cost=0.28..3.29 rows=1 width=36) (actual time=0.010..0.011 rows=1 loops=1)

  • Output: claim.claim_id, claim.invoice_id, claim.claim_state, claim.claim_sequence, claim.transaction_type, claim.contract_sequence, claim.claim_tag
  • Index Cond: (claim.claim_id = '79fc2375-6e6b-4300-8909-9cf665c0b6a2'::uuid)
  • Buffers: shared hit=3
53. 0.010 0.010 ↑ 1.0 1 1

Index Scan using invoice_pkey on client_demo.invoice (cost=0.28..3.29 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)

  • Output: invoice.invoice_id, invoice.claimant_company_id
  • Index Cond: (invoice.invoice_id = claim.invoice_id)
  • Buffers: shared hit=3
54. 0.010 0.010 ↑ 1.0 1 1

Index Scan using company_pkey on client_demo.company claimant_company (cost=0.27..0.32 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=1)

  • Output: claimant_company.company_id, claimant_company.client_id, claimant_company.company_name, claimant_company.company_number, claimant_company.company_type_id, claimant_company.active_ind, claimant_company.address_1, claimant_company.address_2, claimant_company.address_3, claimant_company.address_4, claimant_company.city, claimant_company.region, claimant_company.postal_code, claimant_company.country, claimant_company.company_agg_id, claimant_company.deduction_interval, claimant_company.alt_address_active, claimant_company.alt_address_1, claimant_company.alt_address_2, claimant_company.alt_address_3, claimant_company.alt_address_4, claimant_company.alt_city, claimant_company.alt_region, claimant_company.alt_postal_code, claimant_company.alt_country, claimant_company.atlas_company_id, claimant_company.email, claimant_company.tax_type_id, claimant_company.redistributor_ind, claimant_company.operator_request, claimant_company.early_payment_discount, claimant_company.vip
  • Index Cond: (claimant_company.company_id = invoice.claimant_company_id)
  • Buffers: shared hit=3
55. 0.006 0.006 ↑ 1.0 1 1

Index Scan using company_type_pkey on client_demo.company_type claimant_company_type (cost=0.14..0.17 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=1)

  • Output: claimant_company_type.company_type_id, claimant_company_type.client_id, claimant_company_type.company_type_description, claimant_company_type.company_type_cd, claimant_company_type.active_ind, claimant_company_type.internal_company_type
  • Index Cond: (claimant_company_type.company_type_id = claimant_company.company_type_id)
  • Buffers: shared hit=2
56. 0.011 0.018 ↓ 2.5 10 1

Bitmap Heap Scan on client_demo.claim_product (cost=1.82..7.58 rows=4 width=139) (actual time=0.011..0.018 rows=10 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id
  • Recheck Cond: (claim_product.claim_id = '79fc2375-6e6b-4300-8909-9cf665c0b6a2'::uuid)
  • Heap Blocks: exact=2
  • Buffers: shared hit=4
57. 0.007 0.007 ↓ 2.5 10 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..1.81 rows=4 width=0) (actual time=0.007..0.007 rows=10 loops=1)

  • Index Cond: (claim_product.claim_id = '79fc2375-6e6b-4300-8909-9cf665c0b6a2'::uuid)
  • Buffers: shared hit=2
58. 0.040 0.040 ↑ 1.0 1 10

Index Scan using product_pkey on client_demo.product (cost=0.28..3.29 rows=1 width=45) (actual time=0.004..0.004 rows=1 loops=10)

  • Output: product.product_id, product.client_id, product.product_name, product.product_number, product.gtin_number, product.brand_name, product.active_ind, product.default_uom_type_id, product.pack, product.size, product.size_uom, product.pack_size_size_uom, product.upc_number, product.reporting_visibility_ind, product.private_label_grouping_id
  • Index Cond: (product.product_id = claim_product.product_id)
  • Buffers: shared hit=30
59. 0.030 0.030 ↑ 1.0 1 10

Index Scan using company_pkey on client_demo.company (cost=0.27..2.92 rows=1 width=39) (actual time=0.003..0.003 rows=1 loops=10)

  • Output: company.company_id, company.client_id, company.company_name, company.company_number, company.company_type_id, company.active_ind, company.address_1, company.address_2, company.address_3, company.address_4, company.city, company.region, company.postal_code, company.country, company.company_agg_id, company.deduction_interval, company.alt_address_active, company.alt_address_1, company.alt_address_2, company.alt_address_3, company.alt_address_4, company.alt_city, company.alt_region, company.alt_postal_code, company.alt_country, company.atlas_company_id, company.email, company.tax_type_id, company.redistributor_ind, company.operator_request, company.early_payment_discount, company.vip
  • Index Cond: (claim_product.distributor_company_id = company.company_id)
  • Buffers: shared hit=30
60. 0.000 0.000 ↓ 0.0 0 10

Index Scan using contract_item_pkey on client_demo.contract_item (cost=0.28..3.29 rows=1 width=81) (actual time=0.000..0.000 rows=0 loops=10)

  • Output: contract_item.contract_item_id, contract_item.contract_id, contract_item.product_id, contract_item.grouping_id, contract_item.estimated_quantity, contract_item.uom_type_id, contract_item.rebate_deviated_value, contract_item.rebate_deviated_type, contract_item.inactive_override, contract_item.rebate_direct_value, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_growth_decimal_model
  • Index Cond: (claim_product.contract_item_id = contract_item.contract_item_id)
61. 0.000 0.020 ↓ 0.0 0 10

Nested Loop (cost=0.82..1.29 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=10)

  • Output: c.contract_family_id, ct.internal_company_type
  • Inner Unique: true
62. 0.010 0.020 ↓ 0.0 0 10

Nested Loop Left Join (cost=0.68..0.93 rows=1 width=48) (actual time=0.002..0.002 rows=0 loops=10)

  • Output: c.contract_family_id, gc.company_type_id, cmp.company_type_id
  • Inner Unique: true
63. 0.000 0.010 ↓ 0.0 0 10

Nested Loop Left Join (cost=0.41..0.55 rows=1 width=48) (actual time=0.001..0.001 rows=0 loops=10)

  • Output: c.contractee_company_id, c.contract_family_id, gc.company_type_id
  • Inner Unique: true
64. 0.010 0.010 ↓ 0.0 0 10

Index Scan using contract_family_pkey on client_demo.contract_family c (cost=0.27..0.36 rows=1 width=48) (actual time=0.001..0.001 rows=0 loops=10)

  • 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.import_contract_number, c.accrual_quantity_source
  • Index Cond: (contract_family.contract_family_id = c.contract_family_id)
65. 0.000 0.000 ↓ 0.0 0

Index Scan using grouping_company_pkey on client_demo.grouping_company gc (cost=0.14..0.18 rows=1 width=32) (never executed)

  • Output: gc.grouping_id, gc.client_id, gc.grouping_description, gc.grouping_cd, gc.grouping_type, gc.parent_grouping_id, gc.active_ind, gc.company_type_id, gc.grouping_company_type
  • Index Cond: (gc.grouping_id = c.contractee_grouping_id)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using company_pkey on client_demo.company cmp (cost=0.27..0.38 rows=1 width=32) (never executed)

  • 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
  • Index Cond: (cmp.company_id = c.contractee_company_id)
67. 0.000 0.000 ↓ 0.0 0

Index Scan using company_type_pkey on client_demo.company_type ct (cost=0.14..0.33 rows=1 width=20) (never executed)

  • Output: ct.company_type_id, ct.client_id, ct.company_type_description, ct.company_type_cd, ct.active_ind, ct.internal_company_type
  • Index Cond: (ct.company_type_id = COALESCE(cmp.company_type_id, gc.company_type_id))
68. 0.020 0.140 ↑ 1.0 1 10

Nested Loop (cost=0.84..2.46 rows=1 width=58) (actual time=0.012..0.014 rows=1 loops=10)

  • Output: pro.product_id, suomc.rate, suomc.uom_type_id, tuomc.rate, tuomc.uom_type_id
  • Join Filter: (claim_product.uom_type_id = tuomc.uom_type_id)
  • Buffers: shared hit=81
69. 0.010 0.090 ↑ 1.0 1 10

Nested Loop (cost=0.56..2.13 rows=1 width=53) (actual time=0.008..0.009 rows=1 loops=10)

  • Output: pro.product_id, suomc.rate, suomc.product_id, suomc.uom_type_id
  • Buffers: shared hit=51
70. 0.040 0.040 ↑ 1.0 1 10

Index Only Scan using product_pkey on client_demo.product pro (cost=0.28..1.80 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=10)

  • Output: pro.product_id
  • Index Cond: (pro.product_id = claim_product.product_id)
  • Heap Fetches: 0
  • Buffers: shared hit=21
71. 0.040 0.040 ↑ 1.0 1 10

Index Scan using product_uom_conversion_pkey on client_demo.product_uom_conversion suomc (cost=0.28..0.32 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=10)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
  • Index Cond: (suomc.product_id = pro.product_id)
  • Buffers: shared hit=30
72. 0.030 0.030 ↑ 1.0 1 10

Index Scan using product_uom_conversion_pkey on client_demo.product_uom_conversion tuomc (cost=0.28..0.32 rows=1 width=37) (actual time=0.003..0.003 rows=1 loops=10)

  • Output: tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Index Cond: (tuomc.product_id = pro.product_id)
  • Buffers: shared hit=30
73. 0.010 0.010 ↑ 1.0 1 10

Result (cost=0.00..0.04 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=10)

  • Output: NULL::uuid, (CASE WHEN (contract_family.contract_type = ANY ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) THEN 'GROWTH'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'LOCATION'::enum.internal_company_type)) THEN 'DIRECT'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'LOCATION'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DEVIATED'::text WHEN ((contract_family.contract_type <> ALL ('{GROWTH_AUTOPAY,GROWTH_STANDARD}'::enum.contract_type[])) AND (ct.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type) AND (claimant_company_type.internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)) THEN 'DIRECT'::text ELSE NULL::text END)::enum.contract_rebate_method, NULL::enum.claim_product_contract_eligibility_type
74. 0.000 0.120 ↓ 0.0 0 10

Nested Loop Left Join (cost=1.53..19.84 rows=1 width=68) (actual time=0.012..0.012 rows=0 loops=10)

  • Output: claim_product_1.claim_product_id, claim_product_1.claim_id, ('DEFAULT'::enum.price_tier), (CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc_1.rate / suomc_1.rate))
  • Buffers: shared hit=40
75. 0.010 0.120 ↓ 0.0 0 10

Nested Loop (cost=1.41..19.66 rows=1 width=75) (actual time=0.012..0.012 rows=0 loops=10)

  • Output: claim_product_1.claim_product_id, claim_product_1.claim_id, ('DEFAULT'::enum.price_tier), price_default.price_id, price_default.price, suomc_1.rate, tuomc_1.rate
  • Inner Unique: true
  • Join Filter: ((pro_1.product_id = tuomc_1.product_id) AND (claim_product_1.uom_type_id = tuomc_1.uom_type_id))
  • Buffers: shared hit=40
76. 0.000 0.110 ↓ 0.0 0 10

Nested Loop (cost=1.13..19.34 rows=1 width=134) (actual time=0.011..0.011 rows=0 loops=10)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id, ('DEFAULT'::enum.price_tier), price_default.product_id, price_default.price_id, price_default.price, pro_1.product_id, suomc_1.product_id, suomc_1.rate
  • Inner Unique: true
  • Buffers: shared hit=40
77. 0.010 0.110 ↓ 0.0 0 10

Nested Loop (cost=0.85..19.03 rows=1 width=118) (actual time=0.011..0.011 rows=0 loops=10)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id, ('DEFAULT'::enum.price_tier), price_default.product_id, price_default.price_id, price_default.price, suomc_1.product_id, suomc_1.rate
  • Inner Unique: true
  • Buffers: shared hit=40
78. 0.000 0.100 ↓ 0.0 0 10

Nested Loop (cost=0.57..8.29 rows=35 width=113) (actual time=0.010..0.010 rows=0 loops=10)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id, ('DEFAULT'::enum.price_tier), price_default.product_id, price_default.uom_type_id, price_default.price_id, price_default.price
  • Buffers: shared hit=40
79. 0.010 0.100 ↓ 0.0 0 10

Nested Loop (cost=0.43..4.62 rows=1 width=64) (actual time=0.010..0.010 rows=0 loops=10)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id, claim_product_price.price_id
  • Inner Unique: true
  • Buffers: shared hit=40
80. 0.070 0.070 ↑ 1.0 1 10

Index Scan using claim_product_pkey on client_demo.claim_product claim_product_1 (cost=0.29..3.31 rows=1 width=48) (actual time=0.006..0.007 rows=1 loops=10)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id
  • Index Cond: (claim_product.claim_product_id = claim_product_1.claim_product_id)
  • Filter: ((claim_product_1.claim_id = '79fc2375-6e6b-4300-8909-9cf665c0b6a2'::uuid) AND (claim_product.claim_id = claim_product_1.claim_id))
  • Buffers: shared hit=30
81. 0.020 0.020 ↓ 0.0 0 10

Index Scan using claim_product_price_unique on client_demo.claim_product_price (cost=0.14..1.28 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=10)

  • Output: claim_product_price.claim_product_id, claim_product_price.price_id
  • Index Cond: (claim_product_price.claim_product_id = claim_product_1.claim_product_id)
  • Buffers: shared hit=10
82. 0.000 0.000 ↓ 0.0 0

Append (cost=0.14..3.62 rows=5 width=65) (never executed)

83. 0.000 0.000 ↓ 0.0 0

Index Scan using price_default_pkey on client_demo.price_default (cost=0.14..0.21 rows=1 width=57) (never executed)

  • Output: 'DEFAULT'::enum.price_tier, price_default.product_id, price_default.uom_type_id, price_default.price_id, price_default.price
  • Index Cond: (price_default.price_id = claim_product_price.price_id)
84. 0.000 0.000 ↓ 0.0 0

Index Scan using price_generated_pkey on client_demo.price_sale (cost=0.28..2.69 rows=1 width=64) (never executed)

  • Output: 'SALE'::enum.price_tier, price_sale.product_id, price_sale.uom_type_id, price_sale.price_id, price_sale.price
  • Index Cond: (price_sale.price_id = claim_product_price.price_id)
85. 0.000 0.000 ↓ 0.0 0

Index Scan using price_company_pkey on client_demo.price_company (cost=0.13..0.19 rows=1 width=59) (never executed)

  • Output: 'COMPANY'::enum.price_tier, price_company.product_id, price_company.uom_type_id, price_company.price_id, price_company.price
  • Index Cond: (price_company.price_id = claim_product_price.price_id)
86. 0.000 0.000 ↓ 0.0 0

Index Scan using price_list_pkey on client_demo.price_list (cost=0.12..0.19 rows=1 width=59) (never executed)

  • Output: 'LIST'::enum.price_tier, price_list.product_id, price_list.uom_type_id, price_list.price_id, price_list.price
  • Index Cond: (price_list.price_id = claim_product_price.price_id)
87. 0.000 0.000 ↓ 0.0 0

Index Scan using price_class_pkey on client_demo.price_class (cost=0.15..0.34 rows=1 width=84) (never executed)

  • Output: 'CLASS'::enum.price_tier, price_class.product_id, price_class.uom_type_id, price_class.price_id, price_class.price
  • Index Cond: (price_class.price_id = claim_product_price.price_id)
88. 0.000 0.000 ↓ 0.0 0

Index Scan using product_uom_conversion_pkey on client_demo.product_uom_conversion suomc_1 (cost=0.28..0.31 rows=1 width=37) (never executed)

  • Output: suomc_1.product_id, suomc_1.uom_type_id, suomc_1.rate
  • Index Cond: ((suomc_1.product_id = price_default.product_id) AND (suomc_1.uom_type_id = price_default.uom_type_id))
89. 0.000 0.000 ↓ 0.0 0

Index Only Scan using product_pkey on client_demo.product pro_1 (cost=0.28..0.31 rows=1 width=16) (never executed)

  • Output: pro_1.product_id
  • Index Cond: (pro_1.product_id = suomc_1.product_id)
  • Heap Fetches: 0
90. 0.000 0.000 ↓ 0.0 0

Index Scan using product_uom_conversion_pkey on client_demo.product_uom_conversion tuomc_1 (cost=0.28..0.30 rows=1 width=37) (never executed)

  • Output: tuomc_1.product_id, tuomc_1.uom_type_id, tuomc_1.rate
  • Index Cond: (tuomc_1.product_id = price_default.product_id)
91. 0.000 0.000 ↓ 0.0 0

Index Scan using price_modified_excl on client_demo.price_modified pm (cost=0.12..0.15 rows=1 width=25) (never executed)

  • Output: pm.price_id, pm.math_operation_value, pm.math_operation
  • Index Cond: (price_default.price_id = pm.price_id)
  • Filter: upper_inf(pm.active_range)
92. 0.007 0.120 ↓ 2.0 10 1

Materialize (cost=15.00..15.17 rows=5 width=64) (actual time=0.101..0.120 rows=10 loops=1)

  • Output: claim_product_3.claim_id, claim_product_location.claim_product_id, (string_agg(claim_product_location.distributor_invoice_number, ', '::text))
  • Buffers: shared hit=25
93. 0.019 0.113 ↓ 2.0 10 1

GroupAggregate (cost=15.00..15.11 rows=5 width=64) (actual time=0.099..0.113 rows=10 loops=1)

  • Output: claim_product_3.claim_id, claim_product_location.claim_product_id, string_agg(claim_product_location.distributor_invoice_number, ', '::text)
  • Group Key: claim_product_3.claim_id, claim_product_location.claim_product_id
  • Buffers: shared hit=25
94. 0.020 0.094 ↓ 2.0 10 1

Sort (cost=15.00..15.01 rows=5 width=41) (actual time=0.092..0.094 rows=10 loops=1)

  • Output: claim_product_3.claim_id, claim_product_location.claim_product_id, claim_product_location.distributor_invoice_number
  • Sort Key: claim_product_location.claim_product_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=25
95. 0.005 0.074 ↓ 2.0 10 1

Nested Loop (cost=2.10..14.94 rows=5 width=41) (actual time=0.027..0.074 rows=10 loops=1)

  • Output: claim_product_3.claim_id, claim_product_location.claim_product_id, claim_product_location.distributor_invoice_number
  • Buffers: shared hit=25
96. 0.010 0.019 ↓ 2.5 10 1

Bitmap Heap Scan on client_demo.claim_product claim_product_3 (cost=1.82..7.58 rows=4 width=32) (actual time=0.014..0.019 rows=10 loops=1)

  • Output: claim_product_3.claim_id, claim_product_3.claim_product_id
  • Recheck Cond: (claim_product_3.claim_id = '79fc2375-6e6b-4300-8909-9cf665c0b6a2'::uuid)
  • Heap Blocks: exact=2
  • Buffers: shared hit=4
97. 0.009 0.009 ↓ 2.5 10 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..1.81 rows=4 width=0) (actual time=0.009..0.009 rows=10 loops=1)

  • Index Cond: (claim_product_3.claim_id = '79fc2375-6e6b-4300-8909-9cf665c0b6a2'::uuid)
  • Buffers: shared hit=2
98. 0.050 0.050 ↑ 2.0 1 10

Index Only Scan using claim_product_location_pkey on client_demo.claim_product_location (cost=0.29..1.82 rows=2 width=25) (actual time=0.004..0.005 rows=1 loops=10)

  • Output: claim_product_location.claim_product_id, claim_product_location.location_id, claim_product_location.distributor_invoice_number
  • Index Cond: (claim_product_location.claim_product_id = claim_product_3.claim_product_id)
  • Heap Fetches: 0
  • Buffers: shared hit=21
99. 0.010 0.030 ↓ 0.0 0 10

Nested Loop (cost=0.15..4.29 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=10)

  • Output: claim_product_order.claim_id, cpo.display_order, cpo.claim_product_id
  • Join Filter: (claim_product.claim_product_id = cpo.claim_product_id)
  • Buffers: shared hit=10
100. 0.020 0.020 ↓ 0.0 0 10

Index Scan using claim_product_order_pkey on client_demo.claim_product_order (cost=0.14..2.04 rows=1 width=88) (actual time=0.002..0.002 rows=0 loops=10)

  • Output: claim_product_order.claim_id, claim_product_order.item_ids
  • Index Cond: ((claim_product.claim_id = claim_product_order.claim_id) AND (claim_product_order.claim_id = '79fc2375-6e6b-4300-8909-9cf665c0b6a2'::uuid))
  • Buffers: shared hit=10
101. 0.000 0.000 ↓ 0.0 0

Function Scan on pg_catalog.unnest cpo (cost=0.00..1.00 rows=100 width=24) (never executed)

  • Output: cpo.claim_product_id, cpo.display_order
  • Function Call: unnest(claim_product_order.item_ids)