pgsql query is causing performance issue

Question

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.

Answer 1

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;