Query much slower after upgrade to 9.6.1 - Mailing list pgsql-performance

From Adam Brusselback
Subject Query much slower after upgrade to 9.6.1
Date
Msg-id CAMjNa7cirhpZxx-9HaDcXNFt95w7eoXLwWscTBa2kEFzLQFaZg@mail.gmail.com
Whole thread Raw
Responses Re: Query much slower after upgrade to 9.6.1  (Adam Brusselback <adambrusselback@gmail.com>)
List pgsql-performance
Hello all, I have a query that was running quickly enough on 9.5.5 and has slowed to a halt after upgrading to 9.6.1.

The server hardware is the same, 2 core, 4GB ram DigitalOcean virtual server, running Debian 8.6.

The query is a pretty heavy reporting query to aggregate the dollar value of "claims" against a specific "contract".

The query is below for reference.

Query plan on 9.6.1: https://explain.depesz.com/s/NwmH
Query plan on 9.5.5: https://explain.depesz.com/s/ioI4

We just migrated over the weekend, and this issue was brought to my attention today.

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'

pgsql-performance by date:

Previous
From: Justin Pryzby
Date:
Subject: Re: no MCV list of tiny table with unique columns
Next
From: Adam Brusselback
Date:
Subject: Re: Query much slower after upgrade to 9.6.1