pgsql query is causing performance issue-CodePudding

My query on PostgreSQL instance is taking a long time to retrieve the result. This query ran for every 30 mins in our application code.

select fgh_dk,
       (select dmb
        from fgh_tonee
        where id=fgh_dk),
       ghk_nm,
       SUM(hjlm_sup) as mgh_klmno
from yunm
where fgh_dk is not null
group by fgh_dk, ghk_nm
order by fgh_dk,ghk_nm;

Please suggest us best way to rewrite this query.

CodePudding user response：

Normally, such questions can never be answered without seeing EXPLAIN (ANALYZE, BUFFERS) output. But your query is bound to perform better if you do away with the subquery:

select yunm.fgh_dk,
       fgh_tonee.dmb,
       yunm.ghk_nm,
       SUM(yunm.hjlm_sup) as mgh_klmno
from yunm
   left join fgh_tonee
      on fgh_tonee.id = yunm.fgh_dk
where yunm.fgh_dk is not null
group by yunm.fgh_dk, yunm.ghk_nm, fgh_tonee.dmb
order by yunm.fgh_dk, yunm.ghk_nm, fgh_tonee.dmb;