explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BnkR

Settings
# exclusive inclusive rows x rows loops node
1. 9.611 2,289.600 ↓ 157.3 26,424 1

Subquery Scan on claim_select_product (cost=199,350.93..199,353.03 rows=168 width=490) (actual time=2,269.477..2,289.600 rows=26,424 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=966446, temp read=6253 written=6238
2. 75.031 2,279.989 ↓ 157.3 26,424 1

Sort (cost=199,350.93..199,351.35 rows=168 width=498) (actual time=2,269.472..2,279.989 rows=26,424 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, cpl.distributor_invoice_number)), ((cpo.display_order)::integer), cpo.display_order
  • Sort Key: cpo.display_order
  • Sort Method: external merge Disk: 9120kB
  • Buffers: shared hit=966446, temp read=6253 written=6238
3. 68.709 2,204.958 ↓ 157.3 26,424 1

Hash Left Join (cost=190,578.62..199,344.72 rows=168 width=498) (actual time=946.322..2,204.958 rows=26,424 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, cpl.distributor_invoice_number), (cpo.display_order)::integer, cpo.display_order
  • Hash Cond: ((claim_product.claim_id = claim_product_order.claim_id) AND (claim_product.claim_product_id = cpo.claim_product_id))
  • Buffers: shared hit=966446, temp read=5113 written=5097
4. 26.029 2,121.980 ↓ 157.3 26,424 1

Hash Left Join (cost=190,571.68..199,320.55 rows=168 width=439) (actual time=932.015..2,121.980 rows=26,424 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, cpl.distributor_invoice_number, ((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
  • Hash Cond: ((claim_product.claim_id = cpl.claim_id) AND (claim_product.claim_product_id = cpl.claim_product_id))
  • Buffers: shared hit=966385, temp read=5113 written=5097
5. 25.840 1,940.135 ↓ 157.3 26,424 1

Nested Loop Left Join (cost=112,587.22..121,335.21 rows=168 width=407) (actual time=776.135..1,940.135 rows=26,424 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=839328, temp read=5113 written=5097
6. 46.904 857.335 ↓ 157.3 26,424 1

Hash Join (cost=112,584.97..115,349.56 rows=168 width=371) (actual time=775.998..857.335 rows=26,424 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: (product_uom_conversions.claim_product_id = claim_product.claim_product_id)
  • Buffers: shared hit=58139, temp read=5113 written=5097
7. 4.954 110.283 ↑ 1.0 26,424 1

Subquery Scan on product_uom_conversions (cost=34,245.75..35,075.40 rows=27,537 width=64) (actual time=75.724..110.283 rows=26,424 loops=1)

  • Output: product_uom_conversions.uom_type_ids, product_uom_conversions.claim_product_id, product_uom_conversions.claim_id
  • Buffers: shared hit=20996
8. 26.493 105.329 ↑ 1.0 26,424 1

GroupAggregate (cost=34,245.75..34,800.03 rows=27,537 width=64) (actual time=75.722..105.329 rows=26,424 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=20996
9. 17.269 78.836 ↑ 1.0 27,472 1

Sort (cost=34,245.75..34,315.77 rows=28,009 width=48) (actual time=75.701..78.836 rows=27,472 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: 2915kB
  • Buffers: shared hit=20996
10. 14.840 61.567 ↑ 1.0 27,472 1

Hash Join (cost=467.00..32,176.78 rows=28,009 width=48) (actual time=7.886..61.567 rows=27,472 loops=1)

  • Output: claim_product_2.claim_product_id, claim_product_2.claim_id, product_uom_conversion.uom_type_id
  • Hash Cond: (claim_product_2.product_id = product_uom_conversion.product_id)
  • Buffers: shared hit=20996
11. 42.478 45.947 ↑ 1.0 26,424 1

Bitmap Heap Scan on client_pinnacle.claim_product claim_product_2 (cost=389.34..31,681.35 rows=27,537 width=48) (actual time=7.092..45.947 rows=26,424 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 = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Heap Blocks: exact=20870
  • Buffers: shared hit=20975
12. 3.469 3.469 ↑ 1.0 26,424 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..382.46 rows=27,537 width=0) (actual time=3.469..3.469 rows=26,424 loops=1)

  • Index Cond: (claim_product_2.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=105
13. 0.417 0.780 ↑ 1.0 2,518 1

Hash (cost=46.18..46.18 rows=2,518 width=32) (actual time=0.780..0.780 rows=2,518 loops=1)

  • Output: product_uom_conversion.uom_type_id, product_uom_conversion.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 190kB
  • Buffers: shared hit=21
14. 0.363 0.363 ↑ 1.0 2,518 1

Seq Scan on client_pinnacle.product_uom_conversion (cost=0.00..46.18 rows=2,518 width=32) (actual time=0.009..0.363 rows=2,518 loops=1)

  • Output: product_uom_conversion.uom_type_id, product_uom_conversion.product_id
  • Buffers: shared hit=21
15. 27.806 700.148 ↑ 1.0 26,424 1

Hash (cost=76,757.01..76,757.01 rows=27,537 width=339) (actual time=700.148..700.148 rows=26,424 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)
  • Buckets: 32768 Batches: 2 Memory Usage: 5130kB
  • Buffers: shared hit=37143, temp read=4385 written=4952
16. 28.184 672.342 ↑ 1.0 26,424 1

Nested Loop Left Join (cost=36,089.81..76,757.01 rows=27,537 width=339) (actual time=445.560..672.342 rows=26,424 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=37143, temp read=4385 written=4371
17. 12.958 644.158 ↑ 1.0 26,424 1

Hash Left Join (cost=36,089.81..75,104.79 rows=27,537 width=347) (actual time=445.540..644.158 rows=26,424 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
  • Hash Cond: (contract_item.contract_id = contract_amend_version.contract_amend_version_id)
  • Buffers: shared hit=37143, temp read=4385 written=4371
18. 16.672 517.399 ↑ 1.0 26,424 1

Hash Left Join (cost=29,443.40..68,079.75 rows=27,537 width=327) (actual time=331.674..517.399 rows=26,424 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
  • Hash Cond: ((claim_product.product_id = pro.product_id) AND (contract_item.uom_type_id = suomc.uom_type_id) AND (claim_product.uom_type_id = tuomc.uom_type_id))
  • Buffers: shared hit=35604, temp read=4385 written=4371
19. 11.691 496.370 ↑ 1.0 26,424 1

Hash Left Join (cost=29,133.23..67,184.42 rows=27,537 width=333) (actual time=327.288..496.370 rows=26,424 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
  • Hash Cond: (contract_family.contract_family_id = c.contract_family_id)
  • Buffers: shared hit=35541, temp read=4385 written=4371
20. 12.386 473.718 ↑ 1.0 26,424 1

Hash Left Join (cost=28,301.33..66,245.13 rows=27,537 width=345) (actual time=316.308..473.718 rows=26,424 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
  • Inner Unique: true
  • Hash Cond: (claim.contract_sequence = contract_family.contract_sequence)
  • Buffers: shared hit=35052, temp read=4385 written=4371
21. 12.719 458.658 ↑ 1.0 26,424 1

Hash Left Join (cost=27,942.53..65,814.00 rows=27,537 width=299) (actual time=313.601..458.658 rows=26,424 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
  • Hash Cond: (claim_product.distributor_company_id = company.company_id)
  • Buffers: shared hit=34821, temp read=4385 written=4371
22. 11.834 440.894 ↑ 1.0 26,424 1

Hash Join (cost=27,450.89..65,250.04 rows=27,537 width=268) (actual time=308.504..440.894 rows=26,424 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, 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
  • Hash Cond: (claim_product.product_id = product.product_id)
  • Buffers: shared hit=34567, temp read=4385 written=4371
23. 6.517 427.165 ↑ 1.0 26,424 1

Nested Loop (cost=27,325.26..65,052.00 rows=27,537 width=230) (actual time=306.591..427.165 rows=26,424 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, 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
  • Buffers: shared hit=34498, temp read=4385 written=4371
24. 0.003 0.069 ↑ 1.0 1 1

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

  • Output: claim.claim_id, claim.contract_sequence, claimant_company_type.internal_company_type
  • Inner Unique: true
  • Buffers: shared hit=13
25. 0.004 0.059 ↑ 1.0 1 1

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

  • Output: claim.claim_id, claim.contract_sequence, claimant_company.company_type_id
  • Inner Unique: true
  • Buffers: shared hit=11
26. 0.005 0.045 ↑ 1.0 1 1

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

  • Output: claim.claim_id, claim.contract_sequence, invoice.claimant_company_id
  • Inner Unique: true
  • Buffers: shared hit=8
27. 0.027 0.027 ↑ 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.020..0.027 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 = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=4
28. 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
29. 0.010 0.010 ↑ 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.010..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
30. 0.007 0.007 ↑ 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.007..0.007 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
31. 72.321 420.579 ↑ 1.0 26,424 1

Hash Left Join (cost=27,324.00..64,769.29 rows=27,537 width=222) (actual time=306.520..420.579 rows=26,424 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, 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
  • Hash Cond: (claim_product.contract_item_id = contract_item.contract_item_id)
  • Buffers: shared hit=34485, temp read=4385 written=4371
32. 46.507 51.150 ↑ 1.0 26,424 1

Bitmap Heap Scan on client_pinnacle.claim_product (cost=389.34..31,681.35 rows=27,537 width=158) (actual time=9.232..51.150 rows=26,424 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 = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Heap Blocks: exact=20870
  • Buffers: shared hit=20975
33. 4.643 4.643 ↑ 1.0 26,424 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..382.46 rows=27,537 width=0) (actual time=4.643..4.643 rows=26,424 loops=1)

  • Index Cond: (claim_product.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=105
34. 166.519 297.108 ↑ 1.0 381,407 1

Hash (cost=17,324.07..17,324.07 rows=381,407 width=80) (actual time=297.108..297.108 rows=381,407 loops=1)

  • Output: 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.contract_item_id, contract_item.uom_type_id
  • Buckets: 131072 Batches: 8 Memory Usage: 6493kB
  • Buffers: shared hit=13510, temp written=4266
35. 130.589 130.589 ↑ 1.0 381,407 1

Seq Scan on client_pinnacle.contract_item (cost=0.00..17,324.07 rows=381,407 width=80) (actual time=5.861..130.589 rows=381,407 loops=1)

  • Output: 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.contract_item_id, contract_item.uom_type_id
  • Buffers: shared hit=13510
36. 1.020 1.895 ↑ 1.0 2,517 1

Hash (cost=94.17..94.17 rows=2,517 width=54) (actual time=1.895..1.895 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
37. 0.875 0.875 ↑ 1.0 2,517 1

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

  • Output: product.product_name, product.product_number, product.product_id
  • Buffers: shared hit=69
38. 2.566 5.045 ↑ 1.0 10,562 1

Hash (cost=359.62..359.62 rows=10,562 width=47) (actual time=5.045..5.045 rows=10,562 loops=1)

  • Output: company.company_name, company.company_number, company.company_id
  • Buckets: 16384 Batches: 1 Memory Usage: 956kB
  • Buffers: shared hit=254
39. 2.479 2.479 ↑ 1.0 10,562 1

Seq Scan on client_pinnacle.company (cost=0.00..359.62 rows=10,562 width=47) (actual time=0.011..2.479 rows=10,562 loops=1)

  • Output: company.company_name, company.company_number, company.company_id
  • Buffers: shared hit=254
40. 1.248 2.674 ↑ 1.0 5,680 1

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

  • Output: contract_family.contract_title, contract_family.contract_type, contract_family.contract_sequence, contract_family.contract_family_id
  • Buckets: 8192 Batches: 1 Memory Usage: 550kB
  • Buffers: shared hit=231
41. 1.426 1.426 ↑ 1.0 5,680 1

Seq Scan on client_pinnacle.contract_family (cost=0.00..287.80 rows=5,680 width=54) (actual time=0.017..1.426 rows=5,680 loops=1)

  • Output: contract_family.contract_title, contract_family.contract_type, contract_family.contract_sequence, contract_family.contract_family_id
  • Buffers: shared hit=231
42. 1.072 10.961 ↓ 66.8 5,680 1

Hash (cost=830.84..830.84 rows=85 width=20) (actual time=10.961..10.961 rows=5,680 loops=1)

  • Output: c.contract_family_id, ct.internal_company_type
  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 353kB
  • Buffers: shared hit=489
43. 1.673 9.889 ↓ 66.8 5,680 1

Hash Join (cost=497.96..830.84 rows=85 width=20) (actual time=4.424..9.889 rows=5,680 loops=1)

  • Output: c.contract_family_id, ct.internal_company_type
  • Inner Unique: true
  • Hash Cond: (COALESCE(cmp.company_type_id, gc.company_type_id) = ct.company_type_id)
  • Buffers: shared hit=489
44. 1.767 8.206 ↑ 1.0 5,680 1

Hash Left Join (cost=496.89..814.55 rows=5,680 width=48) (actual time=4.405..8.206 rows=5,680 loops=1)

  • Output: c.contract_family_id, gc.company_type_id, cmp.company_type_id
  • Inner Unique: true
  • Hash Cond: (c.contractee_company_id = cmp.company_id)
  • Buffers: shared hit=488
45. 1.368 2.111 ↑ 1.0 5,680 1

Hash Left Join (cost=5.25..307.98 rows=5,680 width=48) (actual time=0.048..2.111 rows=5,680 loops=1)

  • Output: c.contractee_company_id, c.contract_family_id, gc.company_type_id
  • Inner Unique: true
  • Hash Cond: (c.contractee_grouping_id = gc.grouping_id)
  • Buffers: shared hit=234
46. 0.709 0.709 ↑ 1.0 5,680 1

Seq Scan on client_pinnacle.contract_family c (cost=0.00..287.80 rows=5,680 width=48) (actual time=0.006..0.709 rows=5,680 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
  • Buffers: shared hit=231
47. 0.010 0.034 ↑ 2.0 50 1

Hash (cost=4.00..4.00 rows=100 width=32) (actual time=0.034..0.034 rows=50 loops=1)

  • Output: gc.grouping_id, gc.company_type_id
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=3
48. 0.024 0.024 ↑ 2.0 50 1

Seq Scan on client_pinnacle.grouping_company gc (cost=0.00..4.00 rows=100 width=32) (actual time=0.008..0.024 rows=50 loops=1)

  • Output: gc.grouping_id, gc.company_type_id
  • Buffers: shared hit=3
49. 1.979 4.328 ↑ 1.0 10,562 1

Hash (cost=359.62..359.62 rows=10,562 width=32) (actual time=4.328..4.328 rows=10,562 loops=1)

  • Output: cmp.company_id, cmp.company_type_id
  • Buckets: 16384 Batches: 1 Memory Usage: 789kB
  • Buffers: shared hit=254
50. 2.349 2.349 ↑ 1.0 10,562 1

Seq Scan on client_pinnacle.company cmp (cost=0.00..359.62 rows=10,562 width=32) (actual time=0.004..2.349 rows=10,562 loops=1)

  • Output: cmp.company_id, cmp.company_type_id
  • Buffers: shared hit=254
51. 0.002 0.010 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=20) (actual time=0.010..0.010 rows=3 loops=1)

  • Output: ct.internal_company_type, ct.company_type_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
52. 0.008 0.008 ↑ 1.0 3 1

Seq Scan on client_pinnacle.company_type ct (cost=0.00..1.03 rows=3 width=20) (actual time=0.007..0.008 rows=3 loops=1)

  • Output: ct.internal_company_type, ct.company_type_id
  • Buffers: shared hit=1
53. 0.819 4.357 ↓ 1.0 2,520 1

Hash (cost=266.09..266.09 rows=2,519 width=58) (actual time=4.357..4.357 rows=2,520 loops=1)

  • Output: pro.product_id, suomc.rate, suomc.uom_type_id, tuomc.rate, tuomc.uom_type_id
  • Buckets: 4096 Batches: 1 Memory Usage: 254kB
  • Buffers: shared hit=63
54. 0.948 3.538 ↓ 1.0 2,520 1

Hash Join (cost=185.27..266.09 rows=2,519 width=58) (actual time=2.375..3.538 rows=2,520 loops=1)

  • Output: pro.product_id, suomc.rate, suomc.uom_type_id, tuomc.rate, tuomc.uom_type_id
  • Hash Cond: (tuomc.product_id = pro.product_id)
  • Buffers: shared hit=63
55. 0.238 0.238 ↑ 1.0 2,518 1

Seq Scan on client_pinnacle.product_uom_conversion tuomc (cost=0.00..46.18 rows=2,518 width=37) (actual time=0.013..0.238 rows=2,518 loops=1)

  • Output: tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Buffers: shared hit=21
56. 0.526 2.352 ↑ 1.0 2,518 1

Hash (cost=153.80..153.80 rows=2,518 width=53) (actual time=2.352..2.352 rows=2,518 loops=1)

  • Output: pro.product_id, suomc.rate, suomc.product_id, suomc.uom_type_id
  • Buckets: 4096 Batches: 1 Memory Usage: 242kB
  • Buffers: shared hit=42
57. 0.871 1.826 ↑ 1.0 2,518 1

Hash Join (cost=101.00..153.80 rows=2,518 width=53) (actual time=0.760..1.826 rows=2,518 loops=1)

  • Output: pro.product_id, suomc.rate, suomc.product_id, suomc.uom_type_id
  • Inner Unique: true
  • Hash Cond: (suomc.product_id = pro.product_id)
  • Buffers: shared hit=42
58. 0.211 0.211 ↑ 1.0 2,518 1

Seq Scan on client_pinnacle.product_uom_conversion suomc (cost=0.00..46.18 rows=2,518 width=37) (actual time=0.003..0.211 rows=2,518 loops=1)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
  • Buffers: shared hit=21
59. 0.377 0.744 ↑ 1.0 2,517 1

Hash (cost=69.53..69.53 rows=2,517 width=16) (actual time=0.744..0.744 rows=2,517 loops=1)

  • Output: pro.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=21
60. 0.367 0.367 ↑ 1.0 2,517 1

Index Only Scan using product_pkey on client_pinnacle.product pro (cost=0.28..69.53 rows=2,517 width=16) (actual time=0.020..0.367 rows=2,517 loops=1)

  • Output: pro.product_id
  • Heap Fetches: 0
  • Buffers: shared hit=21
61. 4.326 113.801 ↑ 1.0 18,396 1

Hash (cost=6,416.46..6,416.46 rows=18,396 width=36) (actual time=113.801..113.801 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
  • Buckets: 32768 Batches: 1 Memory Usage: 1550kB
  • Buffers: shared hit=1539
62. 8.021 109.475 ↑ 1.0 18,396 1

Hash Join (cost=5,754.27..6,416.46 rows=18,396 width=36) (actual time=79.784..109.475 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
63. 6.364 61.538 ↑ 1.0 18,396 1

Hash Join (cost=2,858.18..3,472.06 rows=18,396 width=28) (actual time=39.815..61.538 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
64. 5.455 23.365 ↑ 1.0 18,396 1

Hash Join (cost=807.59..1,373.17 rows=18,396 width=52) (actual time=7.959..23.365 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
65. 8.133 14.719 ↑ 1.0 18,396 1

Hash Join (cost=448.79..966.05 rows=18,396 width=64) (actual time=4.735..14.719 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
66. 1.905 1.905 ↑ 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.010..1.905 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
67. 2.407 4.681 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=32) (actual time=4.681..4.681 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
68. 2.274 2.274 ↑ 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.007..2.274 rows=13,235 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id
  • Buffers: shared hit=151
69. 1.466 3.191 ↑ 1.0 5,680 1

Hash (cost=287.80..287.80 rows=5,680 width=20) (actual time=3.191..3.191 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
70. 1.725 1.725 ↑ 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.028..1.725 rows=5,680 loops=1)

  • Output: contract_family_1.contract_sequence, contract_family_1.contract_family_id
  • Buffers: shared hit=231
71. 2.453 31.809 ↑ 1.0 13,235 1

Hash (cost=1,885.15..1,885.15 rows=13,235 width=24) (actual time=31.809..31.809 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
72. 1.901 29.356 ↑ 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=24.708..29.356 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
73. 10.432 27.455 ↑ 1.0 13,235 1

HashAggregate (cost=1,620.45..1,752.80 rows=13,235 width=24) (actual time=24.706..27.455 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
74. 10.994 17.023 ↓ 1.8 24,312 1

Hash Left Join (cost=448.79..1,554.28 rows=13,235 width=32) (actual time=4.892..17.023 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
75. 1.180 1.180 ↑ 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.008..1.180 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
76. 2.482 4.849 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=40) (actual time=4.849..4.849 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
77. 2.367 2.367 ↑ 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.002..2.367 rows=13,235 loops=1)

  • Output: crv2.contract_renew_version_id, crv2.contract_family_id, crv2.create_timestamp
  • Buffers: shared hit=151
78. 3.951 39.916 ↑ 1.0 18,396 1

Hash (cost=2,666.15..2,666.15 rows=18,396 width=24) (actual time=39.916..39.916 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
79. 2.802 35.965 ↑ 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=28.939..35.965 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
80. 11.802 33.163 ↑ 1.0 18,396 1

HashAggregate (cost=2,298.23..2,482.19 rows=18,396 width=24) (actual time=28.937..33.163 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
81. 11.576 21.361 ↓ 1.1 19,996 1

Hash Left Join (cost=698.91..2,206.25 rows=18,396 width=32) (actual time=8.201..21.361 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
82. 1.648 1.648 ↑ 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.008..1.648 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
83. 3.819 8.137 ↑ 1.0 18,396 1

Hash (cost=468.96..468.96 rows=18,396 width=40) (actual time=8.137..8.137 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
84. 4.318 4.318 ↑ 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.003..4.318 rows=18,396 loops=1)

  • Output: cav2.contract_amend_version_id, cav2.contract_renew_version_id, cav2.create_timestamp
  • Buffers: shared hit=285
85. 0.000 0.000 ↑ 1.0 1 26,424

Result (cost=0.00..0.04 rows=1 width=24) (actual time=0.000..0.000 rows=1 loops=26,424)

  • 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
86. 32.604 1,056.960 ↑ 1.0 1 26,424

Nested Loop Left Join (cost=2.26..35.62 rows=1 width=68) (actual time=0.032..0.040 rows=1 loops=26,424)

  • 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=781189
87. 26.939 713.448 ↑ 1.0 1 26,424

Nested Loop (cost=1.98..35.29 rows=1 width=70) (actual time=0.019..0.027 rows=1 loops=26,424)

  • 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))
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=703462
88. 27.454 660.600 ↑ 1.0 1 26,424

Nested Loop (cost=1.70..34.98 rows=1 width=129) (actual time=0.016..0.025 rows=1 loops=26,424)

  • 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=625735
89. 27.454 581.328 ↑ 1.0 1 26,424

Nested Loop (cost=1.42..34.67 rows=1 width=113) (actual time=0.014..0.022 rows=1 loops=26,424)

  • 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=573916
90. 5.150 502.056 ↑ 96.0 1 26,424

Nested Loop (cost=1.14..5.87 rows=96 width=108) (actual time=0.011..0.019 rows=1 loops=26,424)

  • 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=496189
91. 52.848 237.816 ↑ 1.0 1 26,424

Nested Loop (cost=0.86..4.28 rows=1 width=64) (actual time=0.008..0.009 rows=1 loops=26,424)

  • 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=211190
92. 105.696 105.696 ↑ 1.0 1 26,424

Index Scan using claim_product_pkey on client_pinnacle.claim_product claim_product_1 (cost=0.43..2.62 rows=1 width=48) (actual time=0.004..0.004 rows=1 loops=26,424)

  • 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 = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid) AND (claim_product.claim_id = claim_product_1.claim_id))
  • Buffers: shared hit=105905
93. 79.272 79.272 ↑ 1.0 1 26,424

Index Scan using claim_product_price_unique on client_pinnacle.claim_product_price (cost=0.43..1.66 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=26,424)

  • 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=105285
94. 77.727 259.090 ↑ 5.0 1 25,909

Append (cost=0.28..1.53 rows=5 width=60) (actual time=0.002..0.010 rows=1 loops=25,909)

  • Buffers: shared hit=284999
95. 51.818 51.818 ↑ 1.0 1 25,909

Index Scan using price_default_pkey on client_pinnacle.price_default (cost=0.28..0.30 rows=1 width=58) (actual time=0.002..0.002 rows=1 loops=25,909)

  • 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)
  • Buffers: shared hit=77727
96. 51.818 51.818 ↓ 0.0 0 25,909

Index Scan using price_generated_pkey on client_pinnacle.price_sale (cost=0.43..0.47 rows=1 width=60) (actual time=0.002..0.002 rows=0 loops=25,909)

  • 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)
  • Buffers: shared hit=77727
97. 51.818 51.818 ↓ 0.0 0 25,909

Index Scan using price_company_pkey on client_pinnacle.price_company (cost=0.29..0.30 rows=1 width=58) (actual time=0.002..0.002 rows=0 loops=25,909)

  • 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)
  • Buffers: shared hit=51818
98. 0.000 0.000 ↓ 0.0 0 25,909

Index Scan using price_list_pkey on client_pinnacle.price_list (cost=0.15..0.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=25,909)

  • 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)
  • Buffers: shared hit=25909
99. 25.909 25.909 ↓ 0.0 0 25,909

Index Scan using price_class_pkey on client_pinnacle.price_class (cost=0.28..0.30 rows=1 width=58) (actual time=0.001..0.001 rows=0 loops=25,909)

  • 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)
  • Buffers: shared hit=51818
100. 51.818 51.818 ↑ 1.0 1 25,909

Index Scan using product_uom_conversion_pkey on client_pinnacle.product_uom_conversion suomc_1 (cost=0.28..0.30 rows=1 width=37) (actual time=0.002..0.002 rows=1 loops=25,909)

  • 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))
  • Buffers: shared hit=77727
101. 51.818 51.818 ↑ 1.0 1 25,909

Index Only Scan using product_pkey on client_pinnacle.product pro_1 (cost=0.28..0.30 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=25,909)

  • Output: pro_1.product_id
  • Index Cond: (pro_1.product_id = price_default.product_id)
  • Heap Fetches: 0
  • Buffers: shared hit=51819
102. 25.909 25.909 ↑ 1.0 1 25,909

Index Scan using product_uom_conversion_pkey on client_pinnacle.product_uom_conversion tuomc_1 (cost=0.28..0.30 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=25,909)

  • Output: tuomc_1.product_id, tuomc_1.uom_type_id, tuomc_1.rate
  • Index Cond: (tuomc_1.product_id = price_default.product_id)
  • Buffers: shared hit=77727
103. 310.908 310.908 ↓ 0.0 0 25,909

Index Scan using price_modified_excl on client_pinnacle.price_modified pm (cost=0.28..0.30 rows=1 width=27) (actual time=0.012..0.012 rows=0 loops=25,909)

  • 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)
  • Buffers: shared hit=77727
104. 6.208 155.816 ↓ 1.5 26,424 1

Hash (cost=77,713.62..77,713.62 rows=18,056 width=64) (actual time=155.816..155.816 rows=26,424 loops=1)

  • Output: cpl.distributor_invoice_number, cpl.claim_product_id, cpl.claim_id
  • Buckets: 32768 Batches: 1 Memory Usage: 1934kB
  • Buffers: shared hit=127057
105. 3.665 149.608 ↓ 1.5 26,424 1

Subquery Scan on cpl (cost=77,126.80..77,713.62 rows=18,056 width=64) (actual time=132.053..149.608 rows=26,424 loops=1)

  • Output: cpl.distributor_invoice_number, cpl.claim_product_id, cpl.claim_id
  • Buffers: shared hit=127057
106. 12.071 145.943 ↓ 1.5 26,424 1

GroupAggregate (cost=77,126.80..77,533.06 rows=18,056 width=64) (actual time=132.052..145.943 rows=26,424 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=127057
107. 11.846 133.872 ↓ 1.5 26,424 1

Sort (cost=77,126.80..77,171.94 rows=18,056 width=33) (actual time=132.037..133.872 rows=26,424 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: 2833kB
  • Buffers: shared hit=127057
108. 3.367 122.026 ↓ 1.5 26,424 1

Nested Loop (cost=389.90..75,850.22 rows=18,056 width=33) (actual time=7.092..122.026 rows=26,424 loops=1)

  • Output: claim_product_3.claim_id, claim_product_location.claim_product_id, claim_product_location.distributor_invoice_number
  • Buffers: shared hit=127057
109. 35.953 39.387 ↑ 1.0 26,424 1

Bitmap Heap Scan on client_pinnacle.claim_product claim_product_3 (cost=389.34..31,681.35 rows=27,537 width=32) (actual time=7.064..39.387 rows=26,424 loops=1)

  • Output: claim_product_3.claim_id, claim_product_3.claim_product_id
  • Recheck Cond: (claim_product_3.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Heap Blocks: exact=20870
  • Buffers: shared hit=20975
110. 3.434 3.434 ↑ 1.0 26,424 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..382.46 rows=27,537 width=0) (actual time=3.434..3.434 rows=26,424 loops=1)

  • Index Cond: (claim_product_3.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=105
111. 79.272 79.272 ↑ 5.0 1 26,424

Index Only Scan using claim_product_location_pkey on client_pinnacle.claim_product_location (cost=0.56..1.55 rows=5 width=17) (actual time=0.003..0.003 rows=1 loops=26,424)

  • 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=106082
112. 5.717 14.269 ↓ 264.2 26,424 1

Hash (cost=5.44..5.44 rows=100 width=40) (actual time=14.269..14.269 rows=26,424 loops=1)

  • Output: claim_product_order.claim_id, cpo.display_order, cpo.claim_product_id
  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2114kB
  • Buffers: shared hit=61
113. 3.718 8.552 ↓ 264.2 26,424 1

Nested Loop (cost=0.42..5.44 rows=100 width=40) (actual time=2.302..8.552 rows=26,424 loops=1)

  • Output: claim_product_order.claim_id, cpo.display_order, cpo.claim_product_id
  • Buffers: shared hit=61
114. 0.034 0.034 ↑ 1.0 1 1

Index Scan using claim_product_order_pkey on client_pinnacle.claim_product_order (cost=0.42..3.44 rows=1 width=144) (actual time=0.028..0.034 rows=1 loops=1)

  • Output: claim_product_order.claim_id, claim_product_order.item_ids
  • Index Cond: (claim_product_order.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=4
115. 4.800 4.800 ↓ 264.2 26,424 1

Function Scan on pg_catalog.unnest cpo (cost=0.00..1.00 rows=100 width=24) (actual time=2.269..4.800 rows=26,424 loops=1)

  • Output: cpo.claim_product_id, cpo.display_order
  • Function Call: unnest(claim_product_order.item_ids)
  • Buffers: shared hit=57