Postgresql Query Optimizing

Hi,
Requesting you to please check. Oracle code taking less than 4 seconds to execute whereas PostgreSQL taking more than an hr (still running).

Merge into is not available in old version of postgresql/pgadmin. Amazon RDS won’t support in PRD as of now.So,i;ve tried to re-write but that’s taking too much time.

------ ORACLE CODE -------

Merge INTO abc.cust_tot CT
Using (SELECT /*+ PARALLEL(ct,4) PARALLEL(cd,4) USE_HASH(ct) */
ct.cust_num, cust_yp, cust_yp_dt
from cust_tot ct
inner join cust_dt cd
on (ct.cust_num = cd.cust_num)
where ct.cust_num not in
(select cust_no from Cust_h_bal)) S
ON (CT.cust_num = S.cust_num)
WHEN MAtched then
Update
set cust_bal = case
when Trunc(NVL(S.cust_yp_dt,
To_date(‘01/01/1975’, ‘MM/DD/YYYY’))) >
ADD_MONTHS(Trunc(sysdate), -12) then
case
when S.cust_yp >= 0 then
case
when NVL(S.cust_yp, 1) <> NVL(cust_bal, 0) then
NVL(S.cust_yp, 0)
else
cust_bal
end
else
0
End
else
0
End,
timestamp = sysdate;

------------------- PostgreSQL:


UPDATE abc.cust_tot
SET cust_bal =
CASE
WHEN DATE_TRUNC(‘DAY’,(COALESCE(s.cust_yp_dt, TO_DATE(‘01/01/1975’, ‘MM/DD/YYYY’)))) >
DATE_TRUNC(‘DAY’,CURRENT_DATE) - INTERVAL ‘12 MONTHS’ THEN
CASE
WHEN s.cust_yp >= 0 THEN
CASE
WHEN COALESCE(s.cust_yp, 1) <> COALESCE(CT.cust_bal, 0)
THEN COALESCE(s.cust_yp, 0)
ELSE CT.cust_bal
END
ELSE 0
END
ELSE 0
END ,timestamp = CURRENT_DATE
FROM abc.cust_tot AS ct
inner JOIN (SELECT /*+ Parallel(ct 4 hard) Parallel(cd 4 hard) HashJoin(ct) */
ct.cust_no_717, cust_yp, cust_yp_dt
FROM
abc.cust_tot AS ct
inner JOIN abc.cust_dt AS cd
ON ct.cust_no_717 = cd.cust_no_717
WHERE NOT EXISTS
(SELECT 1
FROM abc.Cust_h_bal as chb where chb.cust_num = ct.cust_num)
) AS s
ON ct.cust_num = s.cust_num;


MERGE is available in PG15.

Please check the execution plan, with EXPLAIN. And please keep in mind that this is a german speaking forum.

1 „Gefällt mir“

Hi,

Sure. Merge will be available next year maybe in april/may. Pls guide the other way.

1 „Gefällt mir“

The target table should not repeated in the FROM clause of an UPDATE statement. That is most probably the reason why the UPDATE is so slow.

Your code is a bit hard to follow, but when I translate the original MERGE statement, I would come up with something like this:

UPDATE abc.cust_tot ct
  SET cust_bal = CASE
                  WHEN DATE_TRUNC('DAY',(COALESCE(s.cust_yp_dt, date '1975-01-01))) > 
                     DATE_TRUNC('DAY',CURRENT_DATE) - INTERVAL '12 MONTHS' THEN
                    CASE
                      WHEN s.cust_yp >= 0 THEN 
                        CASE
                          WHEN COALESCE(s.cust_yp, 1) <> COALESCE(CT.cust_bal, 0) THEN COALESCE(s.cust_yp, 0)
                          ELSE CT.cust_bal  
                        END
                      ELSE 0
                    END
                  ELSE 0
                END,
                timestamp = CURRENT_DATE
FROM (
  SELECT ct.cust_num, cust_yp, cust_yp_dt
  from cust_tot tot
     join cust_dt cd on ct.cust_num = cd.cust_num
  where not exists (select * 
                    from Cust_h_bal bal 
                    where bal.cust_num = tot.cust_num)
  ) AS s
WHERE ct.cust_num = s.cust_num;

Your UPDATE statement seems to do something different than the MERGE.

It joins the table cust_tot one additional time but using the column cust_no_717 - which is not used at all in your MERGE statement.

1 „Gefällt mir“

Sure. Thanks. I will give you an update shortly.

I am wondering if the JOIN in the derived table is needed at all.

So something like:

UPDATE abc.cust_tot ct
  ...
FROM cust_dt cd 
WHERE ct.cust_num = cd.cust_num
AND NOT EXISTS (select * 
                from Cust_h_bal bal 
                where bal.cust_num = ct.cust_num)

1 „Gefällt mir“

Thanks.This is working.

Sehr gut. Und das nächste Mal bitte auf Deutsch

1 „Gefällt mir“

psql:/dbf/appworx/pgsql/ats_monthly.sql:909: HINWEIS: Fehler – konnte nicht in Datei „base/pgsql_tmp/pgsql_tmp31083.1724“ schreiben: Kein Speicherplatz mehr auf dem Gerät
–--set fname :REPORT_PATH ats_monthly.xls