By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,469 Members | 1,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,469 IT Pros & Developers. It's quick & easy.

How to optimize postgresql with subquires

P: 1
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

Expand|Select|Wrap|Line Numbers
  1. (SELECT COALESCE(SUM(cos.NewCostPrice),0) 
  2. FROM  m_costhistory  cos 
  3. LEFT JOIN m_costdetail md ON md.m_costdetail_id=cos.m_costdetail_id
  4. WHERE prod.M_Product_ID = md.M_Product_ID AND prod.ProductType !='A'
  5. AND cos.created >= (('2020-02-17'::date) - interval '3 month' ) 
  6. AND cos.created < ('2020-02-17'::date) 
  7. AND cos.M_CostElement_ID=ce.M_CostElement_ID
  8. AND cos.AD_Client_ID =1000008 AND md.C_AcctSchema_ID = 1000004 
  9. AND cos.AD_Org_ID = CASE WHEN cos.AD_Org_ID =0 Then cos.AD_Org_ID ELSE  1000002  END 
  10. AND cos.created=(SELECT max(created) FROM m_costhistory) 
  11. )as Cost1,
4 Weeks Ago #1
Share this Question
Share on Google+
1 Reply

Expert Mod 10K+
P: 12,401
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.
4 Weeks Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.