How to optimize postgresql with subquires

[Nomfundo]

Hi i have sql that has 4 calculation as the one below with difference being in the interval.
I have cost1 cost2 cost3 cost4 for different interval, Im also having quatities that i multiple to the costs on my outer query. I realize my query takes like 28 sec to return 66 rows.
when i remove the cost subquery calculation it takes a 1 sec. I how can i optimize the ff subquery. Im using the subquery to get the latest cos.NewCostPrice for a given interval. thank you

(SELECT COALESCE(SUM(cos.NewCostPrice),0) 
FROM  m_costhistory  cos 
LEFT JOIN m_costdetail md ON md.m_costdetail_id=cos.m_costdetail_id
WHERE prod.M_Product_ID = md.M_Product_ID AND prod.ProductType !='A'
AND cos.created >= (('2020-02-17'::date) - interval '3 month' ) 
AND cos.created < ('2020-02-17'::date) 
AND cos.M_CostElement_ID=ce.M_CostElement_ID
AND cos.AD_Client_ID =1000008 AND md.C_AcctSchema_ID = 1000004 
AND cos.AD_Org_ID = CASE WHEN cos.AD_Org_ID =0 Then cos.AD_Org_ID ELSE  1000002  END 
AND cos.created=(SELECT max(created) FROM m_costhistory) 
)as Cost1,

[Rabbit]

It's not necessarily the subquery that's slowing things down. It's that it's correlated to an outer query. Rewrite your query as a non-correlated subquery.

That's really all I can say since you only show us part of the query and you haven't really described what the query is supposed to do. So I can only make guesses.

You need to take out the references to the outer query and rewrite it, most likely with the key fields you need, utilizing a group by, and then joining it to the other query.

Very rarely is a correlated subquery needed. Typically, most correlated subqueries can be rewritten as a regular subquery by utilizing an aggregate query with a group by on the key fields.