explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 34x

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 206.090 ↑ 1.0 1 1

Subquery Scan on claim_select_product (cost=8,724.32..8,724.33 rows=1 width=490) (actual time=206.089..206.090 rows=1 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=1679
2. 0.016 206.085 ↑ 1.0 1 1

Sort (cost=8,724.32..8,724.32 rows=1 width=498) (actual time=206.084..206.085 rows=1 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, ((product.product_name)::text), ((product.product_number)::text), product_uom_conversions.uom_type_ids, 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: 25kB
  • Buffers: shared hit=1679
3. 0.031 206.069 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,266.91..8,724.31 rows=1 width=498) (actual time=194.049..206.069 rows=1 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, 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=1679
4. 0.005 206.001 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,266.49..8,718.62 rows=1 width=439) (actual time=193.984..206.001 rows=1 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), product_uom_conversions.uom_type_ids, (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
  • Join Filter: ((claim_product.claim_id = claim_product_3.claim_id) AND (claim_product.claim_product_id = claim_product_location.claim_product_id))
  • Buffers: shared hit=1675
5. 0.007 205.948 ↑ 1.0 1 1

Nested Loop Left Join (cost=6,869.18..8,317.99 rows=1 width=407) (actual time=193.932..205.948 rows=1 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), product_uom_conversions.uom_type_ids, ((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=1667
6. 0.013 205.895 ↑ 1.0 1 1

Hash Join (cost=6,866.93..8,279.75 rows=1 width=371) (actual time=193.880..205.895 rows=1 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), product_uom_conversions.uom_type_ids
  • Hash Cond: (claim_product.claim_product_id = product_uom_conversions.claim_product_id)
  • Buffers: shared hit=1660
7. 0.004 205.832 ↑ 106.0 1 1

Nested Loop Left Join (cost=6,641.83..8,054.24 rows=106 width=339) (actual time=193.818..205.832 rows=1 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=1653
8. 0.008 205.812 ↑ 106.0 1 1

Nested Loop Left Join (cost=6,641.83..8,047.88 rows=106 width=347) (actual time=193.799..205.812 rows=1 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)
  • Buffers: shared hit=1653
9. 0.005 205.764 ↑ 106.0 1 1

Nested Loop Left Join (cost=6,640.99..7,913.27 rows=106 width=353) (actual time=193.756..205.764 rows=1 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=1644
10. 0.010 205.710 ↑ 106.0 1 1

Nested Loop Left Join (cost=6,640.15..7,790.88 rows=106 width=365) (actual time=193.704..205.710 rows=1 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
  • Inner Unique: true
  • Buffers: shared hit=1636
11. 3.693 205.673 ↑ 106.0 1 1

Hash Right Join (cost=6,639.87..7,441.08 rows=106 width=319) (actual time=193.669..205.673 rows=1 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, contract_family_1.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version_view.contract_amend_version
  • Hash Cond: (contract_amend_version.contract_amend_version_id = contract_item.contract_id)
  • Buffers: shared hit=1633
12. 12.829 200.043 ↑ 1.0 18,396 1

Hash Join (cost=5,754.27..6,416.46 rows=18,396 width=36) (actual time=148.579..200.043 rows=18,396 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=1539
13. 11.731 117.042 ↑ 1.0 18,396 1

Hash Join (cost=2,858.18..3,472.06 rows=18,396 width=28) (actual time=78.331..117.042 rows=18,396 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=969
14. 10.261 41.616 ↑ 1.0 18,396 1

Hash Join (cost=807.59..1,373.17 rows=18,396 width=52) (actual time=14.579..41.616 rows=18,396 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=667
15. 13.687 26.919 ↑ 1.0 18,396 1

Hash Join (cost=448.79..966.05 rows=18,396 width=64) (actual time=10.103..26.919 rows=18,396 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=436
16. 3.193 3.193 ↑ 1.0 18,396 1

Seq Scan on client_pinnacle.contract_amend_version (cost=0.00..468.96 rows=18,396 width=32) (actual time=0.012..3.193 rows=18,396 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=285
17. 5.708 10.039 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=32) (actual time=10.038..10.039 rows=13,235 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id
  • Buckets: 16384 Batches: 1 Memory Usage: 956kB
  • Buffers: shared hit=151
18. 4.331 4.331 ↑ 1.0 13,235 1

Seq Scan on client_pinnacle.contract_renew_version (cost=0.00..283.35 rows=13,235 width=32) (actual time=0.008..4.331 rows=13,235 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id
  • Buffers: shared hit=151
19. 2.101 4.436 ↑ 1.0 5,680 1

Hash (cost=287.80..287.80 rows=5,680 width=20) (actual time=4.436..4.436 rows=5,680 loops=1)

  • Output: contract_family_1.contract_sequence, contract_family_1.contract_family_id
  • Buckets: 8192 Batches: 1 Memory Usage: 353kB
  • Buffers: shared hit=231
20. 2.335 2.335 ↑ 1.0 5,680 1

Seq Scan on client_pinnacle.contract_family contract_family_1 (cost=0.00..287.80 rows=5,680 width=20) (actual time=0.027..2.335 rows=5,680 loops=1)

  • Output: contract_family_1.contract_sequence, contract_family_1.contract_family_id
  • Buffers: shared hit=231
21. 5.309 63.695 ↑ 1.0 13,235 1

Hash (cost=1,885.15..1,885.15 rows=13,235 width=24) (actual time=63.695..63.695 rows=13,235 loops=1)

  • Output: contract_renew_version_view.contract_renew_version, contract_renew_version_view.contract_renew_version_id
  • Buckets: 16384 Batches: 1 Memory Usage: 852kB
  • Buffers: shared hit=302
22. 3.159 58.386 ↑ 1.0 13,235 1

Subquery Scan on contract_renew_version_view (cost=1,620.45..1,885.15 rows=13,235 width=24) (actual time=49.993..58.386 rows=13,235 loops=1)

  • Output: contract_renew_version_view.contract_renew_version, contract_renew_version_view.contract_renew_version_id
  • Buffers: shared hit=302
23. 19.890 55.227 ↑ 1.0 13,235 1

HashAggregate (cost=1,620.45..1,752.80 rows=13,235 width=24) (actual time=49.991..55.227 rows=13,235 loops=1)

  • Output: crv1.contract_renew_version_id, count(crv2.contract_renew_version_id)
  • Group Key: crv1.contract_renew_version_id
  • Buffers: shared hit=302
24. 22.269 35.337 ↓ 1.8 24,312 1

Hash Left Join (cost=448.79..1,554.28 rows=13,235 width=32) (actual time=10.759..35.337 rows=24,312 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: 32009
  • Buffers: shared hit=302
25. 2.365 2.365 ↑ 1.0 13,235 1

Seq Scan on client_pinnacle.contract_renew_version crv1 (cost=0.00..283.35 rows=13,235 width=40) (actual time=0.016..2.365 rows=13,235 loops=1)

  • Output: crv1.contract_renew_version_id, crv1.contract_family_id, crv1.date_range, crv1.create_timestamp, crv1.dead
  • Buffers: shared hit=151
26. 5.946 10.703 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=40) (actual time=10.703..10.703 rows=13,235 loops=1)

  • Output: crv2.contract_renew_version_id, crv2.contract_family_id, crv2.create_timestamp
  • Buckets: 16384 Batches: 1 Memory Usage: 1059kB
  • Buffers: shared hit=151
27. 4.757 4.757 ↑ 1.0 13,235 1

Seq Scan on client_pinnacle.contract_renew_version crv2 (cost=0.00..283.35 rows=13,235 width=40) (actual time=0.006..4.757 rows=13,235 loops=1)

  • Output: crv2.contract_renew_version_id, crv2.contract_family_id, crv2.create_timestamp
  • Buffers: shared hit=151
28. 7.357 70.172 ↑ 1.0 18,396 1

Hash (cost=2,666.15..2,666.15 rows=18,396 width=24) (actual time=70.172..70.172 rows=18,396 loops=1)

  • Output: contract_amend_version_view.contract_amend_version, contract_amend_version_view.contract_amend_version_id
  • Buckets: 32768 Batches: 1 Memory Usage: 1263kB
  • Buffers: shared hit=570
29. 4.045 62.815 ↑ 1.0 18,396 1

Subquery Scan on contract_amend_version_view (cost=2,298.23..2,666.15 rows=18,396 width=24) (actual time=51.649..62.815 rows=18,396 loops=1)

  • Output: contract_amend_version_view.contract_amend_version, contract_amend_version_view.contract_amend_version_id
  • Buffers: shared hit=570
30. 20.358 58.770 ↑ 1.0 18,396 1

HashAggregate (cost=2,298.23..2,482.19 rows=18,396 width=24) (actual time=51.647..58.770 rows=18,396 loops=1)

  • Output: cav1.contract_amend_version_id, count(cav2.contract_amend_version_id)
  • Group Key: cav1.contract_amend_version_id
  • Buffers: shared hit=570
31. 21.355 38.412 ↓ 1.1 19,996 1

Hash Left Join (cost=698.91..2,206.25 rows=18,396 width=32) (actual time=13.991..38.412 rows=19,996 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: 32398
  • Buffers: shared hit=570
32. 3.137 3.137 ↑ 1.0 18,396 1

Seq Scan on client_pinnacle.contract_amend_version cav1 (cost=0.00..468.96 rows=18,396 width=40) (actual time=0.007..3.137 rows=18,396 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=285
33. 7.116 13.920 ↑ 1.0 18,396 1

Hash (cost=468.96..468.96 rows=18,396 width=40) (actual time=13.920..13.920 rows=18,396 loops=1)

  • Output: cav2.contract_amend_version_id, cav2.contract_renew_version_id, cav2.create_timestamp
  • Buckets: 32768 Batches: 1 Memory Usage: 1550kB
  • Buffers: shared hit=285
34. 6.804 6.804 ↑ 1.0 18,396 1

Seq Scan on client_pinnacle.contract_amend_version cav2 (cost=0.00..468.96 rows=18,396 width=40) (actual time=0.004..6.804 rows=18,396 loops=1)

  • Output: cav2.contract_amend_version_id, cav2.contract_renew_version_id, cav2.create_timestamp
  • Buffers: shared hit=285
35. 0.003 1.937 ↑ 106.0 1 1

Hash (cost=884.27..884.27 rows=106 width=299) (actual time=1.937..1.937 rows=1 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: 9kB
  • Buffers: shared hit=94
36. 0.002 1.934 ↑ 106.0 1 1

Nested Loop Left Join (cost=128.03..884.27 rows=106 width=299) (actual time=1.930..1.934 rows=1 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=94
37. 0.003 1.921 ↑ 106.0 1 1

Nested Loop Left Join (cost=127.61..521.13 rows=106 width=235) (actual time=1.917..1.921 rows=1 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=90
38. 0.015 1.913 ↑ 106.0 1 1

Hash Join (cost=127.32..297.06 rows=106 width=204) (actual time=1.909..1.913 rows=1 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
  • Hash Cond: (claim_product.product_id = product.product_id)
  • Buffers: shared hit=87
39. 0.001 0.056 ↑ 106.0 1 1

Nested Loop (cost=1.69..171.15 rows=106 width=166) (actual time=0.054..0.056 rows=1 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=18
40. 0.002 0.048 ↑ 1.0 1 1

Nested Loop (cost=1.25..7.34 rows=1 width=24) (actual time=0.046..0.048 rows=1 loops=1)

  • Output: claim.claim_id, claim.contract_sequence, claimant_company_type.internal_company_type
  • Inner Unique: true
  • Buffers: shared hit=14
41. 0.002 0.040 ↑ 1.0 1 1

Nested Loop (cost=1.12..7.18 rows=1 width=36) (actual time=0.038..0.040 rows=1 loops=1)

  • Output: claim.claim_id, claim.contract_sequence, claimant_company.company_type_id
  • Inner Unique: true
  • Buffers: shared hit=12
42. 0.002 0.029 ↑ 1.0 1 1

Nested Loop (cost=0.84..6.88 rows=1 width=36) (actual time=0.028..0.029 rows=1 loops=1)

  • Output: claim.claim_id, claim.contract_sequence, invoice.claimant_company_id
  • Inner Unique: true
  • Buffers: shared hit=9
43. 0.014 0.014 ↑ 1.0 1 1

Index Scan using claim_pkey on client_pinnacle.claim (cost=0.42..3.44 rows=1 width=36) (actual time=0.013..0.014 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 = '01d90c64-8da8-49e0-a06d-85998944ec14'::uuid)
  • Buffers: shared hit=5
44. 0.013 0.013 ↑ 1.0 1 1

Index Scan using invoice_pkey on client_pinnacle.invoice (cost=0.42..3.44 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=1)

  • Output: invoice.invoice_id, invoice.claimant_company_id
  • Index Cond: (invoice.invoice_id = claim.invoice_id)
  • Buffers: shared hit=4
45. 0.009 0.009 ↑ 1.0 1 1

Index Scan using company_pkey on client_pinnacle.company claimant_company (cost=0.29..0.31 rows=1 width=32) (actual time=0.009..0.009 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
46. 0.006 0.006 ↑ 1.0 1 1

Index Scan using company_type_pkey on client_pinnacle.company_type claimant_company_type (cost=0.13..0.15 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
47. 0.007 0.007 ↑ 106.0 1 1

Index Scan using claim_product_claim_id_idx on client_pinnacle.claim_product (cost=0.43..162.75 rows=106 width=158) (actual time=0.007..0.007 rows=1 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
  • Index Cond: (claim_product.claim_id = '01d90c64-8da8-49e0-a06d-85998944ec14'::uuid)
  • Buffers: shared hit=4
48. 0.989 1.842 ↑ 1.0 2,517 1

Hash (cost=94.17..94.17 rows=2,517 width=54) (actual time=1.842..1.842 rows=2,517 loops=1)

  • Output: product.product_name, product.product_number, product.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 247kB
  • Buffers: shared hit=69
49. 0.853 0.853 ↑ 1.0 2,517 1

Seq Scan on client_pinnacle.product (cost=0.00..94.17 rows=2,517 width=54) (actual time=0.009..0.853 rows=2,517 loops=1)

  • Output: product.product_name, product.product_number, product.product_id
  • Buffers: shared hit=69
50. 0.005 0.005 ↑ 1.0 1 1

Index Scan using company_pkey on client_pinnacle.company (cost=0.29..2.11 rows=1 width=47) (actual time=0.005..0.005 rows=1 loops=1)

  • 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=3
51. 0.011 0.011 ↑ 1.0 1 1

Index Scan using contract_item_pkey on client_pinnacle.contract_item (cost=0.42..3.43 rows=1 width=80) (actual time=0.010..0.011 rows=1 loops=1)

  • 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)
  • Buffers: shared hit=4
52. 0.027 0.027 ↑ 1.0 1 1

Index Scan using contract_family_unique on client_pinnacle.contract_family (cost=0.28..3.30 rows=1 width=54) (actual time=0.027..0.027 rows=1 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
  • Index Cond: (contract_family.contract_sequence = claim.contract_sequence)
  • Buffers: shared hit=3
53. 0.005 0.049 ↑ 1.0 1 1

Nested Loop (cost=0.84..1.14 rows=1 width=20) (actual time=0.047..0.049 rows=1 loops=1)

  • Output: c.contract_family_id, ct.internal_company_type
  • Inner Unique: true
  • Buffers: shared hit=8
54. 0.004 0.035 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.71..0.92 rows=1 width=48) (actual time=0.033..0.035 rows=1 loops=1)

  • Output: c.contract_family_id, gc.company_type_id, cmp.company_type_id
  • Inner Unique: true
  • Buffers: shared hit=6
55. 0.004 0.019 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.42..0.54 rows=1 width=48) (actual time=0.017..0.019 rows=1 loops=1)

  • Output: c.contractee_company_id, c.contract_family_id, gc.company_type_id
  • Inner Unique: true
  • Buffers: shared hit=3
56. 0.011 0.011 ↑ 1.0 1 1

Index Scan using contract_family_pkey on client_pinnacle.contract_family c (cost=0.28..0.38 rows=1 width=48) (actual time=0.010..0.011 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.import_contract_number, c.accrual_quantity_source
  • Index Cond: (contract_family.contract_family_id = c.contract_family_id)
  • Buffers: shared hit=3
57. 0.004 0.004 ↓ 0.0 0 1

Index Scan using grouping_company_pkey on client_pinnacle.grouping_company gc (cost=0.14..0.16 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)

  • 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)
58. 0.012 0.012 ↑ 1.0 1 1

Index Scan using company_pkey on client_pinnacle.company cmp (cost=0.29..0.38 rows=1 width=32) (actual time=0.011..0.012 rows=1 loops=1)

  • 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)
  • Buffers: shared hit=3
59. 0.009 0.009 ↑ 1.0 1 1

Index Scan using company_type_pkey on client_pinnacle.company_type ct (cost=0.13..0.19 rows=1 width=20) (actual time=0.009..0.009 rows=1 loops=1)

  • 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))
  • Buffers: shared hit=2
60. 0.005 0.040 ↑ 1.0 1 1

Nested Loop (cost=0.84..1.26 rows=1 width=58) (actual time=0.038..0.040 rows=1 loops=1)

  • 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=9
61. 0.005 0.030 ↑ 1.0 1 1

Nested Loop (cost=0.56..0.92 rows=1 width=53) (actual time=0.028..0.030 rows=1 loops=1)

  • Output: pro.product_id, suomc.rate, suomc.product_id, suomc.uom_type_id
  • Buffers: shared hit=6
62. 0.013 0.013 ↑ 1.0 1 1

Index Only Scan using product_pkey on client_pinnacle.product pro (cost=0.28..0.59 rows=1 width=16) (actual time=0.012..0.013 rows=1 loops=1)

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

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

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
  • Index Cond: (suomc.product_id = pro.product_id)
  • Buffers: shared hit=3
64. 0.005 0.005 ↑ 1.0 1 1

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

  • Output: tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Index Cond: (tuomc.product_id = pro.product_id)
  • Buffers: shared hit=3
65. 0.016 0.016 ↑ 1.0 1 1

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

  • 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
66. 0.003 0.050 ↑ 106.0 1 1

Hash (cost=223.77..223.77 rows=106 width=64) (actual time=0.050..0.050 rows=1 loops=1)

  • Output: product_uom_conversions.uom_type_ids, product_uom_conversions.claim_product_id, product_uom_conversions.claim_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=7
67. 0.000 0.047 ↑ 106.0 1 1

Subquery Scan on product_uom_conversions (cost=221.39..223.77 rows=106 width=64) (actual time=0.046..0.047 rows=1 loops=1)

  • Output: product_uom_conversions.uom_type_ids, product_uom_conversions.claim_product_id, product_uom_conversions.claim_id
  • Buffers: shared hit=7
68. 0.013 0.047 ↑ 106.0 1 1

HashAggregate (cost=221.39..222.71 rows=106 width=64) (actual time=0.046..0.047 rows=1 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=7
69. 0.003 0.034 ↑ 108.0 1 1

Nested Loop (cost=0.71..220.85 rows=108 width=48) (actual time=0.032..0.034 rows=1 loops=1)

  • Output: claim_product_2.claim_product_id, claim_product_2.claim_id, product_uom_conversion.uom_type_id
  • Buffers: shared hit=7
70. 0.019 0.019 ↑ 106.0 1 1

Index Scan using claim_product_claim_id_idx on client_pinnacle.claim_product claim_product_2 (cost=0.43..162.75 rows=106 width=48) (actual time=0.018..0.019 rows=1 loops=1)

  • Output: claim_product_2.claim_id, claim_product_2.claim_product_id, claim_product_2.product_id
  • Index Cond: (claim_product_2.claim_id = '01d90c64-8da8-49e0-a06d-85998944ec14'::uuid)
  • Buffers: shared hit=4
71. 0.012 0.012 ↑ 1.0 1 1

Index Only Scan using product_uom_conversion_pkey on client_pinnacle.product_uom_conversion (cost=0.28..0.54 rows=1 width=32) (actual time=0.011..0.012 rows=1 loops=1)

  • 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=3
72. 0.002 0.046 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.26..38.24 rows=1 width=68) (actual time=0.046..0.046 rows=0 loops=1)

  • 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=7
73. 0.002 0.044 ↓ 0.0 0 1

Nested Loop (cost=1.98..37.91 rows=1 width=70) (actual time=0.044..0.044 rows=0 loops=1)

  • 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=7
74. 0.002 0.042 ↓ 0.0 0 1

Nested Loop (cost=1.70..37.60 rows=1 width=129) (actual time=0.042..0.042 rows=0 loops=1)

  • 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
  • Join Filter: (suomc_1.product_id = pro_1.product_id)
  • Buffers: shared hit=7
75. 0.002 0.040 ↓ 0.0 0 1

Nested Loop (cost=1.42..37.29 rows=1 width=113) (actual time=0.040..0.040 rows=0 loops=1)

  • 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=7
76. 0.001 0.038 ↓ 0.0 0 1

Nested Loop (cost=1.14..8.48 rows=96 width=108) (actual time=0.038..0.038 rows=0 loops=1)

  • 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=7
77. 0.004 0.037 ↓ 0.0 0 1

Nested Loop (cost=0.86..6.90 rows=1 width=64) (actual time=0.037..0.037 rows=0 loops=1)

  • 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=7
78. 0.015 0.015 ↑ 1.0 1 1

Index Scan using claim_product_pkey on client_pinnacle.claim_product claim_product_1 (cost=0.43..3.46 rows=1 width=48) (actual time=0.014..0.015 rows=1 loops=1)

  • 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 = '01d90c64-8da8-49e0-a06d-85998944ec14'::uuid) AND (claim_product.claim_id = claim_product_1.claim_id))
  • Buffers: shared hit=4
79. 0.018 0.018 ↓ 0.0 0 1

Index Scan using claim_product_price_unique on client_pinnacle.claim_product_price (cost=0.43..3.45 rows=1 width=32) (actual time=0.018..0.018 rows=0 loops=1)

  • 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=3
80. 0.000 0.000 ↓ 0.0 0

Append (cost=0.28..1.53 rows=5 width=60) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Index Scan using price_default_pkey on client_pinnacle.price_default (cost=0.28..0.30 rows=1 width=58) (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)
82. 0.000 0.000 ↓ 0.0 0

Index Scan using price_generated_pkey on client_pinnacle.price_sale (cost=0.43..0.47 rows=1 width=60) (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)
83. 0.000 0.000 ↓ 0.0 0

Index Scan using price_company_pkey on client_pinnacle.price_company (cost=0.29..0.30 rows=1 width=58) (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)
84. 0.000 0.000 ↓ 0.0 0

Index Scan using price_list_pkey on client_pinnacle.price_list (cost=0.15..0.17 rows=1 width=84) (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)
85. 0.000 0.000 ↓ 0.0 0

Index Scan using price_class_pkey on client_pinnacle.price_class (cost=0.28..0.30 rows=1 width=58) (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)
86. 0.000 0.000 ↓ 0.0 0

Index Scan using product_uom_conversion_pkey on client_pinnacle.product_uom_conversion suomc_1 (cost=0.28..0.30 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))
87. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using product_uom_conversion_pkey on client_pinnacle.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)
89. 0.000 0.000 ↓ 0.0 0

Index Scan using price_modified_excl on client_pinnacle.price_modified pm (cost=0.28..0.30 rows=1 width=27) (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)
90. 0.002 0.048 ↓ 0.0 0 1

GroupAggregate (cost=397.31..398.88 rows=70 width=64) (actual time=0.047..0.048 rows=0 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=8
91. 0.018 0.046 ↓ 0.0 0 1

Sort (cost=397.31..397.48 rows=70 width=33) (actual time=0.046..0.046 rows=0 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=8
92. 0.002 0.028 ↓ 0.0 0 1

Nested Loop (cost=0.99..395.16 rows=70 width=33) (actual time=0.028..0.028 rows=0 loops=1)

  • Output: claim_product_3.claim_id, claim_product_location.claim_product_id, claim_product_location.distributor_invoice_number
  • Buffers: shared hit=8
93. 0.014 0.014 ↑ 106.0 1 1

Index Scan using claim_product_claim_id_idx on client_pinnacle.claim_product claim_product_3 (cost=0.43..162.75 rows=106 width=32) (actual time=0.014..0.014 rows=1 loops=1)

  • Output: claim_product_3.claim_id, claim_product_3.claim_product_id
  • Index Cond: (claim_product_3.claim_id = '01d90c64-8da8-49e0-a06d-85998944ec14'::uuid)
  • Buffers: shared hit=4
94. 0.012 0.012 ↓ 0.0 0 1

Index Only Scan using claim_product_location_pkey on client_pinnacle.claim_product_location (cost=0.56..2.14 rows=5 width=17) (actual time=0.012..0.012 rows=0 loops=1)

  • 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=4
95. 0.007 0.037 ↑ 1.0 1 1

Nested Loop (cost=0.42..5.58 rows=1 width=40) (actual time=0.035..0.037 rows=1 loops=1)

  • 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=4
96. 0.013 0.013 ↑ 1.0 1 1

Index Scan using claim_product_order_pkey on client_pinnacle.claim_product_order (cost=0.42..3.33 rows=1 width=144) (actual time=0.012..0.013 rows=1 loops=1)

  • 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 = '01d90c64-8da8-49e0-a06d-85998944ec14'::uuid))
  • Buffers: shared hit=4
97. 0.017 0.017 ↑ 100.0 1 1

Function Scan on pg_catalog.unnest cpo (cost=0.00..1.00 rows=100 width=24) (actual time=0.017..0.017 rows=1 loops=1)

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