Thread: Query much slower after upgrade to 9.6.1

SELECT 
  con.client_id
, 'product'::text AS type
, p.product_number AS identifier
, p.product_name AS description
, civ.rebate_direct_decimal_model
, civ.rebate_deviated_decimal_model
, civ.rebate_direct_value
, civ.rebate_direct_type
, civ.rebate_deviated_value
, civ.rebate_deviated_type
, actuals.claimant
, actuals.claimant_id
, civ.estimated_quantity AS prob_exp_volume
, COALESCE(actuals.rebate_allowed_quantity, 0) AS actual_volume
, CASE WHEN r.rate IS NULL OR (r.rate < 0) THEN NULL ELSE (r.rate * civ.estimated_quantity) END AS prob_exp_dollars 
, COALESCE(actuals.rebate_allowed_dollars, 0) AS actual_dollars 
, COALESCE(actuals.transaction_date,null) AS transaction_date
FROM contract con
INNER JOIN contract_item_view civ
ON con.contract_id = civ.contract_id
INNER JOIN product p
ON civ.product_id = p.product_id
INNER JOIN product_uom_conversion civuomc
ON civ.uom_type_id = civuomc.uom_type_id
AND civ.product_id = civuomc.product_id
LEFT JOIN LATERAL (
SELECT 
 claim_product.product_id
, company.company_name AS claimant
, company.company_id AS claimant_id
, MAX(COALESCE(transaction.transaction_date,null)) AS transaction_date
, SUM((civuomc.rate / cpuomc.rate) * claim_product.rebate_allowed_quantity) AS rebate_allowed_quantity
, SUM(claim_product.rebate_allowed_quantity * claim_product.rebate_allowed_rate) AS rebate_allowed_dollars
FROM contract
INNER JOIN contract_claim_bridge
USING (contract_id)
INNER JOIN claim
USING (claim_id)
INNER JOIN claim_product
USING (claim_id)
INNER JOIN product_uom_conversion cpuomc
ON claim_product.uom_type_id = cpuomc.uom_type_id
AND claim_product.product_id = cpuomc.product_id
INNER JOIN invoice
USING (invoice_id)
INNER JOIN company
ON company.company_id = invoice.claimant_company_id
LEFT JOIN LATERAL (
SELECT MAX(transaction_date) AS transaction_date
FROM claim_transaction
WHERE TRUE
AND claim_transaction.claim_id = claim.claim_id
AND claim_transaction.transaction_type IN ('PAYMENT'::enum.transaction_type,'DEDUCTION'::enum.transaction_type)
ORDER BY transaction_date DESC
LIMIT 1
)  transaction
ON TRUE
WHERE contract.contract_sequence = con.contract_sequence
AND contract.contract_renew_version = con.contract_renew_version
AND contract.client_id = con.client_id
AND claim.claim_state = 'COMPLETE'
GROUP BY claim_product.product_id, company.company_name, company.company_id
) actuals
ON actuals.product_id = civ.product_id
LEFT JOIN LATERAL gosimple.calculate_contract_item_probable_exposure_rate(
(
SELECT array_agg(row(x.contract_item_id, x.estimated_quantity, x.price)::gosimple.in_calculate_contract_item_probable_exposure_rate)
FROM
(
SELECT 
 civ2.contract_item_id
, civ2.estimated_quantity AS estimated_quantity
, AVG(pd2.price / puc2.rate) AS price
FROM contract con2
INNER JOIN contract_item_view civ2
ON con2.contract_id = civ2.contract_id
LEFT JOIN product_uom_conversion puc2
ON puc2.product_id = civ2.product_id
AND puc2.uom_type_id = civ2.uom_type_id
LEFT JOIN price_default pd2
ON civ2.product_id = pd2.product_id
AND pd2.active_range @> now()
WHERE TRUE
AND con2.contract_id = con.contract_id
GROUP BY civ2.contract_item_id, civ2.estimated_quantity
) AS x
)) r
ON civ.contract_item_id = r.contract_item_id
WHERE TRUE
AND con.contract_id = '54e28f3b-8f87-46fc-abf0-6fe86f528c0c'